CodeProject, PHP

Using prepared statements in PHP with MySqli

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!
Link to this article on CodeProject

Written by Nikola Brežnjak

Using prepared statements in PHP with MySqli

Related Posts

Leave a Comment

Cancel Reply

Using prepared statements in PHP with MySqli

Related Posts

Way of the Warrior Kid – Jocko Willink

I don’t always use logging, but when I do I make sure it’s to /dev/null

Why am I getting Unexpected token ‘\u0000’ when using npm install -g package

Leave a Comment

Cancel Reply