PHP & MySQL

<? PHP & MySQL?>

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 and to authorize our access to 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 = "xxxxxxxxx"; //your MySQL password (the one you made up!)
$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. Later we'll relocate this data to a configuration file, then reference that file in every database oriented page, so we can limit our changes to one location.

PHP MySQL Functions

PHP has MySQL specific functions to allow us to connect and retrieve data. We'll start with the first and in many cases arguably the fastest set of connection functions, all of which begin with the prefix mysql_.

Database Retrieval Steps

As in many server side languages, using the mysql_ functions we connect and retrieve our data in distinct stages. We can break these stages into the following steps:



Connect to MySQL, authenticate the MySQL users

Connect to the Database, verify authorization to this resource

Select data to be retrieved via SQL statement

Retrieve data set (result)

Loop through the data, and insert it into our page

Disconnect from MySQL, and release resources

Connecting To MySQL

To connect to MySQL, we'll use the mysql_connect() function. We'll use the variables we created to provide our MySQL username and password. 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 supresses error msg

Note we are also providing $myHostName, which is the string "localhost" only on Zephir, since the web server and database server are the same machine. The name of the server is nearly always required when using a hosting company, and frequently looks like mysql.mycompany.com. Check with your hosting company for the name of your MySQL server.

We then use a 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.

Where It's @

Notice the @ symbol placed in front of the mysql_connect() function above. That symbol attempts to suppress error reporting in PHP, on a line by line basis. This is called the error control operator, and is helpful in preventing PHP from exposing potentially damaging information in a an error message to hackers.

However, when we suppress errors, we limit our ability to see errors in troubleshooting. We can use another PHP specific function or die() to indicate code to run if an error is encountered:

$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. While this is handy for troubleshooting, it still exposes too much info. We'll start handling our own errors next.

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: " . $myFile . " on line: " . $myLine . " ";
 print "Error Message: " . $errorMsg . " ";
 die();
}

This gives us the ability to handle errors in a consistent manner, and to change how we handle errors in one place. However, this still exposes our errors to the public!

Beginning Error Handling

When we create a function in PHP, the only data available by default inside the function are the parameters/arguments we pass into the function, and the superglobals, such as $_POST & $_SERVER. However, we can invite any available variable from outside the function into our function by listing a comma separated set of the variables (by name) after the keyword, global:

global $myVar,$myOtherVar;

Using this, we can declare a variable at the top of our page:

$hidePageErrors = TRUE;

And then change our error handling function to only expose errors when we wish:

function myerror($myFile, $myLine, $errorMsg)
{
global $hidePageErrors;
 if($hidePageErrors)
 {
 print "I'm sorry, we have encountered an error!";
 die();
 }else{
 print "Error in file: " . $myFile . " on line: " . $myLine . " ";
 print "Error Message: " . $errorMsg . " ";
 die();
 }
}

Now 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()));

Note the use of the constants __FILE__ and __LINE__. These so called magic constants allow us to dynamically determine where in the code the error is coming from, even when we add and subtract lines of code!

Selecting The Database

So far we've been authenticated as a MySQL user, but we haven't identified which database we wish to use. Next we'll attempt to connect to a database via the mysql_select_db() function:

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 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 mysql_query() function:

$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_assoc($result))
{ //pull data from array
echo "FirstName: " . $row['FirstName'] . " ";
echo "LastName: " . $row[LastName'] . " ";
echo "Email: " . $row[Email'] . " ";
}

The area above is where most of our 'action' is. Inside the while loop we can intersperse HTML & database data. The last thing we should do is disconnect from the database and release resources via the mysql_close() command:

mysql_close($myConn);

If we don't specify the connection ($myConn) the function will close the last available database connection. Note that in the documentation of PHP they claim it's alright not to explicitly close connections, but it is always a good practice to release resources immediately, and can lead to efficiency issues and major problems in other environments, such as .NET. Only lately (and for advanced reasons you'll see later this quarter) have I come to the conclusion to not close mysql_ connections, in PHP. We'll talk about why this is later.

Fetch Functions

We have multiple ways we can retrieve the data from our result. Above we knew the names of the database fields in advance, therefore we used mysql_fetch_assoc(), which will return an associative (named) array of fields. If we didn't know the names of the fields, we could use mysql_fetch_row(), which returns only values by numerical array indices (index) or we could have used mysql_fetch_array() to retrieve both versions at once.

Below is our class example:

first data.php View Code

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 mysql_num_fields():

$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 using mysql_field_name():

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

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(). 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!

firstTable.php View Code

MySQL Improved (mysqli_)

At the php.net website, they recommend using what is the called the MySQL Improved (mysqli_) functions to connect to MySQL. This set of similar functions can do several things not provided by the original mysql_ functions. We'll cover mysqli_ later.

For an overview of more advanced PHP/MySQL functions, view the PHP/MySQL API To see how PHP connects to other databases, view the PHP Extensions.

Print this Page