Accessing Your MySQL Database from the Web with PHP
- How Web Database Architectures Work
- Querying a Database from the Web
- Putting New Information in the Database
- Using Prepared Statements
- Using Other PHP-Database Interfaces
- Further Reading
- Next
This chapter is from the book
This chapter is from the book
This chapter is from the book
PREVIOUSLY, IN OUR WORK WITH PHP, WE used a flat file to store and retrieve data. When we looked at this file in Chapter 2, “Storing and Retrieving Data,” we mentioned that relational database systems make a lot of these storage and retrieval tasks easier, safer, and more efficient in a web application. Now, having worked with MySQL to create a database, we can begin connecting this database to a web-based front end.
In this chapter, we explain how to access the Book-O-Rama database from the Web using PHP. You learn how to read from and write to the database and how to filter potentially troublesome input data.
Key topics covered in this chapter include
- How web database architectures work
- Querying a database from the Web using the basic steps
- Setting up a connection
- Getting information about available databases
- Choosing a database to use
- Querying the database
- Retrieving the query results
- Disconnecting from the database
- Putting new information in the database
- Using prepared statements
- Using other PHP-database interfaces
- Using a generic database interface: PEAR MDB2
How Web Database Architectures Work
In Chapter 8, “Designing Your Web Database,” we outlined how web database architectures work. Just to remind you, here are the steps:
- A user’s web browser issues an HTTP request for a particular web page. For example, the user might have requested a search for all the books written by Michael Morgan at Book-O-Rama, using an HTML form. The search results page is called results.php.
- The web server receives the request for results.php, retrieves the file, and passes it to the PHP engine for processing.
- The PHP engine begins parsing the script. Inside the script is a command to connect to the database and execute a query (perform the search for books). PHP opens a connection to the MySQL server and sends on the appropriate query.
- The MySQL server receives the database query, processes it, and sends the results—a list of books—back to the PHP engine.
- The PHP engine finishes running the script. This usually involves formatting the query results nicely in HTML. It then returns the resulting HTML to the web server.
- The web server passes the HTML back to the browser, where the user can see the list of books she requested.
Now you have an existing MySQL database, so you can write the PHP code to perform the preceding steps. Begin with the search form. The code for this plain HTML form is shown in Listing 11.1.
Listing 11.1. search.html—Book-O-Rama’s Database Search Page
<html>
<head>
<title>Book-O-Rama Catalog Search</title>
</head>
<body>
<h1>Book-O-Rama Catalog Search</h1>
<form action="results.php" method="post">
Choose Search Type:<br />
<select name="searchtype">
<option value="author">Author</option>
<option value="title">Title</option>
<option value="isbn">ISBN</option>
</select>
<br />
Enter Search Term:<br />
<input name="searchterm" type=""text" size="40"/>
<br />
<input type="submit" name="submit" value="Search"/>
</form>
</body>
</html>
This HTML form is reasonably straightforward. The output of this HTML is shown in Figure 11.1.
)
Figure 11.1 The search form is quite general, so you can search for a book by its title, author, or ISBN.
The script that will be called when the Search button is clicked is results.php. It is listed in full in Listing 11.2. Through the course of this chapter, we discuss what this script does and how it works.
Listing 11.2. results.php—This Script Retrieves Search Results from the MySQL Database and Formats Them for Display
<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', 'bookorama', 'bookorama123', 'books');
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>
Note that this script allows you to enter the MySQL wildcard characters % and _ (underscore). This capability can be useful for the user, but you can escape these characters if they will cause a problem for your application.
Figure 11.2 illustrates the results of using this script to perform a search.
)
Figure 11.2 The results of searching the database for books about Java are presented in a web page using the results.php script.