I noticed that there are very few examples of Postgres json_array_elements() and hence, we have this small post published. While writing queries for JSON data field, I’d recommend you to use an online JSON viewer website like jsonviewer.stack.hu. This tool will help you see the structure of the content that you are working on. See the example below:
Raw Json DataStructured Json Content
As you see above, json content can be represented in various object structures. We can see the content as object value or as array content. Certainly, we need different methods to be able to query array and object structures. Before showing the first SQL, let us underline a few assumptions:
Database table name: publications
Json data column name: properties
And consider that publications table contains only one raw – which is above.
Now, let’s see this simple query:
SELECT properties->>’sortOption’ FROM publications;
Query above will return “subjectArena” value. Why? Because, we are referencing the values labeled via “sortOption” tag and since we have only one row in our table, “subjectArena” string value will be returned by the query. And yes, you should be careful with the syntax. ‘->>’ command is used for string reference, and the tag name should be stated between apostrophe characters. (properties->>’sortOption’)
sortOption and publisherTermsAccepted tags are part of the object structure, but how about the availableOrganizations section? This is an array structure and you cannot access this query via simple referencing. Therefore, we should use json_array_elements() function.
SELECT json_array_elements(properties->’availableOrganizations’) FROM publications;
And this query will return the following values:
123
456
789
As you might have already noticed, we refer to the array (availableOrganizations) via ‘->’ command. This command is used for object pointing, as ‘->>’ is used for string reference. See the document here for more operators: www.postgresql.org/docs/9.3/static/functions-json.html
AdminLTE is a great template for online admin panel systems and the template provides a huge range of needs. See the template here: almsaeedstudio.com
Among other handy components, AdminLTE provides the following calendar box.
Of course, when the user clicks a date, there should be a way to pick this content. I looked around the web if somebody else already blogged about the same problem, but saw none. After quite thorough html source investigations, I realised that AdminLTE uses datepicker-switch class in table headers (<th>). So, the issue was solved for me. The rest is jquery code and call to the related php files.
/*
The following function is triggered when a calendar day
is clicked by the user. Corresponding box item (Agenda)
will show all reservations for the specified date and it
should also update the Capacity value under the calendar box.
*/
$(document).on('click', 'td.day', function() {
var month_and_year = $("th.datepicker-switch").html();
var day = $(this).text();
var picked_date = day + ' ' + month_and_year;
var myData = 'picked_date=' + picked_date;
jQuery.ajax({
type: "POST",
url: "./backend_php/reservation/list_reservations_of_active_date.php",
dataType:"text",
data:myData,
success:function(response){
$("#todays_agenda_content").html(response);
},
error:function (xhr, ajaxOptions, thrownError){
alert(thrownError);
}
});
jQuery.ajax({
type: "POST",
url: "./backend_php/reservation/update_capacity.php",
dataType:"text",
data:myData,
success:function(response){
$("#progress_bar_respond").html(response);
},
error:function (xhr, ajaxOptions, thrownError){
alert(thrownError);
}
});
});
Sercan Leylek (29) fra Tyrkia er blant dem som ble kåret på Topp 10-listen til Leadership Foundation i fjor høst over innvandrere som er forbilder for andre fordi de har gjort det skarpt på sine områder.
Han kom til Norge som 22-åring, ferdig utdannet som webutvikler og software-ingeniør. Etter tre måneder som trainee, fikk han fast jobb i et Oslo-basert firma.
Leylek er aktiv samfunnsdebattant, forfatter, engasjert i innvandrerspørsmål og holder kontakt med det tyrkiske miljøet i Oslo. Han nikker gjenkjennende til det undersøkelsen viser.
– Dessverre er det nok sånn at en 15-åring som innvandrer med familien sin fra Tyrkia, vil ha kommet kortere i sin utvikling som 18-åring her i Norge enn hvis han var blitt boende i hjemlandet, sier han.
Føler seg hjelpeløse
– I Tyrkia skjer utviklingen raskere enn i innvandrermiljøet her i landet. Her er foreldrene altfor opptatt av å beskytte familiens tyrkiske identitet, og mange gir ikke barna nok motivasjon til å ta utdannelse. De har for få ambisjoner på barnas vegne, mener han.
Han mener 90 prosent av barrièrene innvandrerungdom møter, skyldes foreldre og bakgrunn – bare ti prosent skyldes samfunnet.
Leylek ser at mange tenåringer som kommer hit, føler seg hjelpeløse og gjør store anstrengelser for å klare skolen.
– Da trenger de foreldre som gir dem en «boost», og ikke stopper dem, sier han.
I’ve just come over another time consuming and perfectly annoying issue, so I decided to drop this post.
My following jquery command was keep returning ‘undefined‘ value for no good reason.
JQuery part:
var comment = $('textarea#' + telephone_nr).val();
HTML part:
<textarea id ="9801213">
As you guess, I used the telephone number value as my identifier to access the value in textarea item, but although I tried a number of methods, I didn’t manage to get the correct value. Weirdly, the internet is full of wrong information for this error. Many sites claim that textarea item has no .val() function or value attribute. (<<< This is highly wrong info. Textarea item has .val() function. )
In about half an hour, I tried the following piece of code because of some adviser website:
var comment = $("textarea")[0].value;
The code above actually functioned (Pay attention! I didn’t say it worked, it only functioned). Certainly, this is not a solution for me. Because I don’t want to access the textarea item in an array, I want to be able to access these values dynamically.
FINAL SOLUTION
After a few more attempts, I realised that the issue must be related to the id value. My project puts too much trust in telephone number value as primary key, and cause I created some other html items (like textbox, anchor, etc…), somehow I created a confusion while accessing the correct item. I still don’t know why this happened and I assure you there is only one textarea item with a unique identifier.
However, I solved the issue by altering the id attribute of my textarea items.
JQuery part:
var comment = $('textarea#tel_' + telephone_nr).val();
HTML part:
<textarea id ="tel_9801213">
Making sure that id value has a unique value worked. That’s it.
What’s next?
I actually encountered this issue while implementing my last work. I’ve been building a restaurant reservation system and textarea field will keep the comments of the restaurant manager for his/her clients.
I will most likely encounter more trouble while working on this product, and naturally, there will be more posts. Stay tuned!
Reflex Game (or Reaction Game in other words) is a popular online game to practice JQuery and other web development tools. Of course, you can find a number of samples online, but I decided to contribute my work in the web ocean as well.
We will start with the clear description of the task, because …
“If you can’t write it down in English, you can’t code it.” — Peter Halpern
DESCRIPTION
Reflex Game will receive the player’s name and will continue to pop up a square item on the page as long as the player clicks on the square items. Squares will show up with some random time and random location of the screen – of course, within a predefined area.
The purpose of the user is to click the square as early as possible. The game will continuously save the user name and the score (clicking time) into the database.
Top-10 best scores will be listed with user names and the scores. List should be synchronously or asynchronously updated on the same page.
To keep the records, a database and at least one table are certainly needed. Creating install.php file is quite handy both for debugging and carrying the project from localweb to production environment.
$db->select_db("reflex");
echo "Database <strong>Reflex</strong> created successfully!
";
echo "Creating the <strong>game_stats</strong> table...
";
$db->query("
CREATE TABLE game_stats
(
ID int AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(20),
reflex_time DOUBLE
);
");
echo "Table game_state is created. Ready to <a href=\"play.php\">play</a> the game!
";
$db->close();
Play.php
This will be the interface of the user. The page is divided into 3 main sections:
User Name Interface
Game Area
Hi-Score Table
User Name Interface
This interface will let the player type the user name. See the html code below:
And the javascript code below should be inserted into thesection of the page. We use keyup function to inform the client that current_user textbox should not be empty with the help of a javascript function (isBlank(str)).
<script type="text/javascript">
$(document).ready(function(){
refreshTable();
$("input#current_user").keyup(function(){
var text = $('input#current_user').val();
if (isBlank(text))
$("div#textarea").html("
Please enter a username");
else
$("div#textarea").html("");
});
});
function isBlank(str) {
return (!str || /^\s*$/.test(str));
}
function refreshTable(){
$('#responds').load('getTable.php', function(){
setTimeout(refreshTable, 1000);
});
}
</script>
Game Area
This section handles a more tricky part. The javascript code placed in theelement will continuously call the function createSquare() as the user clicks the random square.
And the function createSquare will use Math.random() function to create random time and the coordinates for the square.
function createSquare() {
var time = Math.random() * 3000;
setTimeout(function()
{
var y = Math.random() * 340;
var x = Math.random() * 250;
document.getElementById("square").style.top = y + "px";
document.getElementById("square").style.left = x + "px";
document.getElementById("square").style.display="block";
createdTime = Date.now();
}, time);
}
Hi-Score Table
The section for Hi-Score list is as simple as it is. The label responds will serve here to receive the content created by getTable.php.
And, there is one more important detail to call getTable.php for work. Thesection should contain the following javascript function to call for the update and the result should refer to the predefined lable (responds).
function refreshTable(){
$('#responds').load('getTable.php', function(){
setTimeout(refreshTable, 1000);
});
}
Response.php
This module will do the backend work. The variables current_user and reflexTime will be posted into Response.php and the php code will insert these records into the database.
if(isset($_POST["current_user"]) && strlen($_POST["current_user"])>0)
{
$reflexTime = $_POST["reflexTime"];
$current_user = $_POST["current_user"];
$insert_row = $db->query("INSERT INTO game_stats(user_name, reflex_time) VALUES('".$current_user."', $reflexTime)");
if($insert_row)
{
$db->close();
}
else
{
header('HTTP/1.1 500 Looks like mysql error, could not insert record!');
exit();
}
}
Since we do not wish to keep the scores of an unnamed user, INSERT query is not initiated when the current_user varilable is empty.
getTable.php
The code snippet below will show us the early mentioned Hi-Score listing. SELECT query simply limits the top 10 shortest reflex time and therefore, LIMIT keyword is used in mysql.
<?php include("include/connect.php"); $results = $db->query("
SELECT user_name, reflex_time
FROM game_stats WHERE 1
ORDER BY reflex_time ASC LIMIT 10");
$i = 1;
echo "
<table>";
while ($row = $results->fetch_assoc())
{
echo "
<tr>
<td>$i)</td>
<td>".$row["user_name"].'</td>
<td>'.$row["reflex_time"].'</td>
</tr>
';
$i++;
}
echo "</table>
";
$db->close();
?>
Reflex Game is a good practice tool to use PHP and Jquery together while implementing the front and back end of things. As you saw, our player will be only in touch with the page Play.Php.
You are welcome to put your name in my Top-10 list. Current best time is 0.318 seconds.
Sercan Leylek introduces the website for SPIRE AS this week. Amela Koluder, one of the leading innovation experts in Norway, is the owner of SPIREwhich means “to sprout” in Norwegian. SPIRE aims to provide mentoring and creative workshops for especially international organisations. SPIRE previously worked with H&M, Røde Kors, Favne, inmeta, and Heggelund & Koxvold.
The website not only holds content for innovation business, it also provides some competitiveness tutorial (translated as Konkurransekraft in Norwegian).
Mobile compatible website was developed by using HTML5, CSS. Some php scripting also provides the messaging functionality for the users and the site admin.
True motivation of this article is not to indulge those who wrote a “Hello World” program in several languages. The idea aims to bring some philosophical understanding on our daily job. Stop for a minute, and think:
Why did you decide to earn your life by programming?
What are you actually doing in the broader perspective?
Your piece of code definitely has a purpose and tries to solve something, but how about your purpose?
We know the death cause of most people, but we rarely know what they lived for. In most cases, not even the lost souls truly knew it. This is a perfect metaphor to explain what the programmers spend their life with. We –mostly- know why we wrote some method, class, etc… Our delivery is so precise, but we are not so sure about who we are. Programmer, creator, IT guy, architect, what?
Generally speaking, my intention is to bring a definition to those who earn their lives by writing programs. And this definition claims:
Every Programmer is an Author.
That is simple as it is. Your task falls under the class of an author. The person who has the authentication of an abstract or factual world is the author. An author decides what is happening now, what happened in the past, and what is expected to happen. The author is the God of a given world. It is your decision and the result is your responsibility.
An author’s work contains both the conflict and the solution within. That’s why a programmer’s work is a fiction work. We must have a use-case scenario, a reason to tell our story. Does your program help a patient’s heart to beat? Does it feed an animal? Does it help paying an employee’s salary? These are all differentiated scenarios, and your story aims to reach the happy ending.
An author needs to edit his/her work all the time. The text will be delivered to an editor. The editor will act like a QA analyst and will ask some parts to be removed, added or changed. Then, even though your work is being delivered to the readers (users) for so many years, each edition will need some other update. The final product may be found quite useful by some people, may be disliked by some others. But there is one thing for sure, what you hold in hand right now is something more convenient and reasonable than your first draft.
Programming is an art. It requires aesthetic expression of thoughts. Machine code is written for the computers by the compilers, but the programs are written for humans by humans.
All in all, we may certainly include more genres under the Fiction class of the chart above. However, a program holds the combination of novel, poetry, screenplay and story attributes. That’s why what we do is definitely some fiction work and we are the authors of it.
We finally reached the last chapter in sending automated e-mails via your C# program. This blog will cover the last implementation in 3 sub-sections:
Importing Outlook Signature
Adding Attachment
Using Microsoft.Office.Interop.Outlook Namespace
Importing Outlook Signature
Considering that you already created your e-mail body yourself depending on the customer needs. However, the e-mail body should also contain some signature information. The Outlook keeps your signature files under some predefined folder (Like: C:\Users\sercan.leylek\AppData\Roaming\Microsoft\Signatures). I recommend you to create a custom signature for your automated e-mails. In my case, this file name is PCO_update_signature.htm. If you don’t know how to organise signature templates, see this blog: How to Create Outlook Signature
Now we can dive into the method below. The duty of the method is simple. get_outlook_signature() is supposed to return the html content of the identified signature template.
AttachFileClick event method will be invoked when Attach File button is clicked by the user. The method simply provides an OpenFileDialog item and the selected file(s) will be noted.
Using Microsoft.Office.Interop.Outlook Namespace
The previous two sub-sections introduced some critical functionalities before sending the e-mail. This part will focus on how to create a mail item and eventually deliver the e-mail to the receiver(s).
The method send_email() manages the following list of tasks:
Creating Outlook objects: Application Instance and MailItem
Outlook.Application objOutlook = new Outlook.Application();
Outlook.MailItem mic = (Outlook.MailItem)(objOutlook.CreateItem(Outlook.OlItemType.olMailItem));
In Microsoft Office Outlook, you can create different e-mail signatures. These signature files will be saved under some predefined folder. On my computer, this folder is
The signature templates will be saved under your corresponding folder as htm, rtf and txt files. (You can use these files for your applications. For instance, you can use the C# method Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) to return the custom folder.)
Click Tools > Options…
Under Options window, click Mail Format tab.
Click Signatures button
Signatures and Stationery window will let you create & edit different signature templates in rich text format.
The first article (Automated E-mail Service in C# – 1) mentioned the list of technologies and provided you some UI tips on creating a tool which can distribute your e-mail notifications without installing an SMTP server. This article will focus on the connection between your UI and windows application.
Connecting User Interface & E-Mail Content
Scenario
Since you are trying to automate things, you probably do not aim to send the same e-mail content to everybody. Your requirement must be delivering custom content, in custom cases for different or groups of customers. Therefore, previously stored UI items should be utilised smartly. Take my example below:
My clients are supposed to be receiving some notification e-mails in at least 11 different cases. So, I created the scenarios above via the DropDown box. The task after this categorisation is quite simple; we use the ‘SelectedIndexChanged’ event of the DropDown box and edit the content of the e-mail (alias, the body of the email) each time the user picks another scenario.
CC Button
Certainly, you may need to CC a group mail address or a group of people whose addresses are collected from a database for certain cases. To do that, a checkbox item will fit your purpose.
For this functionality, we will use the CheckedChanged event of Windows Application. See the code below:
This article must’ve provided you some basic understanding of putting things together. By using similar Windows item events, you can create your e-mail content for custom cases. The next article will introduce you the Outlook library in C#. Stay tuned!
Do you remember the attachment interface that we created in 
AttachFileClick event method will be invoked when Attach File button is clicked by the user. The method simply provides an OpenFileDialog item and the selected file(s) will be noted.
