PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Migrating From MySQL To PostgreSQL In PHP

PHP Functions

It is possible to get started with PostgreSQL in PHP without having to learn too much – it’s basically just a matter of replacing the MySQL functions with the PostgreSQL functions.

This chapter lists the commonly used MySQL functions and their equivalents for PostgreSQL.

Connecting to a database

  • Old function: mysql_connect($server, $username, $password)
  • New function: pg_connect($connectString)

Connects to the database. Note that pg_connect() takes a single connection string, instead of separate arguments for each connection parameter.

An example connection string is:

Listing 1 listing-1.php
<?php
    $connectString = 'host=localhost dbname=myDatabase user=foo password=bar';
    $link = pg_connect($connectString);
 
    if (!$link) {
        // handle connection error here
    }
?>

Querying a database

  • Old function: mysql_query($query)
  • New function: pg_query($query) or pg_query($link, $query)

This returns a result resource if the query was executed, or false if the query was invalid. Here’s an example:

Listing 2 listing-2.php
<?php
    $result = pg_query($link, 'select * from myTable');
?>

Counting the number of rows

To count the number of rows retrieved by a select statement:

  • Old function: mysql_num_rows($result)
  • New function: pg_num_rows($result)
Listing 3 listing-3.php
<?php
    $result = pg_query($link, 'select * from myTable');
    echo "Number of rows returned: " . pg_num_rows($result);
?>

To count the number of rows affected by an insert, update or delete:

  • Old function: mysql_affected_rows($link)
  • New function: pg_affected_rows($result)

Note that MySQL took in (optionally) the link identifier, whereas PostgreSQL takes in the result resource.

Listing 4 listing-4.php
<?php
    $result = pg_query($link, 'delete from myTable');
    echo "Number of rows deleted: " . pg_affected_rows($result);
?>

Extracting data

There are a number of different functions that can be used to extract rows from a result set. Each of these functions return the next row, and automatically advance the internal row pointer. If there are no rows returned, false is returned.

Old function: mysql_fetch_row($result)
New function: pg_fetch_row($result)

Old function: mysql_fetch_array($result)
New function: pg_fetch_array($result)

Old function: mysql_fetch_object($result)
New function: pg_fetch_object($result)

Listing 5 listing-5.php
<?php
    $result = pg_query($link, 'select myField from myTable');
    while ($row = pg_fetch_object($result)) {
        echo $row->myField . "<br />\n";
    }
?>

Fetching last ID

Note that the MySQL function returns the last value inserted into a field with auto_increment set, whereas PostgreSQL returns an OID of the last record inserted.

These functions behave fairly differently, and in order to understand how to use the PostgreSQL version properly, you will need to read pages 3 and 4 of this article.

Fetching errors

  • Old function: mysql_error()
  • New functions: pg_last_error($conn), pg_result_error($result)

This can be slightly tricky, as pg_last_error() returns the last error for the given connection, whereas pg_result_error() returns the error for the given result – although if a query fails, the result is false! The manual page for pg_result_error discusses how to overcome this, although realistically, pg_last_error should suffice generally.

Listing 6 listing-6.php
<?php
    $result = pg_query($link, 'select bogus syntax query');
    if (!$result) {
        echo pg_last_error($conn);
    }
?>

Generally speaking, a select query should never return any errors. The only situations where errors might occur is if constraints are violated with inserts, updates or deletes.

Disconnecting from a database

Note that this is not usually necessary, as connections are closed automatically at the end of a script’s execution.

  • Old function: mysql_close($link)
  • New function: pg_close($link)

Putting it all together

As you can see, many of the functions are very similarly named (they didn’t always used to be, but somewhere around PHP 4.2.0 they unified these names).

Here’s an example of the full process of using PostgreSQL to fetch some data from a database. It’s a bit messy, but it’s really just for demonstration purposes!

Listing 7 listing-7.php
<?php
    $connectString = 'host=localhost dbname=myDatabase user=foo password=bar';
    $link = pg_connect($connectString);
 
    if (!$link)
        die("An connection could not be established");
 
    $result = pg_query($link, 'select myField from myTable');
    if (!$result) {
        echo "An error occurred: " . pg_last_error($conn) . "<br />\n";
    }
    else if (pg_num_rows($result) == 0) {
        echo "No rows returned.<br />\n";
    }
    else {
        while ($row = pg_fetch_object($result)) {
            echo $row->myField . "<br />\n";
        }
    }
    pg_close($link);
?>

In This Article