Migrate your site from MySQL to PostgreSQL -- Part 2

Let's See Those Results

PHP's Postgres result set handlers can do everything their MySQL counterparts can; they're just slightly different. These small differences may require nothing more than a little modification to code, but they might also require some involved programming.

First, look at what is similar between MySQL and Postgres. The following is a list of common MySQL result handling functions and their Postgres counterparts:

MySQL

mysql_num_rows($result) Returns the number of rows in a result set. This is only valid for SELECT STATEMENTS

mysql_affected_rows($result) Returns the number of affected rows in an INSERT, UPDATE, or DELETE SQL query

mysql_fetch_object($result) Fetches one row of data and returns it as an object. Field names can be accessed using class syntax. (as in $field1 = $var->field1;) This function keeps an internal value to make sure it returns the next row each time it is called.

mysql_fetch_row($result) This function returns a row of the result set as a numeric array. The values can be accessed using array syntax, starting with 0. (as in $field1 = $var[0]). An internal counter keeps track of the rows and passes a new row result each time it is called.

mysql_fetch_array($result) This one is identical to the other two fetching functions, except it returns an associative array with row results ($field1 = $var["field1"];).

Postgres

pg_numrows($result) Works exactly the same as its MySQL counterpart

pg_cmdtuples($result) Works just like its MySQL counterpart

pg_fetch_object($result, $row) Fetches a designated row of the result set. The $row value must be included, and there is no internal counter. Otherwise, it works just like the corresponding MySQL function.

pg_fetch_row($result, $row) Returns a numeric array of the result from the specified row. The row number <b>must</b> be included. Unlike its MySQL equivalent, no internal counter keeps track of row results.

pg_fetch_array($result, $row) Identical to its MySQL equivalent except for the requirement of specifying the row to return and the lack of an internal counter.

For more information on PHP's MySQL and Postgres functions, see the PHP Documentation on PHP.Net.

The starkest difference between PHP's MySQL and Postgres support is in the actual reading from result sets. Where MySQL automatically determines for you which row to return results from, Postgres requires you to specify the row you want to read from. This tends to get in the way of many common MySQL data-reading algorithms. Here are a few short examples of the kind of problem you might run into, as well as two ways to solve it.

//first common example:  
$rslt=mysql_query("SELECT * FROM blah", $connection_id);  
 
while($value=mysql_fetch_array($rslt))  
{  
 //do data handling stuff  
}  
 
//In the Postgres functions, the previous algo won't work,    
//since they require one to specify row number. The fix  
//goes like this (if you're not using the wrapper function  
//explained earlier):  
 
$rslt=pg_exec($connection_id, "SELECT * from blah");  
$limit=pg_numrows($rslt);  
for($rownum=0;$rownum<$limit;$rownum++)  
{  
 $value=pg_fetch_array($rslt, $rownum);  
 //do stuff here  
}

In the previous example, note that the code is a little longer for Postgres, since the row number is required. However, things become a lot easier if you write your own count-keeping wrapper function and use it in conjunction with the wrapper functions shown previously. Here's the utility file with the wrapper functions added. Note the changes to postg_query() as well as the addition of three global variables and result-gathering functions.

 
<?php  
// /usr/local/lib/php/mysite/configfile.php  
$hostname = "localhost";  
$username = "username";  
$database = "mydb";  
$password = "mypasswd";  
 
//internal counting variables  
$fetch_array_counter=0;  
$fetch_object_counter=0;  
$fetch_row_counter=0;  
 
//wrapper functions to ease  
//transition to PostgreSQL  
 
//connect to database  
function postg_connect($hostname, $username, $password, $database)  
{  
 return pg_connect(host=$hostname, dbname=$database user=$username,    
password=$password");  
}  
//connect without having to pass values  
function postg_autoconnect()  
{  
 global $hostname, $username, $password, $database;  
 return pg_connect(host=$hostname, dbname=$database user=$username,    
password=$password");  
}  
 
//query-making function  
function postg_query($query, $connection_id)  
{  
 //set the globals to 0  
 global $fetch_array_counter, $fetch_row_counter, $fetch_object_counter;  
 $fetch_array_counter=$fetch_row_counter=$fetch_object_counter=0;  
 return pg_exec($connection_id, $query);  
}  
 
//pg_fetch_array() replacement  
function postg_fetch_array($rslt)  
{  
 global $fetch_array_counter;  
 $fetch_array_counter++; //add one to the counter  
 return pg_fetch_array($rslt, $fetch_array_counter);  
}  
 
//pg_fetch_row() replacement  
function postg_fetch_row($rslt)  
{  
 global $fetch_row_counter;  
 $fetch_row_counter++; //add one to the counter  
 return pg_fetch_row($rslt, $fetch_row_counter);  
}  
 
//pg_fetch_object() replacement  
function postg_fetch_object($rslt)  
{  
 global $fetch_object_counter;  
 $fetch_object_counter++; //add one to the counter  
 return pg_fetch_object($rslt, $fetch_object_counter);  
}  
?>

Of course, the previous functions will not work if you want to work with two result sets in the same loop, as there is just one internal counter per fetch type. If for some reason you need to read from more than one result set interchangeably, you'll have to use the traditional Postgres method.

Another problem you may run into when changing your code is the absence of a Postgres equivalent of MySQL's mysql_insert_id(), which returns the index value of the last INSERT query. The PHP documentation's language may mislead one to think that pg_getlastoid() does the job, but that is not the case. The lack of such a function is actually not a downside, for it is a result of Postgres's power in allowing multiple auto-incrementing fields through the SEQUENCE system.

Fortunately, getting the last ID is easy. Sequence information can be accessed through SQL, so the following replacement for mysql_insert_id() is possible:

function postg_insert_id($tablename, $fieldname)  
{  
 global connection_id;  
 $result=pg_exec($connection_id, "SELECT last_value FROM ${tablename}_  
${fieldname}_seq");  
 $seq_array=pg_fetch_row($result, 0);  
 return $seq_array[0];  
}

Because Postgres uses a special naming system to name sequences, the function I created above requires the tablename and fieldname. When called, the function above will retrieve the last sequence value used for any SERIAL fields you may have in your table, even if there are more than one.

By using the previous techniques, you should be able to get your MySQL site successfully running PostgreSQL. However, that's just the first step; read on to see a list of useful PostgreSQL resources.