Migrating PHP scripts to MySQL from PostgreSQL

Published on 2007-01-05 21:13:00+00:00
Programming   mysql   php   postgresql  

I've recently had to work on a project where I needed toconvert some very basic PHP code thataccessed a postgresqldatabase so that it would work with mysql. For the most part, this has beenfairly simple thanks to rpl. Many of PHP'sdatabase functions have very similar names, so I simply use rpl toconvert the existing code. Here's a simple bashscript that I put together to convert some of my postgresqlfunctions to mysql:

if [ ! -n "$1" ]
   echo "Usage: `basename $0` "
  exit 1;


echo "---- Relacing pg_ with mysql_ in $X"

rpl -R -x'.php' 'pg_pconnect' 'mysql_pconnect' $X
rpl -R -x'.php' 'pg_connect' 'mysql_connect' $X
rpl -R -x'.php' 'pg_query' 'mysql_query' $X
rpl -R -x'.php' 'pg_fetch_row' 'mysq_fetch_row' $X
rpl -R -x'.php' 'pg_fetch_assoc' 'mysq_fetch_assoc' $X
rpl -R -x'.php' 'pg_close' 'mysq_close' $X
rpl -R -x'.php' 'pg_num_rows' 'mysq_num_rows' $X
rpl -R -x'.php' 'pg_numrows' 'mysq_num_rows' $X
rpl -R -x'.php' 'pg_last_error' 'mysql_error' $X

Just in case your curious about converting from postgresql or mysql to sqlite, I've compiled the following table of analogous functions from php.net:

PostgreSQL MySQL SQLite
pg_connect mysql_connect sqlite_open // open or create database
pg_query mysql_query sqlite_query
pg_fetch_row mysql_fetch_row sqlite_fetch_array (ordinal and associative)
pg_fetch_assoc mysql_fetch_assoc
pg_close mysql_close sqlite_close
pg_num_rows or pg_numrows mysql_num_rows sqlite_num_rows