Part 3.2 of Databases for beginners - PostgreSQL and PHP

Part 3 of Databases for beginners show how to connect to your database via PHP.

Our previous article explained the SQL language. We executed some standard SQL queries from the web browser, and it's time to show how to do this programmatically, and how to use your database in combination with your application. Our first article also showed how to create a database for free, in the cloud. In this part of the series, we show how to connect to your database via PHP.

PHP and PostgreSQL

A client library for PostgreSQL has to be downloaded. A client library has several methods that can be used, in this case to communicate with PostgreSQL. The methods should be used when you, for example, connect to the database (using the given parameters, host name, port number, etc) or when you select data from your database. There is a choice of libraries for almost every programming language.

Start by downloading the client-library for PHP by adding extension=pdo_pgsql.so and extension=pgsql.so to the php.ini file.

This simple example below, shows how to connect, execute a query, print a result row and disconnect from a PostgreSQL database.

$host = "hostname";
$user = "username";
$pass = "password";
$db = "database";

// Open a PostgreSQL connection
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
 or die ("Could not connect to server\n");

$query = 'SELECT * FROM table';
$results = pg_query($con, $query) or die('Query failed: ' . pg_last_error());

$row = pg_fetch_row($results);
echo $row[0] . "\n";
// Closing connection
pg_close($con);

To connect to your ElephantSQL database you simply provide your connection details. Your instance details can be found in your details page for your ElephantSQL instance.

$host = "hostname";
$user = "username";
$pass = "password";
$db = "database";

// Open a PostgreSQL connection
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");

Select your data from your table.

$query = 'SELECT * FROM table';
$results = pg_query($con, $query) or die('Query failed: ' . pg_last_error());

Print a result row.

$row = pg_fetch_row($results);
echo $row[0] . "\n";

Close your connection

// Closing connection
pg_close($con);