I have a secret about Web-based databasing.

Are you ready? Here it is:

It's pretty easy.

Shocking? Probably. But just read through this simple guide, and the world of Web-based databasing will open at your feet. Plus, let's face it--knowing Web-based databasing will make you a sexier person. It's true.

Ready? Read on.

There are really three simple steps. Connect to the server, pick your database, and run your query.

First, make a connection to the database server:

<?php
mysql_connect( "server", "username", "password" );
?>

Second, pick the database you need to use:

<?php
mysql_select_db( "databasename" );
?>

You are now connected to the database and can run queries on it.

<?php
$rs = mysql_query( "SELECT * FROM table_name" );
?>

You can also, of course, build the query as a string variable, and pass the variable as the argument to mysql_query():

<?php
$query = "SELECT * ";
$query = $query . "FROM table_name";
$query = $query . "WHERE field = 3";
$rs = mysql_query( $query );
?>

In either case, with a SELECT query a recordset consisting of the selected rows is returned and stored in the variable $rs (which, as it turns out, is a two-dimensional array--but that is beside the point).

Think of the variable $rs as a spreadsheet. Each row contains one record returned by the SELECT query, and each column contains one field from the database table that was queried. We can extract one row, or record, of the spreadsheet at a time by using the following command:

<?php
$row = mysql_fetch_assoc( $rs );
?>

This will take the row where the cursor is pointing (the first time, it is pointing to the first row), extract it to the associative array$row, and move the cursor to the next record. We can then access the contents of the record stored in $row by using the table's field name as an array subscript, like $row[ "field_name" ].

When do we stop fetching new rows? Well, the cool thing is that ifmysql_fetch_assoc() has run out of rows, instead of returning an array to us, it will return the value false. So, if $row equals false, we know that we have come to the last record. Cool, no?

Example

Let's assume we have a table named Clients with the fields ClientID, LastName, and FirstName. We want to build a table that shows all the information for all the clients whose last name is Jones. Our database is named MyCompany and we will access it with the user nobody having a password charlie. Our code will look like this:

<?php
mysql_connect( "localhost", "nobody", "charlie" );
mysql_select_db( "MyCompany" );
$query = "SELECT * FROM Clients WHERE LastName = 'Jones'";
$rs = mysql_query( $query );
?>
<!-- Just normal HTML for a spell.-->
<table border = "1">
<tr>
    <td>Client ID</td>
    <td>First Name</td>
    <td>Last Name</td>
</tr>
<!-- We now return to our regularly scheduled PHP code. -->
<?php
while( $row = mysql_fetch_assoc( $rs ) ) {
    echo( "<<tr>" );
    echo( "<td>" . $row[ "ClientID" ] . "</td>" );
    echo( "<td>" . $row[ "FirstName" ] . "</td>" );
    echo( "<td>" . $row[ "LastName" ] . "</td>" );
    echo( "</tr>" );
}
?>
<!-- Some more plain HTML -->
</table>

Ta-dah!! Pretty easy, no? There is one change to the code that will make your life as a web-based database programmer much easier. For most mysql functions that can come to an untimely demise, it is a good idea to add some debugging information. The change is below in bold:

mysql_connect( "localhost", "nobody", "charlie" )
   or die( mysql_error() );
mysql_select_db( "MyCompany" )
   or die( mysql_error() );
$query = "SELECT * FROM Clients WHERE LastName = 'Jones'";
$rs = mysql_query( $query )
   or die( mysql_error() );

That's it. die() causes the script to stop executing after outputting the message in the parentheses. mysql_error() returns a string that contains an explanation of the last error the system generated.

You don't have to do this, but it is frustrating to try to execute a script and nothing--absolutely nothing--happens. If you don't include the mysql_error() message, you will not know what the problem is.

You can also use your own custom messages, like this:

mysql_connect( "localhost", "nobody", "charlie" )
   or die( "Gosh darn it, can't connect to the database server!" );

Typically, however, mysql_error() will provide much more useful information.

A few small things can help here. First, before you execute a query, run the script first with the mysql_query() line commented out, and echo the query to the page. That way you can look at it and make sure it looks kosher before actually executing it against your database.

Secondly, only SELECT queries need to have the $rs variable to receive the returned value. Since UPDATE, INSERT, and DELETE queries don't return a value, there is no need to assign the function's return value to $rs.

That's it. Now you are ready to join the world of databasing on the Web, as a smarter, cooler, and far sexier individual. Follow up with a quick lesson on getting information from a form into a database using the POST method, and your sexiness will know no bounds. That lesson will be found here (once I get it written).