Displaying MySQL Data via PHP

Authentication vs Authorization

Authentication determines who we are. Authorization determines the level of access we may have to a resource. When we connect to a database such as MySQL from PHP, we need to pass information to MySQL to authenticate who we are, to authorize us to access a database.

Gaining Access

With Zephir, our administrator has setup a single database, and a single MySQL user, that both share the same name. Before we gain access to MySQL we're required to set a MySQL password. Since Zephir hosts both the web server and the database server, we can use the word localhost to indicate the MySQL program is installed on the same machine we are using as a web server. This is almost never the case when we obtain our own hosting. Once we have our own host, we will rely on the administrators of the hosting company to provide MySQL access.

Here is where to start with a few of the hosting companies you may be using: Connecting to MySQL via Dreamhost ~ Freehostia MySQL Forum ~ MySQL on GoDaddy

Configuration Data At The Top Of The Page

One way to do this is to declare variables for each of the items we need at the top of a page, and input the specific data we need there. Then, for any database hit on the page, we use the same variables again, and can change the configuration data in one place:

$myHostName = "localhost"; //use localhost for same machine, (zephir) or name given to you by hosting company
$myUserName = "horsey01"; //your MySQL (or Zephir) username
$myPassword = "xxxxxx"; //your MySQL password
$myDatabase = "horsey01"; //db name, which is the same as our username on Zephir

We then can declare a SQL statement to filter our data:

$selSQL = "Select * from tblCustomers";

We place this data toward the top of the page so we can easily change it.

Next we'll use our variables to connect to the database. This requires similar information to logging in via the command line (MySQL monitor). We attempt to create a connection to MySQL, and store the active connection in a variable called $myConn:

$myConn = @mysql_connect($myHostName,$myUserName,$myPassword); // "@" symbol stops error msg

We then use the new variable, "$myConn" as the measure of whether we have connected to the database, and in fact it IS the active connection to the database, until the page is processed, or we take steps to close that connection.

We can add to our code to help us deal with errors in a convenient manner:

$myConn = @mysql_connect($myHostName,$myUserName,$myPassword) or die(mysql_error());

Note the "or die()" allows us to handle error processing as we wish. In this case, we print a message returned via MySQL to indicate the type of error, and stop processing on a page. 

Error Handling Function

Frequently it makes sense to handle an error with a function of our creation. This way we can treat errors in a consistent manner, and later hide errors from the public. Here we show a function named "myerror()".

function myerror($myFile, $myLine, $errorMsg)
{
    print "Error in file: <b>" . $myFile . "</b> on line: <b>" . $myLine . "</b><br />";
    print "Error Message: <b>" . $errorMsg . "</b><br />";
   die();
}

We can add to our code to help us deal with errors in a convenient manner:

$myConn = @mysql_connect($myHostName,$myUserName,$myPassword) or die(myerror(__FILE__,__LINE__,mysql_error()));

For the time being, we will just print the results of the error to the screen.  Later on, we'll determine a more unified (and safer) way to handle errors.  Note the use of the constants __FILE__ and __LINE__.  These allow us to dynamically determine where in the code the error is coming from, even when we add and subtract lines of code!

Next we will find out if we have permission to connect the specific instance of the database:

@mysql_select_db($myDatabase,$myConn) or die(myerror(__FILE__,__LINE__,mysql_error()));

Here we have attempted to connect to a specific database ($myDatabase) with the connection string we created earlier ($myConn). Remember we can be a valid MySQL user, but not have access to a specific database, for example, a database of another developer!

Extracting Data

Once we have passed the 2 hurdles of MySQL and DB connection, we can get to the business of extracting data. To apply the SQL statement specifying what data we want, and to store that data we use the following command:

$result = mysql_query($selSQL,$myConn) or die(myerror(__FILE__,__LINE__,mysql_error()));

The variable $result will now either store a virtual "table" of data in the memory of the server, or the die statement will print an error message to the screen for us. Think of this "table" of data as the rows and columns of a spreadsheet. The data is in the form of an Array, which can be looped to show the data:

while($row=mysql_fetch_array($result))
{ //pull data from array
    print "FirstName: " . $row['FirstName'] . "<br />";
    print "LastName: " . $row[LastName'] . "<br />";
    print "Email: " . $row[Email'] . "<br />";
}

The developer must know the names of the data fields he expects which are specified in the $row array. The while statement loops through all the rows of data, and the developer accesses any fields (columns) he wishes to use. This method is approriate for one or more records, and will be our most common means of extracting data to show on a page. Below is our class example:

Displaying a Data Table of Indeterminate Size

On occasion we will want to connect to a database table, but not will not know how many fields (columns) of data we will get back. A perfect example of this is when we need to create an administrative interface to be able to make changes to the data via the web. To do this, we will need to use a different technique to get our data.

The following example connects to the database in the same manner as previously. One difference is that we need to determine the number of fields (columns) we are expecting from the SQL statement:

$numColumns = @mysql_num_fields($result);

We don't yet know the names of the database fields, but we can extract these, and print them to the page in a loop:

for($x=0; $x<$numColumns; x++)
{ //grab and print each field name
    $fieldName = mysql_field_name($result,$x);
    print $fieldName . "<br />";
}

These can be the "top" or TH for an HTML table identifying the data we are extracting. Then we create 2 loops, one inside the other to display the actual data. The outer (first) loop will start at the first row, and proceed until each row has been processed. Inside the processing of each row, we have a loop to show the data for each particular field in that row:

while($row=mysql_fetch_row($result))
{ //pull data from array
    print "<tr>";
    for($x=0; $x<$numColumns; x++)
    { //print out data
        print "<td>" . $row[$x] . "</td>";
    }
    print "</tr>";
}

Note the call to the database uses mysql_fetch_row instead of mysql_fetch_array. This is because we are not extracting the row data by name. Instead we are using the offset of the array, starting with the first array value of zero for the first field. We don't need to know what these field numbers are, since the loop takes care of that for us!

Also notice the HTML table pieces that are in the above code. To be able to display a 2 dimensional table, it is frequently useful to use an HTML table. The naming similarity is no coincidence!