Administering MySQL Databases on the Web Using PHP [con't]
Using Prepared Statements
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:
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
mysqlistmtexecute() in the procedural version)
actually runs the query. You can then access the number of affected rows and close the
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
mysqlistmtbindresult()) to provide a list of variables
that you would like the result columns to be filled into. Each time you call
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
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
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
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.
For more information on connecting MySQL and PHP together, you can read the appropriate sections of the PHP and MySQL manuals.
For more information on ODBC, visit http://www.webopedia.com/TERM/O/ODBC.html.
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
Original: December 1, 2008