PostgreSQL Part I

Asif Ahmed

Today we will be looking at PostgreSQL with PHP tutorial on Lynda.com. I’ll be writing this post while learning the material so this post may seem like a set of notes.

You can download PostgreSQL from here. Remember to run the executable as an administrator. The first few sections teach us mostly about how to use the PostgreSQL tools like pgAdmin III which can create databases, tables, and users. We also go over how to create a table using PHP and inserting data into a table using PHP. Throughout most of the course, we are heavily referring to their exercise files. These exercise files can be downloaded if you have access to Lynda.com. The beginning files even have a lot of pre-written code. We will only be looking at small snippets of code and try to dissect its meaning.

$horse_id = $db->sql_query_value("INSERT INTO $tn (animal, sound) VALUES (?,?) RETURNING id",
'horse', 'A horse is a horse, of course, of course');
message('Added ID number %d',$horse_id);

$row = $db->sql_query_row("SELECT * FROM $tn WHERE id= ?", $horse_id);
message('id:%d: the %s says %s', $row['id'],$row['animal'],$row['sound']);

This PHP code tries to add a row to an existing table. We execute an SQL statement and insert values into a table ($tn) that was defined earlier in the PHP file. We are inserting the data in the row columns of “animal” and “sound” but we also return the id, which in this case is the row number. The “?” serves as a placeholder in SQL. We define these placeholders right closing the quotes on the SQL statement with the actual values for these parameters. We then output in Line 3 a message that tells us which ID was added to the table. In PHP, much like C, %d and %s serve as a placeholder for string formatting. In Line 4, we can execute a query to figure out what data there is in the row with id, “horse_id”. Finally we output the parameters of this row, but by accessing it as an associative array. This should mean that $row[‘animal’] should return “horse”. “INSERT” statements seek to insert or add data to the table.

Statements like
$db->sql_query_row("SELECT * FROM $tn WHERE animal = ?", "duck");
will return the entire row information where the animal value is equal to “duck”. It can then store this information in an array. A statement like
$db->sql_query_value("SELECT sound FROM $tn WHERE animal = ?", "dog");
will return the sound value where the animal value is equal to “dog”. This statement should return a string or something like “woof”. Note that one has “sql_query_row” and another has “sql_query_value”. These “query” statements will return information about data in the table.

To update data in the table we could use a statement like
$db->sql_do("UPDATE $tn SET sound = ? WHERE animal = ?", "bow wow", "dog");.
This would update the database and set the sound to “bow wow” where the animal is equal to “dog”. As you can see, most sql syntax is very readable. It’s usually pretty obvious what each statement is trying to do.