Getting Started With DataBases

Of course, what most people want to do with PHP is view database records, or perhaps insert database records. There are a few fundamental things you have to do to.

First, of course, install and set up your database program, whether it’s PostgreSQL, MySQL or one of the other products. Help on configuring and installing the databases are available at their respective web sites, or on Geocrawler.com, which is a large mailing list archive. Try searching the archives on Geocrawler before asking a question on the mailing list orSupport Forum.

1. Connect to the database
2. Check for errors
3. Create your SQL statement
4. Execute your SQL
5. Check for errors
6. Display results

Those are the fundamental steps you need to do with PHP to retrieve and display records from a database.

Here’s the PHP code to do it with PostgreSQL (MySQL is pretty similar. For the most part, just replace pg_ with mysql_ )

<?php
/*
	Connect to the PG database. I use persistent connections
	to speed things up. Persistent connections will stay active
	for as long as the Apache process is active, if you are
	using the Apache Module version of PHP. If you are using
	PHP as a CGI, persistent connections are useless.
*/

$conn = pg_pconnect("host=db.company.com user=my_name 
	password=my_passwd dbname=db1");

/*
	Check for errors. Actually, if there is an error the script 
	would have died above. I do this out of habit, and I actually 
	have the code in an include so I don't have to retype it.
*/

if (!$conn) {
    echo "An error occured.n";
    exit;
}

/*
	Create your SQL statement
*/

$sql="SELECT * FROM my_table;";

/*
	Execute your statement
*/

$result_set = pg_Exec ($conn, $sql);

/*
	Check how many rows were returned
*/

$rows = pg_NumRows($result_set);

if ((!$result_set) || ($rows < 1)) {
	//No connection or no rows returned, so print an error
	echo "<H1>ERROR - no rows returned</H1><P>";
	exit;  //exit the script
}

/*
	Display the results. The easiest way is to use pg_result
	pg_result uses the $result_set created above and you give
	it the column name and row number that you want to retrieve

	The following code loops through the result set displaying
	a particular column. $rows was created in an earlier step.

	Of course, you can get really elaborate and incorporate
	HTML into this. I'm leaving that out to simplify the code.
*/

for ($j=0; $j < $rows; $j++) {
	echo pg_result($result_set, $j, "column_name");
}

?>

And that’s it. A similar technique is used to insert data. To do that, you need to understand how variables are passed from a page to a response page.