Interfacing PHP and MySQL

What is PHP?

PHP is an exciting addition to HTML that is allowing web designers to create new pages on the Internet that have dynamic content. In other words, each time a page is accessed, the data on that page might contain different information than the last time it was viewed. One of the advantages of PHP is that it can easily call various system functions and include standard programming constructs such as conditionals and loops in order to generate realtime calculations. All of the standard HTML tags work the same as before with this new language, but when the programmer wishes to access the power of PHP, the new code is embeded between two new tags that are created by combining a question mark "?" with either a less than "<" or a greater than ">" symbol. Here is how PHP code appears in an HTML file:

    Standard HTML code ...

    <?     (Begin PHP section)

    PHP Commands go here ...

    ?>     (End PHP section)

    Standard HTML code ...

Another nice feature of PHP is that it handles the processing of HTML form data without using the CGI interface. PHP is especially powerful when used in conjunction with a database that might reside on the server side since varied web pages can be generated using the results from different database queries. The data collected from forms and user input can also be added to that database, thus allowing the website to grow and become more useful over time by the actions of others on the Internet. MySQL is commonly used for PHP database applications on the web, and is the one that we use at Jefferson on our web server.

Realize that this brief introduction to PHP and MySQL is used just to give the reader a few simple examples of the interactivity and power of these two web design features. For more on PHP, please refer to their web site at www.php.net for complete documentation and better tutorials on this language.

A Simple PHP Example

The first example shows how to call a standard PHP date function in order to print out a web page containing the current date. When a file contains PHP, the file name should end with the extender ".php3" rather than ".html" so that the server is aware that there is some additional work to do before sending the web page to the user. To see the actual output and observe PHP in action, click on the link beside the code segment to retrieve the web page, printdate.php3.

Example 1
View the Code:
<HTML>
<BODY>
<H1>Today's Date: </H1>

<?
  print(date("l F d, Y"));
?>

</BODY>
</HTML>
Run the Program:

      printdate.php3


Writing a Form

The following example shows how to enhance the above page in order to create an HTML form that will send data to another PHP script. The this page, form1.php3, is fairly simple and does not really show the power of PHP. The PHP code merely calls the date() function with two different formats in order to retrieve the time, and also prints a few basic HTML tags could have been done outside the PHP framework.

The web page that this form calls will be far more interesting since it will interact with the MySQL database presented earlier in this tutorial and a TABLE called scores. That table resides in a database account called games, and contains the current list of players and their high scores. The form will allow us to view those scores in a variety of ways using but one simple web page.

Example 2: A Simple Form
View the Code:
<HTML>
<HEAD>
</HEAD>
<BODY BGCOLOR = "#FFFFFF">
<CENTER>
<H1>Today's Contest Results </H1>
</CENTER>

<?
  print("<H3> Time Right Now: <BR>");
  print(date("l F d, Y"));
  print(" <BR>");
  print(date(" g:i:s A "));
  print("</H3>");
 ?>


<FORM ACTION = "process.php3" METHOD = POST>

Select a Way to View Data: <BR>
<SELECT NAME = "ViewMethod">
<OPTION SELECTED> Alphabetically
<OPTION> Numerically
<OPTION> High Scores
<OPTION> Low Scores
</SELECT>
<P>
<INPUT TYPE="submit" VALUE = "Retrieve Scores">
</BODY>
</HTML>
Run the Program:

      form1.php3


Processing the Form: Accessing MySQL

The following program is a little longer than the others, and will be analyzed section by section in order to relate the PHP functions to their MySQL counterparts. The program is not run directly, but is the result after processing the form in the section above.

Example 3: process.php3     Processing the Form Using MySQL and PHP
Part 1: HTML Header and Passed Values
In the first part of the program below, the code is just simple HTML data with just a simple print statement in PHP. This program has been sent the variable $ViewMethod from the form that called it, form1.php3, shown above. In PHP, simple variables are preceeded by the $ and will be evaluated within the print statement. Therefore, whatever value was selected in the form will br printed here.
<HTML>
<HEAD>
</HEAD>
<BODY BGCOLOR = "#FFFFFF">
<CENTER>
<H1>Contest Results </H1>
</CENTER>
Here is how results are ordered:
<?
  print("<B> $ViewMethod </B>.");
Part 2: Connecting to the Database
This brief section really has two actions. The first uses a PHP function called mysql_connect() that requires the hostname, account, and password required to access the MySQL account. Since the web server is also the system that has the MySQL database, the host is called "localhost" rather than "threat.tjhsst.edu". The user account "games" has the password "One2Three". The first function returns an integer which then is used to select the database to be used. In our case, the database and the user have exactly the same names.

  $link = mysql_connect("localhost","games","One2Three");
  mysql_select_db("games", $link);
  
Part 3: Building and Executing a Query
In this section, the variable $query begins with the string "SELECT * FROM scores" but is then modified by various append operations using the operator ".=", a dot preceeding the equal sign. The four possible $ViewMethod values were mutually exclusive, so only one of the options was appended to the string. It the value of the passed variable was the default, "Alphabetically", the query value would be:
    $query   =   SELECT * FROM scores ORDER BY Name
There is one small detail in that the string should not have a terminating semicolon, a requirement in the interactive mode for MySQL.
The action of the function mysql_query() actually executes that query to the database. The variable $result is not actually the data, but an integer value that will be used to retrieve the information returned by the query. The request mysql_num_rows() uses that integer to find out how many rows of data were returned. This information will be useful in the next section where the rows are returned, one by one, and printed to a table.

  $query = "SELECT * FROM scores ";
  if($ViewMethod == "High Scores")
     $query .= "WHERE Num > 500 ORDER BY Num DESC";
  if($ViewMethod == "Low Scores")
     $query .= "WHERE Num <= 500 ORDER BY Num";
  if($ViewMethod == "Alphabetically")
     $query .= "ORDER BY Name";
  if($ViewMethod == "Numerically")
     $query .= "ORDER BY Num";
  $result = mysql_query($query,$link);
  $rows = mysql_num_rows($result);
  print("<BR> Count is $rows");
  
  
Part 4: Generating a Table of Values from the Query
In this final section, the first two print statements generate the HTML code for a simple TABLE with a border and labels at the top row called a table header. After that, a simple for loop is used to generate the rest of the table where the maximum value will be the number of rows, or $rows, determined previously.
The function mysql_fetch_array() returns one row of data at a time and assigns it to the variable $rowdata. It is not necessary to pass the function which row is being returned because the function automatically starts at position 0 in the table of returned data and automatically increments until there are no more rows left. As each row is returned, it is assigned to a data structure called an "associative array", which will be familiar to those who program in Perl. These arrays are not accessed by numerical indices, but by the the field names, or keys, used to define the database structure.
After all names and scores are returned and printed with the necessary HTML table tags, the end of the table is printed along with closing tags to terminate the web page. People who try to observe the source code of this pae will never see the clever programming structures in PHP that were used to create the web page. It will just look like straight HTML code.
  print("<TABLE BORDER = 1 CELLPADDING = 5>");
  print("<TR><TH>Name</TH><TH>Score</TH></TR> ");
  for($i = 0; $i < $rows; $i++)
   {
    $rowdata = mysql_fetch_array($result);
    $name=$rowdata["Name"];
    $val = $rowdata["Num"];
    print("<TR><TD> $name </TD><TD> $val</TD></TR>");
   }

  print("</TABLE>");

?>
</BODY>
</HTML>
To watch how this program works, try executing the form program form1.php3, again. Experiment with the different query options and observe the results.

Some More Examples Using PHP

The following web pages will provide some additional examples and programs using PHP.