We've looked at flat files (chapter 2) for data storage, but that's not how it's done! We set up a database by naming tables and fields using phpMyAdmin (chapter 9) and looked at reading, adding, deleting, and modifying the database using SQL (chapter 10). So now we are ready for finally linking MySQL to PHP, to create web pages with dynamic content taken from databases.
BASIC ARCHITECTURE
- Client originates request to the web server for page that includes dynamic content.
- The web server sees that page includes PHP content and invokes the PHP engine to parse.
- PHP engine detects command to connect to database and a command to execute a query (written as a SQL string) and invokes MySQL server.
- MySQL database server returns requested information from the database.
- PHP engine uses database results to create HTML output, which is returned to the client.
Client Origination
The client will originate the database request by starting some sort of search. The search may be a user entry into a text field, but more often it is a selection from a list of items that are known to be contained in the database; the list is presented as an html select element, with options often from the database itself. In listing 11.1 (page 266), the search possibilities are determined by an html list of fields within one database table,
...<select name="searchtype">
<option value="author">Author</option>
<option value="title">Title</option>
<option value="isbn">ISBN</option>
</select>...
The client form specifies a page on the server (action = ...) to process form input, here to respond to the select option.
PHP Server Response
The processing page first picks up the information from the client form, here using a couple of $_POST super global variables (listing 11.2)
$searchtype=$_POST['searchtype'];
$searchterm=$_POST['searchterm'];
The information from the form is going to be used directly to construct a SQL instruction
($query
= "select * from books where ".$searchtype." like '%".$searchterm."%'";)
but first it has to be cleaned up to make sure that it contains no unintended quotes or slashes. If, for example, the user included a double quote in the search type, the SQL instruction would encounter that and end prematurely, without including the LIKE clause. We'll look more carefully at this later, but that's what the trim, get_magic_quotes_gpc(), and addslashes functions take care of (see more
on get_magic_quotes_gpc)
MYSQL Server Connection and Response
The PHP script needs to connect to the database and pass it instructions about what to return. Connection is made by instantiating an instance of the connection object mysqli() and assigning it a variable name to serve as a "handle" for the connection, as
@ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books');
Instructions are then passed to the object as
$result = $db->query($query);
and the resulting data set is returned to the PHP script to be assigned to the variable $result.
PHP Use of Data Set to Create HTML Stream
The object $result now contains data from the database. Properties of this object include the number of records ($result->num_rows), which is used in a for loop to produce a list of book information. PHP echo commands are used to create an HTML stream that includes a paragraph with book title, author, ISBN number, and price taken from the named fields in the record ($row['author'], etc.).
As a final step, once the dataset has been used, a good PHP script will explicitly free up memory used to contain the dataset and close the connection, as
$result->free();
$db->close();
Adding Records
You add records to a table using a similar approach. A form is used to collect the information to be added (or you could read from an array, text file, another database, etc.). A connection is made, identically to what we did above, and a query string is composed, looking like INSERT INTO tablename VALUES (comma-delimited values); The relevant section from listing 11.4 is
@ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books');
if (mysqli_connect_errno())
{
echo 'Error: Could not connect to database. Please try again later.';exit;
}
$query = "insert into books values
('".$isbn."', '".$author."', '".$title."', '".$price."')";
$result = $db->query($query);
($result)
echo $db->affected_rows.' book inserted into database.';
$db->close();
Prepared Queries (Procedures)
Page 278-279 illustrate a method for separating the insert SQL instruction from the data that follows VALUE. This would allow you to send repeated sets of data (e.g., separate rows) to the same SQL instruction, reducing the amound of coding.