While there are advantages to using point-and-click tools such as DreamWeaver to create database connectivity on a web page, doing so separates limits customization choices while maximizing the number of steps required to get results (“where was that option in the menu again?”). Generally speaking, hand coding a database connection is faster and creates more reliable code using fewer lines. It also allows you to actually understand what you are doing, rather than pushing buttons.

In the steps that follow, I’m assuming that you have a MySQL server, and have created a database table with information inside it.

First, we are going to write this script on a single PHP page, the same page on which we are going to show database results. While this isn’t terribly efficient for a site in which multiple pages draw information from a database, it does avoid the issue that I discussed in Creating a MySQL Connection In DreamWeaver: that of leaving your connection information “in the clear” as an unsecured include file for anyone to find.

Obviously, we must establish our database connection, draw information from a table, and translate it into data that we can use before we use the information anywhere on our web page: for that reason, I usually write the connection script at the very start of the page.

The first line of PHP code looks like this. (Note that I’m using the short version of the opening PHP tag to save a little space).

<? $myDatabase = mysql_connect("mysql_server", "user", "password") 
	or trigger_error(mysql_error(),E_USER_ERROR); ?>

This establishes a connection to your MySQL server, returning an error if no connection can be made. (Of course, you’d replace mysql_server, user and password with the appropriate information.)

The next line chooses the database you wish to draw information from. Again, you’d replace database with the actual name of your database.

<? mysql_select_db("database", $myDatabase); ?>

It is the two lines above that would typically be used in an include, as we would require every page that interacted with a database to have them. The lines that follow would usually be unique to each page.

<? $query = "SELECT * FROM table"; ?>

Once working with MySQL becomes familiar, you’ll find that you will spend most of your time tweaking queries.  At the very least, you’d need to replace table with the actual table name.

Now we need to execute this query on our established database connection:

<? $mydata = mysql_query($query, $myDatabase) or die(mysql_error()); ?>

This retrieves our data from the MySQL database table, showing the cause of the error if the process cannot be completed. The information that is brought back from a SELECT is “raw data” – we need to convert it into a state we can use with PHP. The next line does just that, transforming the first row into an array, labelling each slot in the array with the name of the column the information relates to:

<? $myrows = mysql_fetch_assoc($mydata); ?>

That’s essentially it. The entire code is:

<? $myDatabase = mysql_connect("mysql_server", "user", "password") or 
	trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db("database", $myDatabase);
$query = "SELECT * FROM table";
$mydata = mysql_query($query, $myDatabase) or die(mysql_error());
$myrows = mysql_fetch_assoc($mydata); ?>

So long as we know the names of the columns, we can print out the data from any column in the first row of retrieved data by using:

<?=$myrows['column_name’]?>

Note that this only works for the first row of data that is returned by our query (for example, details of just one person from a users database table). If we have more than one row of information that we wish to display, we will have to use some kind of loop to cycle through them… which will be discussed in the next entry for this series.

Photograph by Christian Weidinger, used under a Creative Commons Attribution-NonCommercial-NoDerivs 2.0 Generic license

Enjoy this piece? I invite you to follow me at twitter.com/dudleystorey to learn more.