This tutorial shows you how to build prepared statements using the
MySQL Improved extension, rather than queries you construct yourself. This helps to protect your application from SQL injection as quoting and escaping are done for you, in case you forget to escape on your variables.
The MySQLi class is a core package of PHP5 - if you're running PHP4 you'll need to upgrade to use this. Methods of this class are available in procedural form (functions) if you want to build functionality into a DAL.
Creating a database connection
Firstly, you need to instantiate the MySQLi object with your database credentials. This is easily done:
| PHP Code |
$conn = new MySQLi('localhost', 'root', 'password', 'mydb');
if (!$conn) {
die("MySQL error: #{$conn->connect_errno}: {$conn->connect_error}");
}
|
This creates the database link and checks for a connection error.
Creating a prepared statement
Prepared statements are like queries, but without some of the values. These are values that are dynamic to each query. For example, if I had a profile page that gets user information based on a user id (in $_GET['id']) I might have a query like this:
| Code |
SELECT username, email FROM users WHERE id = $_GET['id'] LIMIT 1
|
Which I'd query like this (using ext/mysql):
| PHP Code |
$conn = mysql_connect('localhost', 'root', 'pass');
mysql_select_db($conn, 'mydb');
if (!$conn) {
die("MySQL error: #" . mysql_errno($conn) . ": " . mysql_error($conn));
}
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
die('Missing/invalid user id');
}
$query = 'SELECT username, email FROM users '
. 'WHERE id = '
. mysql_real_escape_string($_GET['id'])
. ' LIMIT 1';
$result = mysql_query($query);
if (mysql_num_rows($query) == 0) {
die('No user data found for user ' (int) $_GET['id']);
}
$result = mysql_fetch_assoc($query);
echo $result['username'] . ' : ' . $result['email'];
|
It's untidy and mixes validation in with query code which isn't great. Here's the same query using a prepared statement:
| PHP Code |
$query = 'SELECT username, email FROM users WHERE id = ? LIMIT 1';
// the query is validated here and will error if it is invalid syntax
$stmt = $conn->prepare($query);
// bind the user id as an integer to the first ?
$stmt->bind_param('i', $_GET['id']);
$stmt->execute(); // execute the statement
$stmt->store_result(); // this call is required for the next operation
if ($stmt->num_rows == 0) {
die('No user data found for user ' (int) $_GET['id']);
}
$stmt->bind_result($username, $email);
$stmt->fetch();
$stmt->close();
echo "$username: $email";
|
This is a bit cleaner (due to the OOP interface I feel) but also allows you more power with less code with the bind_result method. The method's fairly simple: the first parameter is the format - one character for each variable to be replaced, with its data type (s is string, i is int, d is decimal and b for blob (binary data)); the rest of the parameters are the corresponding variables.
| PHP Code |
$query = 'SELECT name, age FROM users '
. 'WHERE id = ? OR name LIKE ? OR rating >= ? LIMIT 1';
$stmt = $conn->prepare($query);
$stmt->bind_param('isd', 1, 'kalle', 2.40);
|
Another thing is that variables will be converted to the type you specify; so if you try to convert true to an int
it will come out as 1.
When binding the result to variables you can make things much easier with loops too:
| PHP Code |
$stmt->bind_result($name, $age, $rating);
while ($stmt->fetch()) {
echo "<tr>"
. "<td>$name</td>"
. "<td>$age</td>"
. "<td>$rating</td>"
. "</tr>";
}
|
When iterating over fetch() each variable is rebound for the current row - similar to mysql_fetch_row but again cleaner.
Conclusion
MySQLi has many nifty features (another to explore is
multi_query - allowing you to execute multiple statements (terminated by semicolons) as you would in MySQL) and should be a part of your arsenal if you use PHP with MySQL.
England
F i m *do *M u n d o
Hayward, California
Linear Mode
