Migrating From MySQL To PostgreSQL In PHP
Tuesday, February 12th, 2008
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.
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.
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)
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.
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)
Fetching last ID:
Old function: mysql_insert_id()
New function: pg_last_oid()
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.
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. 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:
Every row that exists in a PostgreSQL database has an OID column associated with it. When you normally select data from a table (e.g. select * from myTable) you won’t see this field
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).
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. 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.
