PHP MySQLi Examples – The Ultimate Tutorial

Welcome to the ultimate tutorial of PHP MySQLi Examples. This article will show example and tutorials for all the major functions of MySQLi in PHP. Hopefully this guide will be your new bible for quick function lookup, and help you on your day to day coding adventures! The contents of the PHP MySQLi examples has 8 different sections to read, develop with code and references. Lets get started…

Contents

  1. Setting Up Our Enviroment
  2. PHP MySQLi Connect
  3. PHP MySQLi Prepare
  4. PHP MySQLi Select DB
  5. PHP MySQLi Fetch Array
  6. PHP MySQLi Insert
  7. PHP MySQLi Replace
  8. PHP MySQLi Prepared Statements

Setting Up Our Environment

Base Requirements

  • >= PHP Version 5.0.7 Installed
  • MySQL Installed

Before we go ahead with all of these PHP MySQLi examples, please take note of the next few paragraphs. It will help you indefinitely when using this database querying class.

If your anything like me, I like to be able to see exceptions in detail if they happen. By default, the MySQLi functions don’t throw exceptions, and you end up with some nasty formatted errors unless you turn error reporting off in the php.ini file.

So, lets go ahead and add the following to the PHP script.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // throw exceptions.

This single function call will enable us to use the Try & Catch blocks and make debugging much, much better. Albeit my personal preference, it may not be yours, so don’t worry if you don’t want to code with Try Catch blocks, it’s not a show stopper.

PHP MySQLi Connect

The more meaningful way to connect to a MySQL database using PHP is to use the mysqli_connect function, it accepts 6 parameters overall, but most commonly, only four is needed. The four parameters we will use in this example are hostname, username, password and database name. The other two are port and socket, but we won’t be needing them.

MySQLi Connect Example Code
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

    try {
        // Try Connect to the DB with mysqli_connect function - Params {hostname, userid, password, dbname}
        $link = mysqli_connect("localhost", "root", "", "mysqli_examples");
    } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
        echo "MySQLi Error Code: " . $e->getCode() . "<br />";
        echo "Exception Msg: " . $e->getMessage();
        exit; // exit and close connection.
    }

    //No Exceptions were thrown, we connected successfully, yay!
    echo "Success, we connected without failure! <br />";
    echo "Connection Info: " . mysqli_get_host_info($link) . PHP_EOL;

    /*
     * Do some cool mysqli stuff...
     */
    
    mysqli_close($link); // finally, close the connection
    ?>

But wait, there is another way to connect! You can actually use the MySQLi object to connect, as it is an alias of the mysqli::__construct() method. This enables Object Oriented Style coding with the functions. So, here is another way to connect to the database.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

    try {
        // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
        $link = new mysqli("localhost", "root", "", "mysqli_examples");
    } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
        echo "MySQLi Error Code: " . $e->getCode() . "<br />";
        echo "Exception Msg: " . $e->getMessage();
        exit; // exit and close connection.
    }

    //No Exceptions were thrown, we connected successfully, yay!
    echo "Success, we connected without failure! <br />";
    echo "Connection Info: " . mysqli_get_host_info($link) . PHP_EOL;

    /*
     * Do some cool mysqli stuff...
     */
    
    mysqli_close($link); // finally, close the connection
    ?>

There is very slight difference here, but it may be preferred among coders. For me personally, the ‘new mysqli()’ way is easier to work with as the object will offer you all of its functions from that single variable. Feel free to go ahead and copy the code from this example to use in your own applications.

References from the PHP Docs –

PHP MySQLi Prepare

What is Prepare all about? It’s simply a safer way of coding MySQL queries. This helps protect against sql injection attacks and therefore, is the best way to write queries with MySQLi.

Prepared statements and parameterized queries may sound complicated but its quite easy to understand. I will show you the main differences between standard select and a prepared select.

Standard Select
$statement = mysqli_prepare($link, "select user_name, first_name, last_name from users_table where id = 1");
Prepared MySQLi Select
$statement = mysqli_prepare($link, "select user_name, first_name, last_name from users_table where id = ?");

The small difference to this initial query is the criteria area, “where id = “. As you can see in the prepared version, it has a question mark for the criteria value. Therefore, we need to write extra code to bind the criteria parameters to the statement. Like so –

mysqli_stmt_bind_param($statement, "?", 1);

Now the value of 1 is bound to the question mark placeholder in the prepared statement and ready to be executed.

The Standard select is already ready to be executed, but is somewhat less safe for security. But having said this, their is no rule to say you have to write prepared statements over non-prepared so it’s completely up to yourself. Lets see some proper examples!

Here is a small list of references on prepared statements from the docs.

PHP MySQLi Select DB

This function is my favorite of all PHP MySQLi examples, it’s the easiest to learn and is so important throughout working with databases in PHP. Here are some PHP MySQLi examples for selecting data from the database with the classes functions. Firstly, we will need to connect to the database and then write our statement.

Procedural Style Select
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $link = mysqli_connect("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $result = mysqli_query($link, "select username, first_name, last_name, gender from users");

        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }
        
        
        /*
         * Do some cool mysqli stuff with our data
         */
        while($row = mysqli_fetch_assoc($result))
            {
                $users[] = $row;
            }
        var_dump($users);
        
        mysqli_close($link); // finally, close the connection

This is some of the data that the script printed –

array (size=5000)
  0 => 
    array (size=4)
      'username' => string 'rogers63' (length=8)
      'first_name' => string 'david' (length=5)
      'last_name' => string 'john' (length=4)
      'gender' => string 'Female' (length=6)
  1 => 
    array (size=4)
      'username' => string 'mike28' (length=6)
      'first_name' => string 'rogers' (length=6)
      'last_name' => string 'paul' (length=4)
      'gender' => string 'Male' (length=4)
.........
Object Oriented Style Select

Now lets take a shot at selecting with the object oriented style way of using the MySQLi class. This, is my preferred way.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $query = "select username, first_name, last_name, gender from users";

            if ($result = $mysqli->query($query)) {

                /* fetch associative array */
                while ($row = $result->fetch_assoc()) {
                    var_dump($row);
                }

                /* free the memory associated with this result set */
                $result->free();
            }
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection

This similar script provides exactly the same output from the database –

array (size=5000)
  0 => 
    array (size=4)
      'username' => string 'rogers63' (length=8)
      'first_name' => string 'david' (length=5)
      'last_name' => string 'john' (length=4)
      'gender' => string 'Female' (length=6)
  1 => 
    array (size=4)
      'username' => string 'mike28' (length=6)
      'first_name' => string 'rogers' (length=6)
      'last_name' => string 'paul' (length=4)
      'gender' => string 'Male' (length=4)
.........

PHP MySQLi Fetch Array

In this section of PHP MySQLi examples, we look at fetch array function. It allows you to specify how you want the results returned on the function call. You can opt for a numeric index array, an associative array or both. Using the following parameters you can control what you want –

  • MYSQLI_ASSOC
  • MYSQLI_NUM
  • MYSQLI_BOTH

For the following examples, I will be specifying both because we can be happy either way! Depending on the style of coding, Fetch Array will require a result parameter and result type, or just result type. The Procedural style requires both parameters and the object oriented style will mean we will of already bound the result. Therefore it will only require the single result type parameter.

With this method of reading data we can pretty much use all of the code previously written in the Select Example.

Procedural Style
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $link = mysqli_connect("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $result = mysqli_query($link, "select username, first_name, last_name, gender from users");
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        /*
         * use mysqli_fetch_array and loop through data
         */
        while ($row = mysqli_fetch_array($result, MYSQLI_BOTH))
        {
            var_dump($row);
        }
        
        mysqli_close($link); // finally, close the connection

Now, I don’t know about you, but I feel like the procedural style is now messy. Feel free to use it though, but take a look at the object oriented style first. The procedural style fetch array returned the following data

array (size=8)
  0 => string 'rogers63' (length=8)
  'username' => string 'rogers63' (length=8)
  1 => string 'david' (length=5)
  'first_name' => string 'david' (length=5)
  2 => string 'john' (length=4)
  'last_name' => string 'john' (length=4)
  3 => string 'Female' (length=6)
  'gender' => string 'Female' (length=6)
Object Oriented Style
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $query = "select username, first_name, last_name, gender from users";

            if ($result = $mysqli->query($query)) {

                //Use mysqli fetch_array and use the MYSQLI_BOTH Parameter for both indexed and associative data
                while ($row = $result->fetch_array(MYSQLI_BOTH)) {
                    var_dump($row);
                }

                /* free the memory associated with this result set */
                $result->free();
            }
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection

Using the MYSQLI_BOTH parameter gives us the following result with, as expected both index and associative results. Thus giving us more flexibility but using more memory, which of course is a pitfall.

array (size=8)
  0 => string 'rogers63' (length=8)
  'username' => string 'rogers63' (length=8)
  1 => string 'david' (length=5)
  'first_name' => string 'david' (length=5)
  2 => string 'john' (length=4)
  'last_name' => string 'john' (length=4)
  3 => string 'Female' (length=6)
  'gender' => string 'Female' (length=6)

Lets see the output with the MYSQLI_ASSOC parameter –

array (size=4)
  'username' => string 'rogers63' (length=8)
  'first_name' => string 'david' (length=5)
  'last_name' => string 'john' (length=4)
  'gender' => string 'Female' (length=6)

Finally, with the MYSQLI_NUM parameter –

array (size=4)
  0 => string 'rogers63' (length=8)
  1 => string 'david' (length=5)
  2 => string 'john' (length=4)
  3 => string 'Female' (length=6)

Here is a link to the docs on fetch_array.

PHP MySQLi Insert

Not much changes with an insert query, it doesn’t require a while loop and it takes less coding to execute what we want to do. The only change is the SQL query that we write and we still use the query function.

Procedural Style
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $link = mysqli_connect("localhost", "root", "", "mysqli_examples");
            // Insert a new record into the users table.
            $query = mysqli_query($link, "insert into users  (username, first_name, last_name, gender) values ('codeAddictz', 'Dave', 'Pepper', 'Male')");
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }
        
        
        mysqli_close($link); // finally, close the connection
Object Oriented Style
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");
            // Insert new record into the db.

            $mysqli->query("insert into users  (username, first_name, last_name, gender) values ('codeAddictz', 'Dave', 'Pepper', 'Male')");

        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection

So that was pretty straight forward, lets move on and make things a little more complicated; Prepared Statements.

PHP MySQLi Prepared Statements

It’s perfectly fine to use all of the PHP MySQLi examples in this tutorial if you are not allowing users to submit data to fill the values in the SQL queries. But if you are, you should start to use prepared statements. Using prepared or parameterized statements protect against SQL Injections, and prevent naughty individuals getting access to all your data.

I touched on this method of executing statements on the database at the beginning of this article, so that you are instantly aware of the reasoning behind it. The following are some ready to use examples for your own projects. The examples are in object oriented style coding for more readability and understanding.

Now we are using the prepare function, which inherits some more functions which we need to be aware of. It inherits the bind_param function that requires n parameters depending on what you are inserting. The first parameter of bind_param will tell MySQLi what data types you intend to use.

So for instance if we know we are going to be inserting all strings, s will be used as it is in the example. But if we were inserting a string then an integer, the parameter would look like this “si”. Check out the docs for bind_param on the php website. There is a nice reference table for characters used to specify data types.

MySQLi Parameterized Insert Example

Object Oriented Style
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.
        
        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");
           
            // Insert with a prepared statement
            $statement = $mysqli->prepare("insert into users  (username, first_name, last_name, gender) values (?, ?, ?, ?)");
            $statement->bind_param("ssss", $uName, $fName, $lName, $gender); // s = string
            // Fill our parameters
            $uName = "CodeAddictz"; // Alternatively could be $_POST["username"]
            $fName = "Dave"; // Alternatively could be $_POST["firstFame"]
            $lName = "Pepper"; // Alternatively could be $_POST["lastName"]
            $gender = "Male"; // Alternatively could be $_POST["gender"]
            
            $statement->execute(); // Execute the statement.

        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection

 

MySQLi Parameterized Select Examples

Object Oriented Style
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.
        
        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli("localhost", "root", "", "mysqli_examples");
           
            // Select some data with username as criteria
            $statement = $mysqli->prepare("select username, first_name, last_name, gender from users where username = ?");
            $statement->bind_param("s", $uName); // s = string
            // Fill our parameters
            $uName = "CodeAddictz"; // Alternatively could be $_POST["username"]
           
            $statement->execute(); // Execute the statement.
            $result = $statement->get_result(); // Binds the last executed statement as a result.
         
            // Fetch the Associative Array
            while ($row = $result->fetch_assoc())
            {   
                var_dump($row);
            }

        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection

The previous script executed and returned the following data from the database

array (size=4)
  'username' => string 'codeAddictz' (length=11)
  'first_name' => string 'Dave' (length=4)
  'last_name' => string 'Pepper' (length=6)
  'gender' => string 'Male' (length=4)

Be sure to check out the get_result documentation via the following link.

Summary

In this tutorial of PHP MySqli exampes, you can understand how to select and insert and use the examples that are given. I also touched on parameterized / prepared statements which are a must have if you are using html forms to submit data to your PHP scripts. Also, the linked references to the documentation about these functions are well worth a read.

Interesting Links

I will continue to add to this ultimate guide over time, but if you have any specific requests for examples on certain functions, please leave a comment and I will look to create them. Additionally, if you have any great PHP MySQLi examples of your own, I would love to have a look at them, leave a comment and we can have a chat!

PHP MySQLi Examples – The Ultimate Tutorial
Posted in PHP

You May Also Like

Leave a Reply

Your email address will not be published.