PHP111

Main Page >> Interactive Systems Development >> Workbook >> xxx

PHP and Databases – Making the Connection

To execute queries on a database is made up of 2 parts:

1. Send the Query to the Database
2. Handle the response

Connecting to the MySQL Database Server

Firstly, to connect to a database we use the following:

$dbServer=mysql_connect ("localhost","cp1079_student","");

• The parameters passed to the mysql_connect statement are:
  • The MySQL Database Server Address - this is usually running on the same machine as PHP, so "localhost" can be used
  • A Username
  • A Password (in this instance, there is no password for the user "demo")
• $dbServer is a variable we use to hold the connection to the database server. It’s use will become clearer as we progress.

Checking the Connection

To check to make sure the connection is OK, you can use the following:

if (!isset($dbServer))
{
  print ("failed to connect to server");
  exit;
}

• if $dbServer is not set - i.e. is empty - no connection exists to the database server

Choosing a database on the MySQL Database Server

Next, we choose the database we want to use with the following:

mysql_select_db("cp1079",$dbServer);

• The parameters passed to the mysql_select_db statement are:
  • the database name we want, in this case cp1079 is the name of the database
  • the database server connection that we have already opened - $dbServer

Executing a Query

To execute a query, first we have to prepare the query. This is best accomplished using a string variable.

$sql = "SELECT name, species, age FROM pet";

When we execute the query, we need to handle the response from the Database server, so we need to use a special variable called a resource

$queryResult = mysql_query($sql);

• $queryResult is the resource that holds the response from the Database server
• $sql is passed to the Database server using the command mysql_query

Did it work?

We can test to see if the query executed correctly at the Database server by using the following:

if (mysql_error())
 {
   print ("There has been an error<BR>".mysql_error());
   exit;
 }

• mysql_error() is a function that will return any error messages generated by the database server based on the last instruction
  • i.e. the mysql_query was the last instruction, if this generated errors, mysql_error() will retrieve the error message
  • so if(mysql_error()) means, if the last instruction caused errors, do the following...
  • in our example, we output the error message and exit the program if an error was caused

Assuming it worked...

We can now check to see how many rows have been returned using the following:

print mysql_num_rows($queryResult)." records returned<BR>";

• this will output the number of records returned in the $queryResult

8 NOTE: - this will only work for SELECT queries, as only SELECT queries return records

Manipulating the Records returned

The most common way to manipulate the returned records, is to extract the first record returned and convert it into an array. We do this with the following:

$dbRecord=mysql_fetch_array($queryResult);

• mysql_fetch_array extracts the first record from $queryResult into an array called $dbRecord
• $dbRecord will be an array containing ONLY the first record returned, not all of the records, just the first

Printing out records returned

As we have SELECTed three fields (name, species, age) from the table, there will be three elements created in our array. To print out the first element (name) we can use:

print $dbRecord[0];

• THIS DOES NOT PRINT OUT THE FIRST RECORD - this ONLY prints out the first element of the array (or first field of the record) of the first record returned from the database

To print out the second field of the first record:

print $dbRecord[1];

and so on…..

Easier access to the fields in the records

The mysql_fetch_array statement also names the elements in the array as well.

$dbRecord[0] is the same as $dbRecord["name"]
$dbRecord[1] is the same as $dbRecord["species"]
$dbRecord[2] is the same as $dbRecord["age"]

Putting it all together

<?
   $dbServer=mysql_connect ("localhost","cp1079_student","");
   if (!isset($dbServer))
   {
     print ("failed to connect to server");
     exit;
   }
   mysql_select_db("cp1079",$dbServer);
   $sql = "SELECT name, species, age FROM pet";
   $queryResult = mysql_query($sql);
   if (mysql_error())
   {
     print ("There has been an error<BR>".mysql_error());
     exit;
   }
   print mysql_num_rows($queryResult)." records returned<BR>";
   $dbRecord=mysql_fetch_array($queryResult);
   print "Name is ".$dbRecord["name"]." and species is ".$dbRecord["species"]." and age is " .$dbRecord["age"];
 ?>

BUT...

This only prints out the first record - our sql query asked for all the records from the pet table.

We need a loop to loop through all the records returned. Try replacing the last two lines of the code above with an elegant loop to iterate through the records regardless of how many records are returned, like this:

while ($dbRecord=mysql_fetch_array($queryResult))
 {
   print "Name is ".$dbRecord["name"]." and species is ".$dbRecord["species"]." and age is ".$dbRecord["age"]."<BR>";
 }

Ready to try some more PHP/MySQL?

A simple example of using a HTML form to capture search requirements and execute a custom query can be found at PHP112 - Simple HTML/PHP/MySQL example