PHP With Postgresql

Generally with PHP My sql is used. PHP is the most widely used Apache module available and provides a strong platform for Web application development. However, most people who use PHP with open source databases use PHP with MySQL.

Example of PHP with Postgresql:

Making the Connection

There are two direct ways to make a connection to PostgreSQL using PHP. They are pg_connect() and pg_pconnect(). The syntax of the two connections is very similar. However, functionally, they are a bit different. The pg_connect() function will create a new connection for each instance of the function. On the other hand, pg_pconnect() will reuse an existing connection to PostgreSQL if one is available.

The following is a simple example of opening a database connection to PostgreSQL:

$connection = pg_connect("dbname=mydb user=myuser host=localhost");
?>

To do the same with pg_pconnect, the syntax is nearly identical:

$connection = pg_pconnect("dbname=mydb user=myuser host=localhost");
?>

The previous examples open a connection to the database "mydb" on the local host, as the "myuser" user. If the user= parameter is omitted, the user that will be authenticated is the system user that your Web server is running as (for example, "nobody").

The next step would be to actually send a command or query to the PostgreSQL database. This is done in conjunction with the pg_connect() function by using the pg_exec() function.

$connection = pg_connect("dbname=mydb user=myuser");
$myresult = pg_exec($connection, "SELECT * FROM id,username,fname,lname where id > 100");
?>

The previous example will connect and execute a query, but not much else. You only have to pass the $connection variable if you have more than one connection to choose from (for example, if you have opened two connections with the pg_connect() function). Therefore, the following code would function identically, if you intend on having only one connection:

pg_connect("dbname=mydb user=myuser");
$myresult = pg_exec("SELECT * FROM id,username,fname,lname where id > 100");
?>

If you wanted to make this code a bit more robust, you could add some exception handling:

$connection = pg_connect("dbname=mydb user=myuser");
if (!$connection) {
print("Connection Failed.");
exit;
}
$myresult = pg_exec($connection, "SELECT * FROM id,username,fname,lname where id > 100");
?>

Now we have a connection to PostgreSQL that will alert you if the connection fails. Also, we are executing a simple query. We are not, however, processing the results of the query we sent. Thus, we will want to add some logic on the result set as shown in the following bit of code:

// make our connection
$connection = pg_connect("dbname=mydb user=myuser");

// let me know if the connection fails
if (!$connection) {
print("Connection Failed.");
exit;
}

// declare my query and execute
$myresult = pg_exec($connection, "SELECT * FROM id,username,fname,lname where id > 100");

// process results
for ($lt = 0; $lt < pg_numrows($myresult); $lt++) {
$id = pg_result($myresult, $lt, 0);
$username = pg_result($myresult, $lt, 1);
$fname = pg_result($myresult, $lt, 2);
$lname = pg_result($myresult, $lt, 3);

// print results
print("User Id: $id
\n");
print("Username: $username
\n");
print("First Name: $fname
\n");
print("Last Name: $lname
\n");
}
?>