Jump to content




Recent Topics

Recent Status Updates

  • Photo
      16 Sep
    Kadence

    Some spammers sign up to CC and then they leave their account hidden so they think it won't get noticed but with an obvious name like "SaxophoneRetailSingapore" it's hard not to know they are a spam bot. #ModLife

    Show comments (3)
  • Photo
      15 Sep
    Error

    Programming is something that I enjoy and want to make a career out of. But, I usually tend to start things and not finish them. Any advice on how I can finish what I start?

    Show comments (1)
  • Photo
      12 Sep
    FacetiousTurtle33

    Just joined. Really enjoy this sight. Excited to become a great programmer, and helper.

    Show comments (3)
View All Updates

Developed by Kemal Taskin
- - - - -

PHP 5: MySQLi Prepared Statements

mysqli bind_param mysql prepared statement

  • Please log in to reply
20 replies to this topic

#1 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 09 December 2008 - 07:16 AM

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:


  • 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

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=?;

 

Simple Chat system using PHP, MySQL and Ajax


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

 

Tutorial: Storing Images in MySQL with PHP


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);
}

 

Using Cookies in PHP


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.

 

Interest in more PHP database tutorials?

PHP OOP Tutorial from beginner to advance

Connecting to MYSQL Database!

PDO: Database connections and abstraction

 

 

Attached Files


Edited by Roger, 28 February 2013 - 11:41 AM.
tags

  • 0

#2 amrosama

amrosama

    CC Mentor

  • VIP Member
  • PipPipPipPipPipPipPipPip
  • 2,764 posts

Posted 21 January 2009 - 12:40 PM

Awsome tutorial
  • 0

#3 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 21 January 2009 - 02:03 PM

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

#4 amrosama

amrosama

    CC Mentor

  • VIP Member
  • PipPipPipPipPipPipPipPip
  • 2,764 posts

Posted 21 January 2009 - 03:21 PM

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

#5 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 21 January 2009 - 06:27 PM

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

#6 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3,033 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 13 June 2009 - 02:44 AM

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

#7 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 13 June 2009 - 06:47 AM

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


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


Thanks for the rep!
  • 0

#8 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3,033 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 13 June 2009 - 06:57 AM

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. :P
  • 0

#9 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 13 June 2009 - 07:05 AM

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

#10 Guest_Jaan_*

Guest_Jaan_*
  • Guest

Posted 13 June 2009 - 03:06 PM

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

#11 BlaineSch

BlaineSch

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1,559 posts

Posted 13 June 2009 - 04:46 PM

Wow I never saw this - A website I was browsing before had these but it didn't use them to this extent it did pretty much the same thing as a regular mysql_query function.. .this is very neat I might just have to start using it!
  • 0

#12 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3,033 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 23 June 2009 - 03:09 AM

When you use mysqli is it still necessary to use functions like mysql_real_escape_string?
  • 0





Also tagged with one or more of these keywords: mysqli, bind_param, mysql, prepared statement