How to run MySQLi Prepared SELECT Statements in PHP

In this article, we shall discuss how to run MySQLi prepared SELECT statements for SELECT queries in PHP. In our previous article, we learned how to connect to a database using MySQLi Extension. If you missed it, I would recommend clicking here and reading it first.

This article assumes you are already conversant with the basics of PHP programming and SQL (Structured Query Language). It also assumes you have already installed MySQL on your local machine and are able to create a database with simple tables. Therefore, let us drive right into it!

Creating our User Table

A SELECT statement is used to fetch records from a database table. Let’s quickly demonstrate this with a practical example. Let’s suppose we have a table in our database called ‘user’ with the following data:

userIdnameemail
1Michael Sinkolongomichaelsinkolongo@example.com
2Chanda Mwapechandamwape@example.com
3John Kabwitajohnkabwita@example.com
4Chilala Paulinachilalapaulina@example.com

To create this user table in your database, you may want to execute the following queries:

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `userId` int(10) NOT NULL,
  `name` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(150) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`userId`, `name`, `email`) VALUES
(1, 'Michael Sinkolongo', 'michaelsinkolongo@example.com'),
(2, 'Chanda Mwape', 'chandamwape@example.com'),
(3, 'John Kabwita', 'johnkabwita@example.com'),
(4, 'Chilala Paulina', 'chilalapaulina@example.com');

Selecting records from user table

So what we want to do now is fetch this data and display it on a web page. To do so, we have to use a select statement and query it against our database:

SELECT * FROM user

Easy, right? Not qute.

Remember, we have already created our database connection in our php code. To execute our query against our connection, we need to do is:

// we include our database connection script
 include('database-connection.php');
//define variable to hold our query
$sql="SELECT * FROM user";
//run our query against our database connection $conn
$query = $conn->query($sql);
//if query is executed successful, i.e. no errors occurred, //fetch our records
if($query){
  while($row = $query->fetch_assoc()){
       echo $row['userId'].' '.$row['name'].' '.$row['email'];

  }
}

And the output after executing this script is:

Specifying which records to fetch using SELECT statement

Now suppose we want to fetch only the record whose name is ‘Michael Sinkolongo’. How do we do that? Easy, we simply specify that in our query like this:

SELECT * FROM user WHERE name ='Michael Sinkolongo'

And the result is the only record with the name Michael Sinkolongo, as shown below:

Selecting record with name ‘Michael Sinkolongo’

As you suspect, we have not yet used MySQLi Prepared SELECT Statements yet. How different is a prepared statement from a normal one?

Using a prepared SQL Statement to fetch

In a prepared SQL statement, instead of directly passing the values we are querying to the statement like we have done above, we use a placeholder and then call the prepare function. We then bind the actual value to the statement before running it against the database.

That sounds like a mouthful, so let’s demonstrate:

  1. Write the query with placeholders instead of actual values
$sql="SELECT * FROM user WHERE name = ?";

As you can see above, we are using a question mark (?) as a placeholder for the value we want to search by.

2. Create the prepared Statement:

$statement = $conn->prepare($sql);

We create the prepared statement by passing the query to the prepare function on the database connection.

3. Bind the Parameters

$name = "Michael Sinkolongo";
$statement->bind_param("s",$name);

The statement has a function called bind_param, which we use to bind the value to the palceholder. It has two or more parameters, the first parameter is a string specifying the type of data we are passing, and the subsequent parameters are the values we are actually passing. Please note: we have to pass variables to the bind_param function as opposed to raw values.

The following table shows the possible types of data we can pass:

CharacterData Type
sString
iInteger
dDouble
bBlob
DataTypes to use when binding

4. Execute the Statement

$statement->execute();

Executing the statement like this runs the query against the database.

5. Fetch the results

$result = $statment->get_result();
while($row = $result->fetch_assoc()){
  echo $row['userId'].' '.$row['name'].' '.$row['email'];
}

And that is pretty much it! Let’s try it now and see what happens.

Result of Executing Mysqli Prepared Statement for SELECT

As you can see, the result is exactly what we expect to see. Awesome! So just like that you have learned how to work with Mysqli Prepared Statements for SELECT queries.

Working with Multiple Search Parameters in our MySQLi prepared SELECT statements

But real word examples are not always this easy. Suppose we want to search by multiple parameters. How can we go about that? For example, suppose we want to see records whose userId is 1, 2, or 3 or whose name is ‘Michael Sinkolongo’. Using the old fashioned and porbably easy way, we can write our query like this:

SELECT * FROM user WHERE userId = 1 OR userId = 2 OR userId = 3 OR name ='Michael Sinkolongo';

Now, with prepared statements, we need to replace the values with placeholders:

SELECT * FROM user WHERE userId = ? OR userId = ? OR userId = ? OR name =?;

Next, we need to define variables to hold our values:

$userId1 = 1;
$userId2 = 2; 
$userId3 = 3;
$name = 'Michael Sinkolongo';

Then bind them to our prepared statement:

$statement->bind_param("iiis",$userId1, $userId2,$userId3,$name);

And now, putting it all together:

// we include our database connection script
 include('database-connection.php');
//define variable to holder our query
$sql="SELECT * FROM user WHERE userId = ? OR userId = ? OR userId = ? OR name =?;";

//create prepared statement
$statement = $conn->prepare($sql);

//bind values to our statement
$userId1 = 1;
$userId2 = 2; 
$userId3 = 3;
$name = 'Michael Sinkolongo';
$statement->bind_param("iiis",$userId1, $userId2,$userId3,$name);

//execute the statement against our database
$statement->execute();

//fetch the results  and display them

$result = $statement->get_result();
while($row = $result->fetch_assoc()){
  echo '<br/>'.$row['userId'].' '.$row['name'].' '.$row['email'];
} 

And after running the application, the result is:

Result of using 3 different parameters to search

Awesome! It works! See, prepared statements are not that difficult after all!

However, you are probably asking why should we use prepared statements? Aren’t simple statements enough?

We shall discuss this in the next article. Stay tuned!

How to connect to MySQL Database using Mysqli Extension in PHP

The first thing you need to know if you are developing a php application with MySQL as your database is how to connect to MySQL database using Mysqli extension. According to the php manual, Mysqli is an extension that allows access to the functionality provided by Mysql version 4.1 and above. The i stands for improved, meaning it is better and much more secure than older ways of doing the same.

MySQLi Parameters

Connecting to MySQL is relatively straightforward. The Mysqli extension takes on about four parameters, namely:

  • The host
    This is the IP address or name of the computer hosting the database.
  • The User
    The user of the database. MySQL has to be accessed through a user. The default user is root, and by default, there is no password. It is always safe to create a password to prevent unauthorised access.
  • The Password
    This is the password of the user accessing the database.
  • The Database
    This is the name of the database that is being connected to.

With that out of the way, let’s get right into it!

Note: To replicate what I am going to do in this article, you can use PHP Version 7.3.11 and MySQL Version 8.0.19. However, most versions of PHP and MySQL will yield similar results.

Without further ado, this is how you connect to MySQL database using MySQLi in PHP:

<?php
        $db_host = "localhost"; //host
        $db_user = 'YOUR_USER_NAME';  //database user e.g root
        $db_password = 'YOUR_USER_PASSWORD'; // database user password 
        $database = 'YOUR_DATABASE_NAME'; //database name
        $conn = new mysqli($db_host, $db_user,$db_password,$database); 
        echo 'Successfully connected to Database!';      

?>

   And when we fire up the application, here is the result:

Successfully connected to Database

Dealing with Errors

However, life is not always that easy. Suppose, just suppose I entered the wrong password. What would happen?

Access Denied Error with Wrong Status Message

Things turn ugly very quickly! We get an awful warning telling us access has been denied for our user. However, notice something strange? The ‘Successfully connected to database’ message still appears, even though clearly this is not true anymore. We need to fix this and quickly!

We need to check the status of the connection before proceeding, and this is how we do it:

        if($conn->connect_error){
            die("Failed to connect to database");
        }  

What this block of code does is check if there is any errors encountered when attempting to connect. If there is any, it will exit with the message ‘Failed to Connect to the database’. Let’s see how this looks, shall we?

Access Denied with Correct Status Message

This is perfect, or almost! We still have that nasty warning. We don’t want our users to see that. Hackers can use such error messages to learn more about the structure of our application and database, exploiting possible vulnerabilities. A quick solution is simply to suppress all errors like this:

  error_reporting(0);

This produces a clean message like this, without the ugly warning:

Clean Display of Error Message

While it is a good idea to suppress all error messages on production, on a development or test environment, this is a bad idea as it hides important errors or warnings that may need fixing. So I use the try-catch block, like this:

    try{
        // code to connect to database
    }
    catch(Exception $e){
        die("Failed to connect to database due to an error.");
    }

Unfortunately, this brings back our warning. Oh darn! But don’t worry, there is a solution. We need to change the reporting mode of the MySQL driver to throw exceptions instead of displaying warnings. And this is how we do that:

    $driver = new mysqli_driver();
    $driver->report_mode = MYSQLI_REPORT_STRICT | MYSQLI_REPORT_ERROR;

So puting it all together now:

    $driver = new mysqli_driver();
    $driver->report_mode = MYSQLI_REPORT_STRICT | MYSQLI_REPORT_ERROR;
    try{

        $db_host = "localhost"; //database host
        $db_user = 'YOUR_USER_NAME';  //database user e.g root
        $db_password = 'YOUR_USER_PASSWORD'; // database user password 
        $database = 'YOUR_DATABASE_NAME'; //database name
        $conn = new mysqli($db_host, $db_user,$db_password,$database); 
        if($conn->connect_error){
            die("Failed to connect to database");
        }
        echo 'Successfully connected to Database!';
    }
    catch(Exception $e){
        die("Failed to connect to database due to an error.");
    }

And there you have it! You have successfully managed to connect to MySQL database using Mysqli extension in PHP! Congratulations! Now you are ready to do great things.

In a future article, I will show you how to run queries off the connection object. Stay tuned!

Hello world!

Learn how to code by practical examples! For each programming language or area, I have chosen the most common questions people ask and given answers that actually work! No need to spend hours pulling out your hair or surfing the web for answers to the most annoying bugs.

Learn from me. I have spent thousands of hours coding and debugging using various programming languages. I have figured out what works well and what does not. Therefore, I will save you a lot of time. Stick with me and you won’t go wrong.