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 then to authorize our access to a database, for example. To work with MySQL we almost always need 4 credentials:

The MySQL Server Name

The MySQL UserName

The MySQL UserName's password

The name of the database

ZephirLand: When working with Zephir, our administrator has setup a single database, a single UNIX user and a single MySQL user, that all share the same name. This is the name you login to the computers at school with as well. In our code examples we call this "horsey01".

On Zephir, 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 must use the word localhost to indicate the MySQL Server Name. This indicates the MySQL program is installed on the same machine we are using as a web server. This is unusual 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, instructions and credentials.

MySQL on Other Hosting Companies: A web hosting company that offers MySQL usually provides instructions for connecting to MySQL. Some companies allow you to create your own database. Some companies only allow one database and give you the name. If you are not sure how to use MySQL on your host, look for instructions, FAQs or a forum discussing MySQL. If you're unable to find what you need here's some MySQL info on a few popular hosting companies: Dreamhost ~ Freehostia ~ GoDaddy ~ BlueHost ~ HostGator ~ 000webhost

Connect via phpMyAdmin or phpMinAdmin: Once you have credentials it's a great idea to test them. Try to use the credentials to connect to MySQL via phpMyAdmin (provided by the host) or via phpMinAdmin (installed by you). Once you connect to MySQL, you could setup a database table with data, so your PHP page has data with which to work.

Configuration Area: When we create a PHP page that will connect to MySQL we'll first store our 4 required database credentials in variables near the top of our PHP page:

$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:

$sql = "select * from test_Customers";

We place this data toward the top of the page so we can easily change it. It's common to reserve the top of a PHP page as a configuration area for instructions and a place to load variable data.

credentials.php: Once we have tested our page and can connect to the database and pull data from it, we'll want to move our database credentials into an include file, perhaps named credentials.php. Once we do this we no longer need to copy and paste the data to each page and can change it if we need to move our application to a different server. Further we don't need to worry about someone seeing our login info from behind us (shoulder surfers).

PHP Database Functions: PHP has many sets of functions to handle connection and data retrieval from several databases such as MySQL, SQL Server, PostgreSQL, Oracle and others. Most database specific functions begin with a string to identify the database, plus an underscore and then a word that identifies the task for example "connect" or "query".

PHP MySQL Functions: PHP's mysql_ database functions (also called extensions) were the standard way to connect to a MySQL database. However in recent years PHP has come out with a new improved version of the mysql_ connection, which is called mysqli_, wherein the "i" stands for improved. Many (but not all) of the same command names exist for mysqli_ as they did for mysql_. Officially PHP recommends using the mysqli_ connection type when possible.

There are times we may still wish to use the mysql classic connection, however. One reason is the mysql classic connection could only process one SQL statement at a time. This could be a good thing, depending on use. View the following cartoon for an illustration:

We'll first review the original mysql_ connection type (now called mysql classic) and get to the similarly used mysql improved later.

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

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.

What is returned from the mysql_connect() function is the active connection to the database. We store the connection in the variable named "$myConn" to remind us we are using a mysql classic connection, not an improved connection ($iConn). The connection will persist until we purposefully close the connection of the page is done processing.

Selecting The Database: So far we've been authenticated as a MySQL user and have our connection, 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);

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

$result = mysql_query($selSQL,$myConn);

The variable $result stores an object that provides a virtual "table" of data in the memory of the web server. Think of this "table" of data as the rows and columns of a spreadsheet.

Fetch Functions: We have multiple ways we can retrieve the data from our $result. In our case we know the names of the database fields in advance, therefore we'll use 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.

while() Loop: Along with the fetch function, we use a while() loop, which is very similar to a foreach() loop in use. The difference is we'll use a variable name $row[] as the indicator of the array that is returned for every row of data returned. We then can access each field ("FirstName", "LastName", etc.) while we are examining each row:

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 work as developers will occur. Inside the while() loop we can intersperse HTML & database data. The two areas that we as developers will do our main work is here, inside the loop, and inside the SQL statement, which in our case is stored in a variable named $sql. These are the first two places to look for errors!

What if there are no records?: The while() loop above works great, as long as there is data returned. What if our SQL statement doesn't return any data? This is not an error, just a condition of our ever changing data.

mysql_num_rows(): The $result object referenced above is more than just a table of data. It is a full fledged object that we can use in various ways. One thing we would like to know if whether or not there are any rows data to be returned from our SQL statement. It's possible to ask the $result object how many records are available from our SQL statement. If there are records, we loop. If not, we can now pass a message to the page to at least indicate that no data was found:

if (mysql_num_rows($result) > 0)//at least one record!
{//show results
 while ($row = mysql_fetch_assoc($result))
 {
 print "";
 print "FirstName: " . $row['FirstName'] . " ";
 print "LastName: " . $row['LastName'] . " ";
 print "Email: " . $row['Email'] . " ";
 print "";
 }
}else{//no records
 print '<div align="center">What! No customers? There must be a mistake!!</div>';
}

Releasing Resources/Closing Connection: Once we are done placing data on our page, its a good idea to consider releasing the valuable resources we are using. The $result object that stores our database data exists in memory on the web server, not the database server. When we are done placing our data on the page, we should explicitly release this data so the memory is available for other processes:

@mysql_free_result($result); # releases web server memory

Note the proper use of the @ symbol in this case. PHP is by default a 'chatty' language which could emit a warning (right in the middle of a page being delivered) to explain it dumped the $result already based on server circumstances. We don't want a user to see this 'warning' so we'll 'squelch' the possible warning message.

The last thing we could do (but won't usually) is disconnect from the database 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.

Don't Close Connections in PHP: I've come to the conclusion to not close mysql_ connections, in PHP. This is because PHP has a unique ability to share a single connection with multiple applications by default. We can trust PHP to close the connection on it's own when the page is complete, contrary to almost all server side lore. Doing this on a Microsoft application (ASP classic, ASP.NET) will clog web server memory and take down your site and perhaps the entire server.

Error Handling: While we are working with a database through PHP, the structure of the database can be changed. PHP has no way to control or determine if the database was changed in such a way as to create an error in our application. Therefore a PHP application should defend itself from errors connecting to the database, determining if we have authorization to view a particular database or if the underlying database structure has changed making our SQL statements invalid.

Where It's @: Notice the @ symbol placed in front of the mysql_connect() function below:

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

The @ 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.

do or die(): 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 indicate code we wish to run in the event of failure. In this case, we print a message returned via MySQL to indicate the type of error, and stop processing on a page. The die() function is designed to halt execution of the page. The remainder of the HTML will not then be delivered to the page, including any footer!

While this is handy for troubleshooting, it exposes too much information to users. We'd like to be able to control how much data is shown about errors.

An 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.

Turning On and Off Error Reporting: Next we'll adapt our error handling function so that it allows us to turn on or off the ability to see explicit error messages. When we are developing we wish to remove errors (debug/troubleshoot) but when the site is live we wish to show a much less dangerous error message.

The global Keyword: When we create a function in PHP, the data available by default inside the function are constants declared before the function is called, 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 handle errors when we attempt to connect to the database:

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

Magic Constants: 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!

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/index 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!

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