The book provides a sample database, bookorama. See Chapter 8, "Designing your Web database," pages 210-215 for the database design (i.e., the tables to be created for the database).

To create a database, you have some choices. Please read all the choices before you start, because the scripts are available on your desktop in the zip file bookorama.zip -- then you can use the third, really easy choice!

• Create the database and add the table definitions.
  • Using the MySQL Monitor:
    • CREATE DATABASE <database>;
    • CREATE TABLE <table> (<col1> <type1>, ..., <coln> <typen>);
  • Using the MySQL Workbench:
    • As above, in a query window (leftmost icon on toolbar or File>New Query Tab),
      or
    • Create the model as an entity-relationship model (File>New Model, then select "Add Diagram" and add the entities and relationships.
    • Have the workbench create the schema for you (Database>Forward Engineer)
  • Using a script:
    • In the MySQL Monitor: mysql -u <user> -p -t < <file>
      There are script files in the directory Chapter09 (bookorama.sql), for creating tables, and in the directory Chapter10 (book_insert.sql), for inserting data.
    • In the Workbench: Use the second icon on the toolbar or File>Open SQL Script to read a script into an SQL Window, then execute all statements in the window. Remember to refresh the table definitions. You can Reverse Engineer a database model from the resulting database.
• Enter data in the tables. You can use the command line monitor, or the Workbench with an SQL Query Window, or a script file with the command mysql -t < <file>
  • INSERT INTO <table> (<col1>, ..., <coln>) VALUES (<val1>, ..., $lt;valn>);
  • INSERT INTO <table> VALUES (<val1>, ..., $lt;valn>);
  • INSERT INTO <table> SET <col1>=<val1>, ..., <coln>=<valn>

I recommend using the SQL scripts, with the "-t" option (for text file) and the input filename after the redirector "<" on the command line. But... first, look at the script file to see what the commands are. You can do the above in the MySQL monitor, by hand, one by one, of course.

• API: the classes, methods, functions and variables that you use to access a service (in this case, a MySQL database).
• Driver: A driver is a piece of software designed to communicate with a specific type of database server.
• Extension: PHP code consists of a core, with optional extensions to the core functionality. PHP's MySQL-related extensions, such as the mysqli extension, and the mysql extension, are implemented using the PHP extension framework.

• MySQL Extension: the original extension, purely procedural interfact
• mysqli Extension: improved extension, includes OO interface, transaction support, and other things
• PHP Data Objects (PDO): a multi-server application layer for PHP, doesn't support all MySQL bells and whistles.

The mysqli extension is recommended and we will be using it.

We need an SQL user. Let's make the username _mysql and the password mysql, with SELECT, INSERT, and UPDATE privileges to the bookorama database.

For the first program, we will use a sample from the book, the search page and php program.

Useful PHP statements:

• get_magic_quotes_gpc: Returns 0 if magic_quotes_gpc is off, 1 otherwise. Or always returns FALSE as of PHP 5.4.0. It is recommended that you never use this; you should think about what your application needs and code accordingly.
• addslashes: Returns a string with backslashes before characters that need to be quoted. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).
• stripslashes: Returns a string with backslashes stripped off. (\' becomes ' and so on.) Double backslashes (\\) are made into a single backslash (\).
• mysqli class: Represents a connection to a MySQL database.
  • Constructor: Creates a new connection to a database.
    <handle> = new mysqli(<host>, <user>, <password>, <database>);
    opens up a connection for <user> on the MySQL server on <host> for the database <database>.
  • query: Issues a query on the database.
    <handle>->query(<string>)
    passes <string> to the connected database to be processed.
  • close: Closes a previously opened database connection.
    <handle>->close();
• [mysqli_result class](http://us3.php.net/manual/en/class.mysqli-result.php): Represents a query result.
  <result> = <handle>->query(<string>);
  returns <result> as an instance of mysqli_result.
  • num_rows: Number of rows returned by the query (an instance variable).
    $num_rows = <result>->num_rows;
  • fetch_assoc: The next row (returned as an array whose keys are field names and values are field values).
    $row = <result>->fetch_assoc();
  • free: Frees the memory associated with a result.
    <result>->free();

Use the search page (search.html) to run the program results.php:

<html>
<head>
  <title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>
<?php
  // create short variable names
  $searchtype=$_POST['searchtype'];
  $searchterm=trim($_POST['searchterm']);

  if (!$searchtype || !$searchterm) {
     echo 'You have not entered search details.  Please go back and try again.';
     exit;
  }

  if (!get_magic_quotes_gpc()){
    $searchtype = addslashes($searchtype);
    $searchterm = addslashes($searchterm);
  }

  @ $db = new mysqli('localhost', '_mysql', 'mysql', 'mysql');

  if (mysqli_connect_errno()) {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }

  $query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = $db->query($query);

  $num_results = $result->num_rows;

  echo "<p>Number of books found: ".$num_results."</p>";

  for ($i=0; $i <$num_results; $i++) {
     $row = $result->fetch_assoc();
     echo "<p><strong>".($i+1).". Title: ";
     echo htmlspecialchars(stripslashes($row['title']));
     echo "</strong><br />Author: ";
     echo stripslashes($row['author']);
     echo "<br />ISBN: ";
     echo stripslashes($row['isbn']);
     echo "<br />Price: ";
     echo stripslashes($row['price']);
     echo "</p>";
  }

  $result->free();
  $db->close();

?>
</body>
</html>

You will verify that you can create an SQL database for your project along with a user that will access it from a PHP program, and write your first PHP program for accessing the database.

• Create a database on your computer (or the computer at your seat) and define least one table from your project database.
• Define a mysql user with select, insert, and update permissions on the database you just created.
• Enter at least three rows of data in your table(s).
• Create a php program that lists the rows in the table(s).

1. What MySQL command line option do you use to execute a script?
2. How do you specify a user on the MySQL command line? A host?
3. What command do you use to add a row to a table? To remove a row from a table? To change a row in a table?
4. Define:
   1. API
   2. Driver (for a database)
   3. PHP extension
5. Write a PHP program that expects username and password fields in the $_POST array, and uses a table called Users with fields Username and Password to look for a matching username and password. It links to a page "Welcome.html" if it finds a match and to a page "InvalidUser.html" if it doesn't. Be sure you protect your program from input sequences that may damage the database or provide unauthorized access.
6. Be sure you can do the exercise above.