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

Replacing MySQL's Auto-increment

In MySQL, to create an auto-incrementing field called someID, you would do something like this:

Listing 9 listing-9.sql
create table myTable (
    someID      int             not null        auto_increment,
    name        varchar(255)    not null,
    primary key (someID)

In PostgreSQL, to get the same behaviour, you do:

Listing 10 listing-10.sql
create table myTable (
    someID      serial          not null,
    name        varchar(255)    not null,
    primary key (someID)

Using serial is basically a shortcut for the server to create an integer column, a sequence and a trigger. A sequence is a basically a function that returns an incremented number each time you call it, while a trigger is a function that is executed every time data is inserted or updated (in this case only inserts are relevant).

Tough to explain – it’s probably just as easy to read Serial Types in the PostgreSQL manual.

Finally, to fetch a newly inserted serial from a PostgreSQL table from PHP, you firstly need to call pg_last_oid(). This returns an OID (see the previous chapter), and you then need to select the serial column using the OID.

Here’s an example, using the SQL just above.

Listing 11 listing-11.php
    $result = pg_query($link, "insert into myTable (name) values ('phpRiot')");
    $lastOid = pg_last_oid($link);
    $result = pg_query($link, "select someID from myTable where oid = " . $lastOid);
    $row = pg_fetch_row($result);
    $newId = $row->someID;

It’s a little bit longer to do than in MySQL, but you’re probably using some kind of database abstraction anyway, into which you could automate this into a single function call.

In This Article