Project 7-2 Create a Web Interface for a Database

Since you have used PHP successfully in Project 7.1 to create a dynamic web page, now you can take it one step further and use it to access and display information by creating a simple web interface for your duckwear database. This project will show you how to combine PHP and HTML to display your data in a web-accessible manner.

Step by Step

In a text editor, open a new file and type in the following script:

 <html> <body> <H3>This is the current DuckWear customer list.</H3> <?php    $link = mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    mysql_select_db('duckwear');    $sql = 'SELECT cust_last, cust_first, cust_city, cust_state       FROM duck_cust';    echo "Last Name First Name City State:<br><br>";    $result = mysql_query($sql, $link);    while ($row = mysql_fetch_array($result))       {       echo "\t".$row[0]."\t";       echo "\t".$row[1]."\t";       echo "\t".$row[2]."\t";       echo "\t".$row[3]."\t";       echo "<br>";       } ?> </table> </body> </html>

Save the file as duckwear.php , remembering to verify that no unwanted file extension has been added to its name when you save it.
Load the file onto your Internet server, using your normal means, and view the duckwear.php page with your web browser.

Project Summary

In Project 7-2, you made an Internet interface between your MySQL database and the Internet. Your page has requested data from the database over the Internet and displayed the up-to-the-moment copy of it on the web page.

Handling Errors

In the previous examples, you checked the result resource and caused the PHP script to exit and issue your own error statement in the event it was false. You can use the actual server error code and message that the MySQL server returns, and log the actual server error code and message to aid in debugging problems. This is accomplished by using the mysql_error() and mysql_errno() functions. Both functions return information for the last attempted MySQL function call.

The mysql_error() function returns the error string directly from the MySQL Server. If there isn't an error, this function returns an empty string. The mysql_errno() function returns the MySQL error code number, instead of the error message string. If an error has not occurred, it will return a numeric value of zero ( ). These functions have the following syntax:

 mysql_error(  link  ) mysql_errno(  link  )

The link parameter is the query resource link identifier.

The following script demonstrates using both of these error-handling commands.

 <?php //Open and check your connection    $link = mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    if (mysql_errno())       {       echo "MySQL Connection Error ".mysql_errno().":            ".mysql_error()."\n";       die();       } //Select a database and check the selection    mysql_select_db('duckwear');    if (mysql_errno())       {       echo "MySQL Databse Selection Error ".mysql_errno().":          ".mysql_error()."\n";       die();       } //Define query string    $sql = 'SELECT cust_last, cust_first, cust_city,  cust_states  FROM duck_cust';   $result = mysql_query($sql, $link); //Check for query error and display error message then die    if (mysql_errno())       {       echo "MySQL Query Error ".mysql_errno().":       ".mysql_error()."\n";       die();       } //Display data by row while there are rows in table    while ($row = mysql_fetch_assoc($result))       {       echo "\t".$row['cust_last'];       echo "\t".$row['cust_first'];       echo "\t".$row['cust_city'];       echo "\t".$row['cust_state'];       echo "\n";       } ?>

Since the cust_states field does not exist in the duckwear table, you can expect to return an error. Running this script would output the following message:

 MySQL Query Error 1054: Unknown column 'cust_states' in 'field list'

Progress Check

1.	What are the opening and closing tags for a PHP script?
2.	What functions can you use to create a link or connection to your MySQL database, and what is the difference between them?
3.	What function verifies whether or not the MySQL database is connected?
4.	What are the two functions for error handling?

Answers

1.	The opening and closing tags for a PHP script are < ?php and ? >.
2.	The mysql_connect() or mysql_pconnect () function is used to create a link, or connection, to MySQL. The former creates a new link with each call, and the latter creates a persistent link that remains open until closed.
3.	The mysql_ping() function verifies whether the MySQL database is connected.
4.	The two functions for error handling are mysql_error() and mysql_errno() .

Good Design Concepts

This section will touch upon some good design concepts, like using escape strings, type casting and general security concerns. In the early days of PHP, it was common to see scripts written where the namespace was shared using a server setting called register_globals , which used to have a default value of on . This meant that a form variable would be accessed in the PHP script via its name. So, if you had the code < input name="field1"/ > in the HTML, you would access it in PHP as < ?php echo $field1 ? >.This was quickly recognized as a security issue, because the variables are essentially posted to the Internet. It is it highly recommended that you keep the register_globals setting off and instead, to use and parse the super- globals . PHP has access to HTTP post and get variable from a web page via these variables called super globals . They include $_POST , $_GET and $_REQUEST (a combination of the previous two).

Separating Logic and HTML Coding

Another good design concept involves the separation of the logic and HTML coding. The script in Project 7-1 mixed the business/database logic with the HTML code. That is generally a bad idea, because it makes maintaining larger projects a nightmare. Therefore, it is recommended that all the database and business logic be separate from the HTML and presentation logic. You can accomplish this simply by writing your code in two separate files: a logic file and a template file.

Here is an example of a logic file named simple2.php:

 <?php    /* All our business logic would reside here    including database calls and queries.*/    $var1 = 'More Information';    // Now you call in the presentation template(s)    include "simple2.tpl.php";?>

The following is an example of a template file named simple2.tpl.php:

 <html>    <body>       <h1>This is mostly an HTML file</h1>       <p>We suggest using HTML only for content; to make       the page look nice, or for layout, use CSS and the       CSS box model.</p>       <h1><?php echo $var1; ?></h1>       <p>It is okay to sprinkle PHP in the template as in       the preceding line, but just for presentation purposes.       </p>    </body> </html>

The simple2.php file calls the simple2.tpl.php file, making it possible to use one template file for a site s look and layout while calling it from multiple pages. This reduces repetitive code, saving space and making it easier to alter the look and layout by changing one template file that automatically changes all the pages that call it.

Tip	You may want to review some of the more robust templating projects available for PHP, including Smarty (http://smarty.php.net), PHPTAL (http://phptal.sf.net), and PHP Savant (http://www.phpsavant.com).

In the interests of simplicity and to encourage a quick learning curve, the projects in this module do not use this advise , but for ease of maintenance in a real-world, production environment, you should try to design your code with this separation in mind.

Maintaining Web Security

Using the raw information for the client is generally a bad idea. Cleaning the variable and using the type-casting functions are recommended. This allows your web interface to run while obfuscating critical information ( names , passwords, and so on) to make it harder for someone to illegally access your sever. You can do this with sprintf -like functions, regular expressions, or by using some of the following built-in MySQL functions:

intval($var)	Get the integer value of $var
floatval($var)	Get the float value of $var
strval(var)	Get the string value of $var
settype($var, type)	Set the $var to one of the following types: Boolean, integer, float, string, array, object, or null

Here is an example of using a type-casting function to ensure you get a string type for this value, no matter what the content may be. This way, even if numbers are supplied as input, they will be saved in a string format because of the forced type-casting indicated by the use of strval($_REQUEST['name']) .

 <?php    $name = strval($_REQUEST['name']); ?> <html> <body> <?php echo "<h1>Hello ".$name."</h1>"; ?> <h2>A Simple Example</h2> <form action="simple.php" method="post"> <label>Name <input name="name"/> </label> </form> </body> </html>

Using type casting makes sure the data type is correct, but you also should make sure the data is not harmful by checking it for special characters that will make the INSERT fail or for embedded SQL commands (MySQL injections) that could unintentionally initiate destructive actions over the web interface. (See the Ask the Expert section for more on MySQL injections.)

Suppose you are inserting a name into a database using a web form like the following.

The insert.html file, the file that calls the PHP file, would look like this:

 <html>    <body>       <form action="insert.php" method="post">          <fieldset>             <legend>Enter Your Name</legend>             <label>First Name                <input type="text" name="cust_first"/></label><br/>             <label>Last Name                <input type="text" name="cust_last"/></label><br/>          </fieldset>       </form>    </body> </html>

The insert.php file, the file that the HTML calls, would look like this:

 <?php // Connection code    $link = mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    if (mysql_errno())       {       echo "MySQL Connection Error ".mysql_errno().":          ".mysql_error()."\n";       die();       }    $cust_first = strval($_REQUEST['cust_first']);    $cust_last = strval($_REQUEST['cust_last']);    $sql = "INSERT INTO duck_cust (cust_first, cust_last)                VALUES ('$cust_first', '$cust_last')";    $result = mysql_query($sql, $link) ;?> <html>    <body>       <h1>Successfully Inserted Data!</h1>    </body> </html>

If the user enters a value with a single apostrophe ('), the insertion would fail. A good way to prevent this is to use the mysql_quote() function, which has the following syntax:

  string  = mysql_escape_string (  variable  )

The variable parameter is a string that defines the variable to escape.

The following example allows users to input special characters by using the mysql_ escape_string() function.

 <?php    // Connection code    $link = mysql_connect('localhost', 'root', '', false,                MYSQL_CLIENT_COMPRESS);    if (mysql_errno())    {      echo "MySQL Connection Error ".mysql_errno().":                ".mysql_error()."\n";       die();    }    $cust_first = strval($_REQUEST['cust_first']);    $cust_last = strval($_REQUEST['cust_last']);    $sql = "INSERT INTO duck_cust (cust_first, cust_last)                VALUES (  mysql_escape_string  ($cust_first),  mysql_escape_string  ($cust_last));    $result = mysql_query($sql, $link); ?> <html>    <body>       <h1>Successfully Inserted Data!</h1>    </body> </html>

Now if a user types in an apostrophe for a name, like D urberville, the escape function will convert it to D\ urberville, preventing an insertion error.

Ask the Expert

What are SQL injections, and how can I prevent them?

SQL injections are when a user inputs an SQL query instead of the expected information. If unprotected against this sort of attack, the database will accept the input string and unintentionally send a command to the database. This can result in faulty data or even the deletion of a table or an entire database.

If you have a web-based deletion function, where your PHP code would delete a customer based on a provided customer number, it would probably look something like the following example:

 <?php // Connection code goes here    $cust_num = strval($_REQUEST['cust_num']);    $sql = "DELETE FROM duck_cust             WHERE cust_num=".$cust_num;    $result = mysql_query($sql); ?>

You are verifying the data by making sure that the $cust_num is a string, but someone can still post a value that contains a harmful SQL query, like this:

 "bogus")'; DROP DATABASE duckwear;

If this string were inserted, it would be read and run like any other command and result in dropping your entire database. This kind of security issue is called SQL-injection.

To prevent this, you should use the mysql_escape_string() function discussed in this module, as shown in the following example:

 <?php // Connection code goes here...    $cust_num = strval($_REQUEST['cust_num']);    $sql = "DELETE FROM duck_cust             WHERE cust_num=".mysql_escape_string($cust_num);    $result = mysql_query($sql); ?>

This prevents the string from being interpreted as a command that would drop the entire duckwear database, and it provides another layer of security for your database/web interface.

Using the MySQL Improved Interface (PHP 5 only)

One of the major advances in the latest version of PHP (PHP 5) is a new object model, which provides a much fuller feature set for object-oriented developers. This version offers MySQL- related functions that can be run in object mode, as well as the ability to bind parameters.

Procedural Mode and Object Mode

The new model includes mysqli functions, such as mysqli_connect() , which can be run in either procedural or object mode. Procedural mode is similar in syntax to the original mysql functions discussed earlier in this module.

Here is an example of the connection syntax using object mode:

 <?php    $mysqli = new mysqli("localhost", "root", "", "duckwear"); // check connection    if (mysqli_connect_errno())       {       echo "Connect failed:". mysqli_connect_error()."\n";       die();       }    $mysqli->close(); ?>

Here is an example of the connection syntax using procedural mode:

 <?php    $link = mysqli_connect("localhost", "root", "", "duckwear"); // check connection    if (!$link)       {       echo "Connect failed:". mysqli_connect_error()."\n";       die();       }    mysqli_close($link); ?>

Notice that the procedural style requires the link to be passed around, while the object-oriented style creates a database object.

Binding Parameters

One of the major features missing from the original mysql library was the ability to bind parameters. You often find yourself looping around data, while issuing an INSERT command each iteration. Without binding, you would need to issue a full INSERT command each iteration, which is processor- and bandwidth- intensive . The binding method handles these difficulties.

Binding data adds information to a stack or an array. You can use the mysqli_stmt_ bind_param() to accomplish this. The command is then issued using wildcards to describe where the data should go. Then the command is executed once with the statement and data array. The database server interprets the SQL statement and quickly substitutes the information from the array.

When binding parameters, you need to indicate the type of parameter. The following are some of the available binding parameters:

I	The value is expected as an integer.
D	The value is expected as a double.
S	The value is expected as a string.
B	The value is expected as a BLOB.

The following is an example of using binding parameters.

 <?php    $mysqli = new mysqli("localhost", "root", "pa55w0rd", "duckwear");    if (mysqli_connect_errno())       {       echo "Connect failed:". mysqli_connect_error()."\n";       die();       }    $sql = 'INSERT into duck_cust (cust_first, cust_last)       VALUES (?, ?)';    $sth =& $mysqli->prepare($sql);    if ($mysqli->errno())    {       echo "Prepare failed: ". $mysqli->errno() .":".                $mysqli->error()."\n";       die();    } // In this example, you are using two strings, hence // the first parameter is "ss".    $sth->mysqli_stmt_bind_param('ss', 'Kodi', 'Evil');    $sth->mysqli_stmt_bind_param('ss', 'Austin', 'Miller');    $sth->mysqli_stmt_bind_param('ss', 'Isabella', 'Smith');    $sth->execute();    if ($mysqli->errno())       {       echo "Execute failed: ". $mysqli->errno() .":".           $mysqli->error()."\n";       die();       }?>

At the time of the writing of this book, PHP 5 was not yet in production, so the command names and behavior could differ . However, it is available for downloading and experimentation, and production release is imminent. For more information, visit the mysqli section of the online PHP documentation.

Progress Check

1.	What are four functions to force type casting of variables?
2.	What function prevents SQL injections?
3.	What is the new function for creating a link or connection with MySQL in PHP 5?
4.	What are four type indicators for binding parameters with the new mysqli_stmt_ bind_param() function and what types do they represent?

Answers

1.	Four functions that force type casting of variables are intval() , floatval() , strval() , and settype() .
2.	The mysql_escape_string prevents SQL injections.
3.	The new function for creating a link or connection with MySQL in PHP 5 is mysqli_connect() .
4.	The four binding parameter types mentioned are i for integer, d for double, s for string, and b for BLOB.