Administering MySQL Databases on the Web Using PHP / Page 2

Administering MySQL Databases on the Web Using PHP [con't]

Using Prepared Statements

The mysqli library supports the use of prepared statements. They are useful for speeding up execution when you are performing large numbers of the same query with different data. They also protect against SQL injection-style attacks.

The basic concept of a prepared statement is that you send a template of the query you want to execute to MySQL and then send the data separately. You can send multiple lots of the same data to the same prepared statement; this capability is particularly useful for bulk inserts.

You could use prepared statements in the insert_book.php script, as follows:

Let's consider this code line by line.

When you set up the query, instead of substituting in the variables as done previously, you put in question marks for each piece of data. You should not put any quotation marks or other delimiters around these question marks.

The second line is a call to $db->prepare(), which is called mysqlistmtprepare() in the procedural version. This line constructs a statement object or resource that you will then use to do the actual processing.

The statement object has a method called bindparam(). (In the procedural version, it is called mysqlistmtbindparam().) The purpose of bind_param() is to tell PHP which variables should be substituted for the question marks. The first parameter is a format string, not unlike the format string used in printf(). The value you are passing here ("sssd") means that the four parameters are a string, a string, a string, and a double, respectively. Other possible characters in the format string are i for integer and b for blob. After this parameter, you should list the same number of variables as you have question marks in your statement. They will be substituted in this order.

The call to $stmt->execute() (mysqlistmtexecute() in the procedural version) actually runs the query. You can then access the number of affected rows and close the statement.

So how is this prepared statement useful? The clever thing is that you can change the values of the four bound variables and re-execute the statement without having to reprepare. This capability is useful for looping through bulk inserts.

As well as binding parameters, you can bind results. For SELECT type queries, you can use $stmt->bindresult() (or mysqlistmtbindresult()) to provide a list of variables that you would like the result columns to be filled into. Each time you call $stmt> fetch() (or mysqlistmtfetch()), column values from the next row in the resultset are filled into these bound variables. For example, in the book search script you looked at earlier, you could use

to bind these four variables to the four columns that will be returned from the query. After calling

you can call

in the loop. Each time this is called, it fetches the next result row into the four bound variables.

You can also use mysqlistmtbindparam() and mysqlistmtbindresult() in the same script.

Using Other PHP-Database Interfaces

PHP supports libraries for connecting to a large number of databases, including Oracle, Microsoft SQL Server, and PostgreSQL.

In general, the principles of connecting to and querying any of these databases are much the same. The individual function names vary, and different databases have slightly different functionality, but if you can connect to MySQL, you should be able to easily adapt your knowledge to any of the others.

If you want to use a database that doesn't have a specific library available in PHP, you can use the generic ODBC functions. ODBC, which stands for Open Database Connectivity, is a standard for connections to databases. It has the most limited functionality of any of the function sets, for fairly obvious reasons. If you have to be compatible with everything, you can't exploit the special features of anything.

In addition to the libraries that come with PHP, available database abstraction classes such as MDB2 allow you to use the same function names for each type of database.

Using a Generic Database Interface: PEAR MDB2

Let's look at a brief example using the PEAR MDB2 abstraction layer. This is one of the most widely used of all the PEAR components. Instructions for installing the MDB2 abstraction layer can be found in the "PEAR Installation" section in Appendix A, "Installing PHP and MySQL. "

For comparative purposes, let's look at how you could write the search results script differently using MDB2.

Listing 11. 5 results_generic.php—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);
}
// set up for using PEAR MDB2
require_once('MDB2.php');
$user = 'bookorama';
$pass = 'bookorama123';
$host = 'localhost';
$db_name = 'books';
// set up universal connection string or DSN
$dsn = "mysqli://". $user. ":". $pass. "@". $host. "/". $db_name;
// connect to database
$db = &MDB2::connect($dsn);
// check if connection worked
if (MDB2::isError($db)) {
echo $db->getMessage();
exit;
}
// perform query
$query = "select * from books where ". $searchtype. " like '%". $searchterm. "%'";
$result = $db->query($query);
// check that result was ok
if (MDB2::isError($result)) {
echo $db->getMessage();
exit;
}
// get number of returned rows
$num_results = $result->numRows();
// display each returned row
for ($i=0; $i <$num_results; $i++) {
$row = $result->fetchRow(MDB2_FETCHMODE_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>";
}
// disconnect from database
$db->disconnect();
?>
</body>
</html>

Let's examine what you do differently in this script. To connect to the database, you use the line

This function accepts a universal connection string that contains all the parameters necessary to connect to the database. You can see this if you look at the format of the connection string:

After this, you check to see whether the connection was unsuccessful using the isError() method and, if so, print the error message and exit:

Assuming everything has gone well, you then set up a query and execute it as follows:

You can check the number of rows returned:

You retrieve each row as follows:

The generic method fetchRow() can fetch a row in many formats; the parameter MDB2FETCHMODEASSOC tells it that you would like the row returned as an associative array.

After outputting the returned rows, you finish by closing the database connection:

As you can see, this generic example is similar to the first script.

The advantages of using MDB2 are that you need to remember only one set of database functions and that the code will require minimal changes if you decide to change the database software.

Because this is a MySQL book, we use the MySQL native libraries for extra speed and flexibility. You might want to use the MDB2 package in your projects because sometimes the use of an abstraction layer can be extremely helpful.

Administering MySQL Databases on the Web Using PHP / Page 2

Administering MySQL Databases on the Web Using PHP [con't]

Using Prepared Statements

Using Other PHP-Database Interfaces

Using a Generic Database Interface: PEAR MDB2

Further Reading

This chapter is an excerpt from the book, PHP and MySQL Web Development by Luke Welling, Laura Thomson, published by Addison-Wesley Professional, October 2008, ISBN 0672329166, Copyright 2008 Addison-Wesley Professional

Find a programming school near you