Formatting Results

So far in this chapter we have shown the basic techniques for connecting to and querying a MySQL DBMS using PHP. In this section, we extend this to produce results with embedded HTML that have both better structure and presentation.

Let’s consider an example that presents results in an HTML <table> environment. Example 4-6 shows a script to query the winestore database and present the details of wines. Previously, in Example 4-5, the details of wines were displayed by wrapping the output in HTML <pre> tags. The script in Example 4-6 uses the function displayWines( ) to present the results as an HTML <table>. The main body of the script has a similar structure to previous examples, with the exceptions that the query is stored in a variable, and the username, password, and the showerror( ) function are stored in separate files and included in the script with the include directive. We introduced the include directive in Chapter 2 and discuss it in more detail later in this section.

The displayWines( ) function first outputs a <table> tag, followed by a table row <tr> tag with six <th> header tags and descriptions matching the six attributes of the wine table. We could have output these using mysql_fetch_field( ) to return the attribute names rather than hardcoding the heading names. However, in most cases, the headers are hardcoded because attribute names are less meaningful to users than manually constructed textual descriptions.

Example 4-6. Producing simple <table> output with MySQL 

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wines</title>
</head>
<body>
<?php
  include 'error.inc';
  include 'db.inc';

  // Show the wines in an HTML <table>
  function displayWines($result)
  {

     echo "<h1>Our Wines</h1>\n";

     // Start a table, with column headers
     echo "\n<table>\n<tr>\n" .
          "\n\t<th>Wine ID</th>" .
          "\n\t<th>Wine Name</th>" .
          "\n\t<th>Type</th>" .
          "\n\t<th>Year</th>" .
          "\n\t<th>Winery ID</th>" .
          "\n\t<th>Description</th>" .
          "\n</tr>";

     // Until there are no rows in the result set,
     // fetch a row into the $row array and ...
     while ($row = @ mysql_fetch_row($result))
     {
        // ... start a TABLE row ...
        echo "\n<tr>";

        // ... and print out each of the attributes
        // in that row as a separate TD (Table Data).
        foreach($row as $data)
           echo "\n\t<td> $data </td>";

        // Finish the row
        echo "\n</tr>";  
     }

     // Then, finish the table
     echo "\n</table>\n";
  }

  $query = "SELECT * FROM wine";

  // Connect to the MySQL server
  if (!($connection = @ mysql_connect($hostname,
                                      $username,
                                      $password)))
     die("Cannot connect");

  if (!(mysql_select_db("winestore", $connection)))
     showerror(  );

  // Run the query on the connection
  if (!($result = @ mysql_query ($query, $connection)))
     showerror(  );

  // Display the results
  displayWines($result);

  // Close the connection
  if (!(mysql_close($connection)))
     showerror(  );
?>
</body>
</html>

After producing the HTML <table> open tag, the displayWines( ) function retrieves the rows in the result set, showing each row as a separate <table> row using the <tr> tag. Each attribute value for each wine—where the attributes match the headings—is displayed within the row as <table> data using the <td> tag. Carriage returns and tab characters are used to lay out the HTML for readability; this has no effect on the presentation of the rendering of the document by a web browser, but it makes the HTML much more readable if the user views the HTML source.

The results of using a <table> environment instead of <pre> tags are more structured and more visually pleasing. The output in a Netscape browser is shown in Figure 4-2, along with a window showing part of the HTML source generated by the script.

Presenting wines from the winestore in an HTML <table> environment

Figure 4-2. Presenting wines from the winestore in an HTML <table> environment

Using Include Files in Practice

Example 4-7 and Example 4-8 show the two files included with the include directive in Example 4-6. As discussed in Chapter 2, the include directive allows common functions in other files to be accessible from within the body of a script without directly adding the functions to the code.

Example 4-7. The db.inc include file 

<?
   $hostName = "localhost";
   $databaseName = "winestore";
   $username = "fred";
   $password = "shhh";
?>

Example 4-8. The error.inc include file 

<?
   function showerror(  )
   {
      die("Error " . mysql_errno(  ) . " : " . mysql_error(  ));
   }
?>

Both include files are added to all code developed for the winestore and allow easy adjustment of the database server name, database name, and DBMS username and password. The flexibility to adjust these parameters in a central location allows testing of the system on a backup or remote copy of the data, by changing the database name or hostname in one file. This approach also allows the use of different username and password combinations with different privileges, for testing purposes.

We have chosen to name our include files with the .inc extension. This presents a minor security problem. If the user requests the include file, the source of the include file is shown in the browser. This may expose the username and password for the DBMS, the source code, the database structure, and other details that should be secure.

There are three ways to address this problem. First, you can store the include files outside the document tree of the Apache web server installation. For example, store the include files in the directory /usr/local/include/php and use the complete path in the include directive. Second, you can use the extension .php instead of .inc. In this case, the include file is processed by the PHP script engine and produces no output because it contains no main body. Third, you can configure Apache so that files with the extension .inc are forbidden to be retrieved.

All three approaches to securing include files work effectively in practice. Using the extension .php for include files is the simplest solution but has the disadvantage that includes files can’t be easily distinguished from other files. In the online winestore, we have configured Apache to disallow retrieval of files with the extension .inc.

Get Web Database Applications with PHP, and MySQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial