This chapter is from the book
This chapter is from the book
This chapter is from the book
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:
$query = "insert into books values(?, ?, ?, ?)";
$stmt = $db->prepare($query);
$stmt->bind_param("sssd", $isbn, $author, $title, $price);
$stmt->execute();
echo $stmt->affected_rows.' book inserted into database.';
$stmt->close();
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 mysqli_stmt_prepare() 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 bind_param(). (In the procedural version, it is called mysqli_stmt_bind_param().) 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() (mysqli_stmt_execute() 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->bind_result() (or mysqli_stmt_bind_result()) to provide a list of variables that you would like the result columns to be filled into. Each time you call $stmt->fetch() (or mysqli_stmt_fetch()), 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
$stmt->bind_result($isbn, $author, $title, $price);
to bind these four variables to the four columns that will be returned from the query. After calling
$stmt->execute();
you can call
$stmt->fetch();
in the loop. Each time this is called, it fetches the next result row into the four bound variables.
You can also use mysqli_stmt_bind_param() and mysqli_stmt_bind_result() in the same script.