Interfacing with MySQL using PHP

Summary

PHP can be used to write dynamic Web pages that can connect and communicate with a MySQL server. PHP has built-in functions for the task, while Perl has a module called DBI for interfacing with databases like MySQL. This article demonstrates some simple examples of how to use PHP as a separate front-end for MySQL.

Note: You must install and start your MySQL server before you can retrieve data from it. Today we will be using my MySQL as part of the lab.

Included on this page:

MySQL Scripting With PHP

For those just starting out, PHP may be the easiest way to learn MySQL scripting. To try your hand at a PHP/MySQL script,

  1. Here is an example PHP script that connects to your MySQL server and prints out the first row of the employees table from the employees database. Copy and paste the following code into a file called "more-mysql.php", make the appropriate substitutions for the highlighted portions, and then upload the file to a location in your Web directory.

    <html>
<body>
<?php

// Connect to the database
$db = mysql_connect("("host.u.washington.edu:port number", "root", "rootpassword");
// Select the database
mysql_select_db("employees",$db);

// Query the database with an SQL query statement
$result = mysql_query("SELECT * FROM employees",$db);

// Print out the results
echo "<p>Lets print out the first row in the result set:</p>";

printf("First Name: %s<br>\n", mysql_result($result,0,"first"));
printf("Last Name: %s<br>\n", mysql_result($result,0,"last"));
printf("Address: %s<br>\n", mysql_result($result,0,"address"));
printf("Position: %s<br>\n", mysql_result($result,0,"position"));

?>
</body>
</html>

  2. Be sure to replace host with either vergil or ovid, port number with the port your MySQL server is running on, and rootpassword with the root password you selected when you set up your MySQL server.

  3. Save the file as "more-mysql.php" in your public_html directory. When you view the file in a Web browser, you should see something similar to the following:

    Lets print out the first row in the result set:
    FirstName: Bob.
    LastName: Smith
    Address: 128 Here St. Cityname
    Position: Marketing Manager

    This tells you that your script successfully connected to the MySQL server. It then selected the database "employees", and then output the First Name, Last Name, Address, and Position of the first row in the "employee" table.

    The syntax of the printf function may seem a little odd if you haven't used Perl or C before. In each of the lines above, %s indicates that the variable in the second half of the expression (e.g., mysql_result($result,0,"position")) should be treated as a string and printed. For more on printf, see the PHP documentation.

  4. Now, lets print out all of the rows of the table, by looping through all of the rows in the database. Edit your more-mysql.php file and add the following code:

    echo "<hr />";
echo "Now lets loop through all of the rows in the table\n";

// Loop through the results and print in a table
echo "<table border=1<\n";
echo "<tr><td>Name</td><td<Position</tr>\n";
while ($myrow = mysql_fetch_row($result)) {
	printf("<tr><td>%s %s</td><td>%s</td></tr>\n", $myrow[1], $myrow[2], $myrow[3]);
}
echo "</table>\n";

    The mysql_fetch_row() function bears a closer look. One small problem with mysql_fetch_row() is that it returns an array that supports only numeric references to the individual fields. So the first field is referred to as 0, the second as 1, and so on. With complex queries this can become something of a nightmare.

  5. Can you figure out why all of the rows in the table are NOT printing out?!?
    Hint: Try looking up the mysql_seek_data() function

  6. But if the query returns no data, we have no way of letting the user know, and we should probably provide some sort of message. This is possible, so let's do it. 

    if ($myrow = mysql_fetch_array($result)) {
  echo "<table border=1<\n";
  echo "<tr><td>Name</td><td<Position</tr>\n";
  do {
    printf("<tr><td>%s %s</td><td>%s</td></tr>\n", $myrow["first"], $myrow["last"], $myrow["address"]);

  } while ($myrow = mysql_fetch_array($result));
  echo "\n";
} else {
  echo "Sorry, no records were found!";
}

    We can also refer to the keys in the associative result array.

  7. Finally, we may want to add some new data to our existing database. Do do this we construct and SQL "query" using an INSERT INTO line. Try adding the code below to your more-mysql.php script.

    Now, you will have to have initialized the variables $first, $last, $address, and $position before you can use them!

     $sql = "INSERT INTO employees (id, first,last,address,position) VALUES ('', '$first','$last','$address','$position')";
 $result = mysql_query($sql);
 echo "Thank you! Information entered.\n";

Resources

The scripts in this article demonstrate the most basic MySQL connection and query functions. For more information about PHP and DBI module functions, refer to the following links.

PHP
PHP.net has official MySQL Documentation; the Webmonkey PHP/MySQL Tutorial is a gentle and methodical introduction to PHP scripting for MySQL.

Perl DBI
Perl.com has a very good Short Guide to DBI.