PHP Programming/PostgreSQL

From Wikibooks, open books for an open world
Jump to navigation Jump to search

PostgreSQL is another popular database you can use with PHP.

If you are already familiar with how to interface with MySQL in PHP, then the following chart should make the conversion to PostgreSQL much easier.

Functions[edit | edit source]

Connecting: mysql_connect() takes three arguments (server, username, password) while pg_connect() takes a single connection string argument.
mysql_connect() Example: $db = mysql_connect('localhost', 'mysql_user', 'mysql_pass');
pg_connect() Example: $db = pg_connect('host=localhost user=pg_user password=pg_pass dbname=my_database');
Database Selection: In MySQL, you have to separately specify the name of the database you wish to connect to, while in PostgreSQL it is built into pg_connect()'s connection string.
Querying: mysql_query() and pg_query() behave in the same manner.
mysql_query() Example: $grab_people = mysql_query("SELECT * FROM people WHERE id_num = 3761832");
pg_query() Example: $grab_people = pg_query("SELECT * FROM people WHERE id_num = 3761832");
Fetching Associative Results: mysql_fetch_assoc() and pg_fetch_assoc() behave in the same manner.
mysql_fetch_assoc() Example: $person = mysql_fetch_assoc($grab_people);
pg_fetch_assoc() Example: $person = pg_fetch_assoc($grab_people);
Grabbing Errors: While MySQL makes use of mysql_error(), PostgreSQL uses pg_last_error().
mysql_error() Example: $error = mysql_error();
pg_last_error() Example: $error = pg_last_error();
Closing Database Connection: mysql_close() and pg_close() behave in the same manner.
mysql_close Example: mysql_close($db);
pg_close Example: pg_close($db);
Freeing Results: mysql_free_result() and pg_free_result() behave in the same manner.
mysql_free_result() Example: mysql_free_result($grab_people);
pg_free_result() Example: pg_free_result($grab_people);

Full MySQL Example[edit | edit source]

 $db = mysql_connect('localhost', 'mysql_user', 'mysql_pass');
 $grab_people = mysql_query("SELECT * FROM people WHERE id_num = 3761832");
 $person = mysql_fetch_assoc($grab_people);
 print_r($person);
 $error = mysql_error();
 if($error != ) { print $error; }
 mysql_free_result($grab_people);
 mysql_close($db);

Full PostgreSQL Example[edit | edit source]

 $db = pg_connect('host=localhost user=pg_user password=pg_pass dbname=my_database');
 $grab_people = pg_query("SELECT * FROM people WHERE id_num = 3761832");
 $person = pg_fetch_assoc($grab_people);
 print_r($person);
 print pg_last_error();
 pg_free_result($grab_people);
 pg_close($db);

For More Information[edit | edit source]