Click here to Skip to main content
11,634,815 members (68,464 online)
Click here to Skip to main content

Tagged as

Using prepared statements in PHP with MySqli

, 18 Feb 2014 CPOL 4.5K 1
Rate this:
Please Sign up or sign in to vote.
So, as most of you know PHP 5.5.x has deprecated the original MySQL extension, and instead we should be using  MySQLi or PDO_MySQL extensions. Here I will show you how to use MySQLi to query the database. I will cover the basic SELECT statement with and without prepared statements, and will show you

So, as most of you know PHP 5.5.x has deprecated the original MySQL extension, and instead we should be using  MySQLi or PDO_MySQL extensions. Here I will show you how to use MySQLi to query the database. I will cover the basic SELECT statement with and without prepared statements, and will show you the benefits of using mysqlnd driver.

For example purposes say we have a repository class where we keep our cars:

class CarRepository {
	private static $_host;
	private static $_database;
	private static $_user;
	private static $_password;
	private static $_mysqli;

	private static $_initialized = false;

    // this is a fix because static classes in php do not call contructor automatically
    //http://stackoverflow.com/questions/468642/is-it-possible-to-create-static-classes-in-php-like-in-c
    private static function initialize() {
        if (self::$_initialized)
            return;

        $credientials = CredentialsManager::GetCredentials();

        self::$_host = $credientials['host'];
        self::$_database = $credientials['database'];
        self::$_user = $credientials['user'];
        self::$_password = $credientials['pass'];        

        self::$_initialized = true;
    }

    private static function openConnection() {
        self::$_mysqli = new mysqli(self::$_host, self::$_user, self::$_password, self::$_database);
        if (self::$_mysqli->connect_errno) {
            echo "Failed to connect to MySQL: " . self::$_mysqli->connect_error;
            return false;
        }       

        return true;
    }

As you can see in the code the initialize() function is used because static classes in PHP don’t call contructor automatically. The CredentialsManager class may look like this:

class CredentialsManager {
    private static $_Host;
    private static $_Database;
    private static $_User;
    private static $_Pass;

    public static function GetCredentials() {
        CredentialsManager::GetLocalHostCredentials();

        return array(
            'host' => CredentialsManager::$_Host,
            'database' => CredentialsManager::$_Database,
            'user' => CredentialsManager::$_User,
            'pass' => CredentialsManager::$_Pass
        );    
    }

    private static function GetLocalHostCredentials() {
        CredentialsManager::$_Host = "localhost";
        CredentialsManager::$_Database = "database";
        CredentialsManager::$_User = "username";
        CredentialsManager::$_Pass = "password";
    }
}

The openConnection() function in the CarRepository class opens the connection to the database and stores it in the private $_mysqli variable. Note that this is the object oriented style of opening the connection, and that there is also a procedural style.

Finally, lets query the database for some data:

public static function GetCarOfTheDay() {
	self::initialize();

    if (self::openConnection()) {
    	$carObj = self::$_mysqli->query("SELECT * FROM cars WHERE car_of_the_day=true ORDER BY position LIMIT 1");

    	$car = self::makeObjects($carObj);

		return $car;
    }
    else
    	return false;
}

So, simply we just call the query method on the $_mysqli object with the SQL query and we get mysqli_result object:

object(mysqli_result)#3 (5) {
    ["current_field"]=> int(0) 
    ["field_count"]=> int(21) 
    ["lengths"]=> NULL 
    ["num_rows"]=> int(1) 
    ["type"]=> int(0)
}

I send this object into another function which then returns a Car object for me:

$car = self::makeObjects($carObj);

Lets keep it simple and say the makeObjects looks like this:

public static function makeObjects($carObj){
	require_once("Car.php");
	$cars = array();

	while ($row = $carObj->fetch_assoc()) {
		$car = new Car();

		$row = $carObj->fetch_assoc();

		$car->Id = $row["id"];
		$car->Make = $row["make"];
		$car->Year = $row["year"];

	}

 	if (count($cars) > 1)
 		return $cars;
 	else
 		return $cars[0];
}

Function returns only one Car object if there is only one or an array of Car objects if there are more results. Fetching more cars would look like this (basically the same call!):

public static function GetAllCars() {
	self::initialize();

    if (self::openConnection()) {
    	$carObjs = self::$_mysqli->query("SELECT * FROM cars");

    	$cars = self::makeObjects($carObjs);

		return $cars;
    }
    else
    	return false;
}

You can use queries like this when there are no parameters going into your SQL queries like the ones shown above, but what if you would like to query the database for a car by its id? Naturally, you would make a function like getCarById($id) with the $id as a parameter. Now, you may try to this along these lines:

public static function GetCarById($id) {
	self::initialize();

	if (self::openConnection()) {
		$carsObj = self::$_mysqli->query("SELECT * FROM cars WHERE id=" . $id);		
	}
	else 
		return false;
}

and though this would work, you would actually be exposing your self to a potential SQL injection attack. And sure, one could argue that you could escape the $id variable and whatnot, but that’s just not the way how it’s done these days. You can learn more about it on this post on StackOverflow, but basically what they say is that you should be using prepared statements.

The following code  shows how to query the database using prepared statements in mysqli (the function echoes the car’s make and year from the cars table):

public static function GetCarMakesById($id) {
	self::initialize();

	if (self::openConnection()) {
		$stmt = self::$_mysqli->prepare("SELECT make, year FROM cars WHERE id=?");

		$stmt->bind_param("i", $id);

		$stmt->execute();

		$stmt->bind_result($make, $year);

        $stmt->fetch();   

        printf("%s - %s\n", $make, $year);
	}
	else 
		return "oh noez";
}

So, here we put the query in the prepare() method of the mysqli object, and you may notice the instead of the actual parameter. That’s just the way how these prepared statements are written in mysqli. Then you tell it which parameter actually comes in place of and itself then makes sure it’s properly escaped, etc. The i in bind_param() method is for “integer”, s is for “string”, d is for “double”, b is for blob (read more about it here).

After that you have to call execute() and then bind_result() which binds/stores the result from the database query to variables set in it (in our case $make and $year) once you call the fetch() method.

Another example of fetching the data where we have more rows:

public static function GetCarMakesPerYear($year) {
	self::initialize();

	if (self::openConnection()) {
		$stmt = self::$_mysqli->prepare("SELECT make FROM cars WHERE year=?");

		$stmt->bind_param("i", $year);

		$stmt->execute();

		$stmt->bind_result($make);

        while ($stmt->fetch()) {
            printf("%s\n", $make);
        }
	}
	else 
		return false;
}

The code above would print out all the makes of a certain year. Now, up to this point all was great and I was pretty happy how things work, but then, I had to make a select like:

SELECT * FROM cars;

and now I was in trouble. After extensive googling I found out that I basically have two options:

  • use the get_result() function on the mysqli object or
  • list all the fields in the bind_result() and use fetch

At this point I immediately thought, now why would anyone not want to use get_result? [This would particularly help me because then I could just query the db with get_result() and just send that object to makeObjects() and thus using the same exact code - no duplication]

Right, so, the problem was that in order to use get_result() function on the mysqli object you would have to have mysqlnd driver installed (You can learn more on these few links). So, since I had PHP 5.3 and I tried updating with yum install php-mysqlnd I got into trouble. I even posted this question on StackOverflow. The solution I used in the end, as I outline it on SO, was to update PHP to 5.4 and force install php54-mysqlnd-5.4.17-1.ius.el6.x86_64 package.

In order to test if you have mysqlnd support you can run this code:

$mysqlnd = function_exists('mysqli_fetch_all');

if ($mysqlnd) {
    echo 'mysqlnd enabled!';
}
else {
	echo "nope";
}

Finally, with this all set I’m now able to use fetch_all() function like this:

public static function GetCarById($id){            
	self::initialize();

if (self::openConnection()) {
    $stmt = self::$_mysqli->prepare("SELECT * FROM car WHERE id=?");
    $stmt->bind_param("i", $id);

    $stmt->execute();

    $carObj = $stmt->get_result();

    $car = self::makeObjects($carObj);

    return $car;
}
else 
    return false;
}

To use the LIKE in prepared statements you have to “prepare” your variable before, like this:

$stmt = self::$_mysqli->prepare("SELECT * FROM car WHERE make LIKE ?");
$make .= '%';
$stmt->bind_param("s", $make);

Wohoo, happy coding!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nikola Breznjak
Web Developer
Croatia Croatia
Nikola Brežnjak is an engineer at heart and a jack of all trades kind of guy.

For those who care about titles, he has a masters degree in computing from FER. For the past 7 years he worked in a betting software industry where he made use of his knowledge in areas ranging from full stack (web & desktop) development to game development through Linux and database administration and use of various languages (C#, PHP, JavaScript to name just a few).

Lately he’s been interested in the MEAN stack, Ionic framework and Unity3D. Also, he likes to help out on StackOverflow where he’s in the top 0.X% currently.

You can find out more about him through his site at nikola-breznjak.com and see what he rambles about on his blog.

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150728.1 | Last Updated 18 Feb 2014
Article Copyright 2014 by Nikola Breznjak
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid