PHP Database access

PHP has native connections to about a dozen kinds of database, each with their own set of function for preparing, performing and interpreting functions, though you should make your life easier and learn the ODBC functions, or using the PEAR DB or PEAR MDB modules instead - then you have a single set of functions that work really well on any database and all you need is an ODBC driver for that database. PEAR is the PHP equivalent of Perl's CPAN - a central repository of code modules that can be downloaded and updated via a command-line tool. Check out the PEAR website to see about installing it - DB is part of the default install, and anyone who's had enough of PHP's database-specific quirks will find it refreshingly consistent. MDB is newer and so supports fewer databases, but has many more features. Using either of these modules requires you to know how to create and modify objects though, and that's for another tutorial. For now I'll show the "old method", which is the ODBC functions.

In PHP, to get data from a database, you create a connection to the database and tie it to a variable. Then every command you want to send to the database uses that link identifier (meaning you can connect to multiple database in the same script, see?)

For an ODBC connection, you have to create a system DSN using the ODBC administrator -- you'll find that in the Control Panel on windows (administrative tools on Win2k). Once you've created the DSN, then you can use the odbc_connect() or odbc_pconnect() functions. The difference between them is that the latter is persistent - if PHP is in memory for more than one script, it will keep pconnects open after the first script exits, and then if another script requests a connection using the same username/password and database, it'll recycle the connection. This can save a lot of time if you're contacting a remote database. On the CGI version of PHP though, ALL connections are closed when PHP quits at the end of the script so there's no difference.


ODBC Database connection:

$link_id = odbc_connect("DSN", "username", "password");

or

$link_id = odbc_pconnect("DSN", "username", "password");

or more safely, if your page NEEDS a database connection:

if (! ($link_id = odbc_pconnect("DSN", "username", "password")) ) {
die("Couldn't connect to database. Reload the page");
}

This basically quits if a connection fails. You could go a step up and show some HTML that waits a few seconds and reloads the page.

Concepts: result pointers

I'm going to assume you know SQL since you wouldn't want to be connecting to a database if you didn't. There's SQL tutorials all over the place, and it's very simple (to start with, anyway NOTE: A lot of tutorials I've seen preach in mySQL, and tell you how to do stuff like create and alter databases in PHP, which is stunningly pointless -- the only things that should be pissing around with your DB design/structure are administration and design tools, or the mySQL interface. These functions are only useful for people writing one of the above; skip those bits.

When you have an open database connection, you need to run a query and catch the result pointer. PHP runs the query for you and creates a place in memory to store the result. You can use a whole bunch of functions to move around in, fetch and display and otherwise mess with the result by using this pointer, just like the database link identifier. The most useful functions are

odbc_exec()


Run a query

odbc_fetch_into()


Get a row from the query result, and store it in an array.

odbc_result_all()


Prints an HTML Table with the entire database result - really handy for testing, but not really pretty enough for production use.

Look up the others in the manual under "Unified ODBC Functions", there's some funky stuff in there.

This little example creates an HTML table based on the result of the query given. You can put any query you like in there instead - the code can display any number of rows and columns. It's a custom version of odbc_result_all(), in fact.

if ($result = odbc_exec($link_id, "SELECT username, password FROM mytable ORDER BY username") ) {
/* $result was set, so the query worked. Beware that the result might
* actually be empty - a working query doesn't have to return anything,
* so don't assume anything =^.^= */

// Start an HTML table
echo "<TABLE border=1 cellspacing=1>\n";

// Build a header row (with the field names)
echo "<TR>";

for ($field_num = 1; $field_num <= odbc_num_fields($result); $field_num++) {
echo "<TH bgcolor=silver>" . odbc_field_name($result, $field_num) . "</TH>";
}
echo "</TR>\n";

/* Now show the data in the result.
* odbc_fetch_into() builds an array with every field in the row,
* and join turns an array into a string by joining every item in
* the array with a set string. The first and last items in an
* array, of course, don't have the joining string attached, which
* is why the line below echos "<TR><TD>" and "</TD></TR>" to start
* and end the HTML table row. odbc_fetch_into() returns false when
* there are no more rows to show, so it's good for a while loop,
* which stops when there are no more rows. */

$row_num = 0; $row = array();
while ( odbc_fetch_into($result, $row_num, $row) ) {
echo "<TR><TD>" . join("</TD><TD>", $row) . "</TD></TR>\n";
$row_num++;
}

// End the table
echo "</TABLE>";

} else {
echo "Database error";