PHP 5: MySQLi Prepared Statements--tutorial.jcwcn.com

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:

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.
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.
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

Code:

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

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

Code:

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

Code:

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

Will be converted into:

Code:

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 Code:

<?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:

Quote:

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:

PHP Code:

/* 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:

PHP Code:

$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:

PHP Code:

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:

PHP Code:

$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.

PHP Code:

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

The final step is to execute the query:

PHP Code:

$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:

PHP Code:

/* 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:

Quote:

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

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

Quote:

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.

PHP Code:

/* 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:

Quote:

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.

Attached Files<>To view attachments in this forum your post count must be 1 or greater. You currently have 0 posts

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

PHP 5: MySQLi Prepared Statements

relation tutorial

Category

New

Hot