When you talk about PHP and databases, people tend to assumeyou're talking about MySQL. But hang on to your horses - difficultthough it may be to believe, PHP does include support for a number ofother databases. One of them is PostgreSQL, the *other* open-sourcedatabase - and this article tells you everything you need to know aboutusing it with PHP.
One of the nice things about PostgreSQL - and one of the reasons why many developers prefer it over MySQL - is its support for transactions (in case you didn't know, this refers to the ability to group a series of SQL statements together so that they are executed either together, or not at all). You can find more information about transactions online, at http://www.postgresql.org/idocs/index.php?tutorial-transactions.html - and if you already know what they are, here's an example which demonstrates how they may be used in a PHP context with PostgreSQL.
<?
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "postgres";
$pass = "postgres";
$db = "test";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// begin a transaction block
$query = "BEGIN WORK";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// generate some queries
$query = "INSERT INTO addressbook values (nextval('addressbook_id_seq'),
'Spiderman', 'The Web, Somewhere In Your Neighborhood', 'None',
'spidey@neigborhood.com')"; $result = pg_query($connection, $query) or
die("Error in query: $query. " . pg_last_error($connection));
$query = "INSERT INTO addressbook values (nextval('addressbook_id_seq'),
'Bruce Wayne', 'Gotham City', '64928 34585', 'bruce@batcave.org')";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// now roll them back
$query = "ROLLBACK";
// if you want to commit them, comment out the line above
// and uncomment the one below
// $query = "COMMIT";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// now check to see how many records are there in the table
// and print this
$query = "SELECT * FROM addressbook";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection)); $rows = pg_num_rows($result); echo
"There are currently $rows records in the database";
// close database connection
pg_close($connection);
?>
Technically, there's nothing new here - this script uses the same
functions you've seen in preceding examples. The difference lies in the use of multiple SQL statements to begin and end a transaction block, and in the use of COMMIT and ROLLBACK statements to commit and erase records from the database.
