Using PostgreSQL from PHP: A Guide for MySQL Programmers

  

 Email Article  

Tweet  

  

People putting together web applications often overlook PostgreSQL in favor of MySQL, yet PostgreSQL is enterprise-ready and has been used by familiar names such as Instagram and Disqus. One advantage is a license that grants the right to use, copy, modify, and distribute the software as long as the relevant copyright notices are maintained, and that doesn't force projects that use PostgreSQL to be released as open source code. You can use PostgreSQL and PHP to build a dynamic web site with data residing in a PostgreSQL database, and if you are familiar with using PHP to communicate with MySQL, it is relatively easy to make the transition to Postgres. If you don't know anything about PostgreSQL, start by reading PostgreSQL Administration for MySQL Admins to learn how to install PostgreSQL and create a simple database.

PostgreSQL allows for a wider variety of client authentication methods than MySQL, including explicit trust, password authentication, and the use of more advanced network authentication methods such as LDAP and Kerberos. The Client Authentication section of the PostgreSQL documentation explains the models and methods.

To get PHP working with PostgreSQL, you need to tweak a number of authentication parameters. First, edit /var/lib/pgsql/data/pg_hba.conf, the file in which you configure authentication, and find the line about IPv4 local connections. Change the last column, which probably says ident, to md5:

# IPv4 local connections:
host	all	all	127.0.0.1/32	md5

This allows users to make connections from localhost to PostgreSQL using a username and password for authentication.

If you created a PostgreSQL user without a password, you need to set a password now. To do this, become superuser (su –), then switch to the postgres user with the command su – postgres. Run psql and enter the following command:

alter role joe password 'secret' ;

Where joe is the name of the role you want to alter and secret is the password.

Finally, if SELinux is enabled, you must configure it to allow the Apache HTTP server to make database connections. As root, type:

setsebool -P httpd_can_network_connect_db on

A test database

Before we look at some PHP code, let's set up some test data to use for our examples. Using psql, run the following commands:

CREATE TABLE birthdays (
firstname varchar(80),
surname varchar(80),
dob date
);

INSERT INTO birthdays (firstname, surname, dob) VALUES ('Fred', 'Smith', '1989-05-02');
INSERT INTO birthdays (firstname, surname, dob) VALUES ('John', 'Jones', '1979-03-04');
INSERT INTO birthdays (firstname, surname, dob) VALUES ('Harry', 'Hill', '1981-02-11');
INSERT INTO birthdays (firstname, surname, dob) VALUES ('Fred', 'Browne', '1969-01-29');

The first step to using PostgreSQL from PHP is to connect to the database. The equivalent to MySQL's mysql_connect() function is pg_connect(), but it takes different parameters than its MySQL counterpart. Pass pg_connect() a single string that contains the connection details:

$dbconn = pg_connect("host=localhost dbname=joe user=joe password='secret'")
    or die('Could not connect: ' . pg_last_error());

And when you want to close the connection, call pg_close() in place of mysql_close():

pg_close($dbconn);

Query the database with the pg_query() function. For example, the follow code fetches all the birthday records:

$result = pg_query("SELECT * FROM birthdays") or die('Query failed: ' . pg_last_error());

PostgreSQL gives you a variety of ways to process the results returned from the database. Relevant functions include:

• pg_fetch_all_columns – Fetches all rows in a particular result column as an array
• pg_fetch_all – Fetches all rows as an array
• pg_fetch_array – Fetch a single row as an array
• pg_fetch_assoc – Fetch a single row as an associative array
• pg_fetch_object – Fetch a single row as an object
• pg_ fetch_ result – Fetches a field from a row
• pg_fetch_row – Fetches a single row as an enumerated array
• pg_num_rows – Returns the number of rows in a result

Here are two examples that process the results of a query. First, pg_fetch_array():

// Print results as a table
echo "<table>\n";
// Loop around and fetch each row as an array
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
    echo "\t<tr>\n";
    // Loop and print each column in the row
    foreach ($line as $col_value) {
        echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>\n";

Second, pg_num_rows() with pg_fetch_result():

$n = pg_numrows($result);
$x = 0;
while($x<$n)
{
        echo pg_fetch_result($result, $x, "firstname") . " " .  pg_fetch_result($result, $x, "surname") . " " .  pg_fetch_result($result, $x, "dob") . "<BR />";
        $x++;
}

If your PHP script makes lots of database queries during a single execution, it is best to free the memory used by the results with pg_free_result($result);, but this isn't strictly necessary as all result memory will be freed automatically when the script ends.

Insert and delete

You can insert and delete rows from the database by using the same pg_query() function:

// Insert a row
$result=pg_query("INSERT INTO birthdays (firstname, surname, dob) VALUES ('David', 'Keller', '1982-10-02')");

// Success?
if (!$result) {
     // In the real world it is better to do some real error processing rather than just die
     die('Could not insert row: ' . pg_last_error());
}

// Delete a row
$result=pg_query("DELETE FROM birthdays WHERE firstname='David' AND surname='Keller'");

// Success?
if (!$result) {
    // In the real world it is better to do some real error processing rather than just die
    die('Could not delete row: ' . pg_last_error());
}

Prepared statements

Parameterized queries, or prepared statements as they are most commonly known, provide an excellent defense against SQL injection attacks. They also help developers define the SQL code they want to use beforehand and then, when needed, call the SQL with the required parameters. Their use also allows PostgreSQL to easily distinguish between code and data, which in turn minimizes the effectiveness of SQL injection attacks.

To create a prepared statement, use the pg_prepare() function. To execute it, use pg_execute():

$result = pg_prepare("birthdayfirstname", 'SELECT * FROM birthdays WHERE firstname = $1');
$result = pg_execute("birthdayfirstname", array("Fred"));

Notice that the second parameter to the pg_execute is an array where each element corresponds to a parameter in the prepared statement ($1, $2, $3, and so on).

Using PostgreSQL shouldn't be a problem for programmers who are used to working with MySQL in PHP. Many of the ideas and principles are the same for both databases, yet PostgreSQL offers advantages over MySQL both in technical terms and in terms of its licensing model.