PHP 5: MySQLi Prepared Statements

[Jordan]

Introduction
This tutorial will guide you through creating MySQLi prepared statements. MySQLi is an extension/API for PHP that is also know as MySQL Improved. MySQLi is included with versions 5 of PHP and later that allows PHP developers to take advantage of all the features in MySQL 4.1.3. According to the manual:

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

Any more introduction to MySQLi is beyond the scope of this tutorial. You can read more at PHP: Introduction - Manual

Prepared Statements
Perpared statements are queries written before any actual data is passed to the query. You setup a query once and have the ability to execute it multiple times, binding different sets of data while using only one query.

MySQL Official definition:

Prepared statements are the ability to set up a statement once, and then execute it many times with different parameters

You may be wondering why you would use prepared statements as opposed to passing the SQL statement directoy to MySQL. There are three main benefits to using prepared statements:

1. Significant performance benefit if you are running the same query multiple times. Creating a normal query (non-prepared) has the additional overhead of parsing the statement for syntax errors and setup for the query to be ran. When using prepared statements in MySQL this overhead is only preformed once (the first time) thus increasing each subsequent use.
2. Passing variables as parameters is more secure than passing unvalidated data into a SQL query. Prepared statements make it harder to perform SQL Injection by seperating SQL logic from from the data.
3. Binding variables is cleaner and more convenient for the developer.

Types
There are two types of prepared statements: bound parameter and bound result. As you can guess, bound parameter prepared statements take an input (insert, update) SQL statement and allows the developer to create a template for SQL execution. Bound result prepared statements allow the developer to extract data from a bound SQL query.

SQL Code
To create a template in a prepared statement replace all values with question marks (?). Lets examine a non-prepared insert query:

Bound Parameters

INSERT INTO CodeCall (FirstName, LastName) VALUES ('Jordan','DeLozier');

Changing this to a bound parameter prepared statement means replacing the values with ?:

INSERT INTO CodeCall (FirstName, LastName) VALUES (?, ?);

SELECT FirstName,LastName FROM CodeCall WHERE FirstName='Jordan';

Will be converted into:

SELECT FirstName,LastName FROM CodeCall WHERE FirstName=?;

Bound Results
There is no SQL conversion for bound results. Rather, bound results assign the results to variables similar to list() language construct or extract().

PHP Code
Natuarlly, you'll need an active MySQLi connection. You can find my database and table structure in the attached SQL file. I'll be using user root with no password, you may need to change.

<?php
$mysqli = new mysqli("localhost", "root", "", "cctutorial_mysqli");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

No link needs to be passed to mysqli_connect_error because at the time of connection, if there is an error, link is null. mysqli_connect_error simply grabs the last connection error and returns blank if none. Output is similar to this:

Warning: mysqli::mysqli() [mysqli.mysqli]: (42000/1044): Access denied for user ''@'localhost' to database 'cctutorial_mysqli' in C:\wamp\www\PHP_Test\mysqli_prepared.php on line 3
Connect failed: Access denied for user ''@'localhost' to database 'cctutorial_mysqli'

*Note: You can also bind parameters to SELECT statements.

Bound Parameters
Now that we have that out of the way we will want to create our prepared statement:

/* Create the prepared statement */
if ($stmt = $mysqli->prepare("INSERT INTO CodeCall (FirstName, LastName) values (?, ?)")) {
    
    /* Bind our params */
    $stmt->bind_param('ss', $firstName, $lastName);
    
    /* Set our params */
    $firstName = "Jordan";
    $lastName  = "DeLozier";
    
    /* Execute the prepared Statement */
    $stmt->execute();
    
    /* Echo results */
    echo "Inserted {$lastName},{$firstName} into database\n";
    
    /* Set our params for second query */
    $firstName = "John";
    $lastName  = "Ciacia";
    
    /* Execute second Query */
    $stmt->execute();
    
    echo "Inserted {$lastName},{$firstName} into database\n";
    
    /* Close the statement */
    $stmt->close();    
}
else {
    /* Error */
    printf("Prepared Statement Error: %s\n", $mysqli->error);
} 

The above script creates a prepared statement:

$stmt = $mysqli->prepare("INSERT INTO CodeCall (FirstName, LastName) values (?, ?)") 

If there is any error with your SQL statement an error is thrown and displayed to the user:

printf("Prepared Statement Error: %s\n", $mysqli->error); 

You may want to remove this in production. Next we bind two variables to the statement object, $stmt:

$stmt->bind_param('ss', $firstName, $lastName); 

Notice we pass three items to the function but we only have two places for variables in our prepared statement. This is because the first argument of bind_param is specifying the bind the types for the corresponding bind values. The values can be:

i - Integer
d - Decimal
s - String
b - Blob (sent in packets)

If you have 5 variables and they are all strings you specify five types ("sssss") as the first param. If you have 3 strings, 1 integer and 1 decimal you specify the types as such: "sssid". Of course, they must be in the correct order. If the integer is first and the decimal is third it would look like this: "isdss".

Next we assign the variables values. Notice that the variables had no value even though we bound them.

$firstName = "Jordan";
$lastName  = "DeLozier"; 

The final step is to execute the query:

$stmt->execute(); 

The execute takes the prepared statement, replaces the question marks (?) with our bound parameter values ($firstName and $lastName) and executes the query. In order to show the convenience of executing the same SQL statement multiple times we also insert John Ciacia into the database:

/* Set our params for second query */
$firstName = "John";
$lastName  = "Ciacia";
    
/* Execute second Query */
$stmt->execute(); 

Notice how easy it was to change the variable values and execute the statement again? If you are following along and run the script at this point you will see this output:

Inserted DeLozier,Jordan into database
Inserted Ciacia,John into database

If anything goes wrong, you may see an error such as this:

Prepared Statement Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTs INTO CodeCall (FirstName, LastName) values (?, ?)' at line 1

I simply added an "s" to INSERT to make the statement invalid.

Bound Results
That covers input and most of the information there will apply to output so I will make this section short. Using the same script above we will add another prepared statement and select the data that we just inserted.

/* Create the prepared statement */
if ($stmt = $mysqli->prepare("SELECT FirstName,LastName FROM CodeCall ORDER BY LastName")) {    
    /* Execute the prepared Statement */
    $stmt->execute();
    
    /* Bind results to variables */
    $stmt->bind_result($firstName, $lastName);
    
    /* fetch values */
    while ($stmt->fetch()) {
        printf("%s %s\n", $lastName, $firstName);
    }

    /* Close the statement */
    $stmt->close();
    
}
else {
    /* Error */
    printf("Prepared Statement Error: %s\n", $mysqli->error);
} 

Everything looks familar here. You use bind_results instead of bind_params and the variables are assigned a value for you. Use fetch() to itterate through the results and print a value.

You should see the following output:

Ciacia John
DeLozier Jordan

Conclusion
MySQLi offers many benefits over traditional mysql and allows you to use all of the features of MySQL 4.1.3. You should use this extension if you are developing in PHP version 5 or above and using MySQL 4.1.3+. Using prepared statements will allow you to save resources (CPU, Memory, etc) which could be vital in many circumstances (shared hosting comes to mind) and reduce the thread of SQL Injection.

I've attached an exported SQL file that contains my table structure - you will need to create a database named cctutorial_mysqli or change the PHP code accordingly. I've also attached the MySQLi PHP script that was created during this tutorial. If you have any questions feel free to ask here.

[amrosama]

Awsome tutorial

[Jordan]

Thanks! I wrote it to get a better grasp of MySQLi for the Zend Certified Engineer test.

[amrosama]

Do you have any idea about how to make a stored procedure or a php script using timed sequence, like once a week

[Jordan]

Yeah, you could just execute it as a cron job (or Windows scheduler).
Posted via CodeCall Mobile

[chili5]

Wow, that is awesome. +rep Now, I have a question, can use a prepared statement with insert statements?

[Jordan]

Yup. I posted an example above but here it is again:

$stmt = $mysqli->prepare("INSERT INTO CodeCall (FirstName, LastName) values (?, ?)") 

Thanks for the rep!

[chili5]

Thanks

I scanned through it clicking, so I missed it. So you can use these with any kind of statement? Hm, might as well rewrite my entire program.

[Jordan]

It is a long tutorial. I should have broken it into 2 parts. What program are you rewriting?

[Jaan]

Looking good mate.. Really nice info I think I should start using mysqli