In the last section we looked at connecting to a database using PHP and the mysqli extension. This was written 'procedurally'. This code can also be written using the Object Oriented (OO) approach. The nice thing about the OO approach is that method and property names are more readable than in the procedural syntax particularly if you have experience of working with objects in any other language. The basic concept is that we create a 'mysqli' object from the 'mysqli' class, and are then able to call upon its properties and methods to query our database.

Creating the Connection

First let us create the connection. This uses the 'new' keyword to create a mysqli object using your connection details. Generally it is tidier to store this information in an include file for easy editing and reuse. The following is a good starting example:

<?php
$hostname = "xxxx.yourserver.com";
$username = "xxxx.yourusername";
$password = "xxxx.yourpassword";
$database = "xxxx.yourdatabase";
$mysqli = new mysqli($hostname, $username, $password, $database);
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
//magic quotes logic
if (get_magic_quotes_gpc())
{
function stripslashes_deep($value)
{
	$value = is_array($value) ?
	array_map('stripslashes_deep', $value) :
	stripslashes($value);
	return $value;
}
$_POST = array_map('stripslashes_deep', $_POST);
$_GET = array_map('stripslashes_deep', $_GET);
$_COOKIE = array_map('stripslashes_deep', $_COOKIE);
$_REQUEST = array_map('stripslashes_deep', $_REQUEST);
}
?>

In the above the variable $mysqli is our mysqli object and we'll make reference to this object when we want to query the database.

The other stuff in the file is to handle any errors if we type in the wrong connection details. The mysqli_connect_errno() method will pick up any error and print it back for us.

(The include also does some tidying up of the superglobals by stripping backslashes that might have been applied by magic quotes.)

Tip: Your may consider removing the error handling once the connection is made

Save the above with a .inc.php file extension name. I like to create a folder called 'includes' to store files of this nature. Then don't forget to include this page in the normal way ie:


include_once('includes/conn.inc.php');

A Basic SQL Query using OO Mysqli

The following snippet queries a database table called 'movies' and will extract one record based on the 'filmID' which is a primary key in the database.

$queryFilms = "SELECT filmName, filmDescription FROM movies WHERE filmID = 10";
$resultFilms = $mysqli->query($queryFilms);
$rowFilms = $resultFilms->fetch_assoc();
// then to output
echo "<p>{$rowFilms['filmName']}</p>";

The $mysqli variable is the mysqli object we created in the include file. The -> (arrow) used here is PHP object oriented syntax. It called the query() method of the mysqli class that itself requires a SQL query. We read this in as the variable $queryFilms.

Tip: If you are coming from a Javascript or ActionScript background it might help to think of the -> (arrows) as dot (.) syntax.

The 'query' returns a mysqli results object and this is stored in our variable $resultsFilms. However that is not the end of the story as we need to extract our lovely data from the mysqli results object before we can play with it.

To do so we use the mysqli method fetch_assoc(). This returns an associate array which uses the database's field names as the indexes. Therefore as we have a field name of 'filmName' in the database we can extract that value from the array using the syntax $rowFilms['filmName'].

If you are not familiar with arrays and in particular associate arrays take a look back to the arrays section.

More Than One Record using OO mysqli

The above query is based on a primary key in the WHERE clause so we would only expect one record to be returned. If we expect more than one record to be return then things get handled slightly differently.

$queryFilms = "SELECT * FROM movies ORDER BY movieName";
$resultFilms = $mysqli->query($queryFilms);
while ($rowFilms = $resultFilms->fetch_assoc()) {
echo $rowFilms['movieName'];
}

The above uses a 'lazy' SELECT * - ie give me all the fields from the database. Here again we pass the SQL into the mysqli's query method and again extract the results into an associate array. However, this time the array is placed within a while loop such that the $rowFilms variable actually becomes a different array of values through each loop until all the rows are extracted. In the above the results are just been echoed to the screen - however you might find it useful to stash this data away in an array ie:

$queryFilms = "SELECT * FROM movies ORDER BY movieName";
$resultFilms = $mysqli->query($queryFilms);
//create array to hold the movie names
$movieNameArray = array();
while ($rowFilms = $resultFilms->fetch_assoc()) {
$movieNameArray[] = $rowFilms['filmName'];
}
//Test the content of the array
print_r($movieNameArray);

To extract the values from the array we could then use a foreach ie:

<?php 
foreach($movieNameArray as $value){
echo "<p>{$value}</p>";
}
?>

Prepare Statements

The above examples are all well and good but the real point of mysqli is to create a prepare statement that can be used to safely send a query that includes externally derived values through to your database. Most often these values come via the superglobals $_POST and $GET in the likes of search forms or query strings.

Using prepared statements involves the following steps:

prepare SQL > bind parameters > execute > bind results > fetch values > close

Just like with the mysqli object we create a prepare object which then has lots of very useful properties and methods for us to use. We can refer to this as our SQL statement object - which is why it is common to create a variable name like $stmt (short for statement).

$stmt = $mysqli->prepare("SELECT filmID, filmName FROM movies WHERE filmID = ?");
$stmt->bind_param('i', $_GET['filmID']);
$stmt->execute();
$stmt->bind_result($filmName);
$stmt->fetch();
$stmt->close();
echo $filmName;

In the above we create a variable that I have called $stmt. Then we used the mysqli object created way back in the include file and the prepare method of the mysqli object.

The 'prepare' statement is a way of separating the values provided by the user from the SQL statement itself. The SQL above uses a ? to indicate where an external variable will appear - this is known as a position placeholder. The bind_param() method is then used to attached the variable to the position placeholder. We only have one integer value in the above example so the bind variable is flagged as 'i' for integer.

$stmt->bind_param('i', $_GET['filmID']);

So the above line effectively writes the value of $_GET['filmID'] into the SQL statement replacing the ?. You can bind multiple values for example:

$stmt = $mysqli->prepare("SELECT filmID, filmName FROM movies WHERE filmID = ? AND filmCert = ?");
$stmt->bind_param('is', $_GET['filmID'], $_GET['filmCertificate']);
$stmt->execute();
$stmt->bind_result($filmName, $filmCertificate);
$stmt->fetch();
$stmt->close();
echo $filmName;
echo $filmCert;

Note that in the above example that the filmCertificate value in the database is a varchar ie a string/text. Therefore the value used in the search is passed as a string in the bind_param() which is why we have the 's' flag in the line:

$stmt->bind_param('is', $_GET['filmID'], $_GET['filmCertificate']);

The types of data that can be bound are:

i – integer
d – double
s – string
b – blob

Warning: The order of the flags must equal to and correspond to the number of parameters to be bound to the query.


Take a look at this interactive page if you are struggling with the syntax here
.

When searching with a string with the 's' flag the position placeholders do not need to be placed in quotes.

Once the parameters are bound to the query we can execute the SQL with the execute() method.

The results from the query are bound to variables using the bind_result() method with sensible names. At this point the values aren't yet available to us - we need to call the fetch() method. This 'fetches' the results from our prepared statement into the bound variables set up by bind_result(). The variables declared in the bind_result() can now be used.

Finally to tidy things up we close the prepare statement with $stmt-close();

Multi Row Prepare Statements

In the above example we only retrieved one row from the database table. The following will extract multiple rows:

$stmt = $mysqli->prepare("SELECT filmID, filmName, filmCertificate FROM movies WHERE filmCertificate = ? ORDER BY filmName");
$stmt->bind_param('s', $_GET['filmCertificate']);
$stmt->execute();
$stmt->bind_result($filmID, $filmName, $filmCertificate);
while ($stmt->fetch()) {
echo "$filmName $filmCertificate";
}
$stmt->close();

The main difference between this and the previous example is that the $stmt->fetch is placed within a while loop so that each row retrieved is fetched.

while ($stmt->fetch()) {
echo "$filmName $filmCertificate";
}

As the $stmt->fetch() has to be before the $stmt->close() again you may find it useful to store these results in an array.

$filmNameArray = array();
$filmNCertArray = array();
while ($stmt->fetch()) {
    $filmNameArray[]=$filmName;
    $filmNCertArray[] = $filmCertificate;
}
$stmt->close();

This also allows you to create and execute an another query.

Wildcard LIKE Searches

Wildcard searches in SQL using LIKE take a little bit more preparation. Concatenate a variable with the % wildcards prior to binding the parameter ie:

$filmNameSearch = "%".$_GET['filmName']."%";

So in the fuller example:

$stmt = $mysqli->prepare("SELECT filmID, filmName, filmCertificate FROM movies WHERE filmName LIKE ?");
$filmNameSearch = "%".$_GET['filmName']."%";
$stmt->bind_param('s', $filmNameSearch);
$stmt->execute();
$stmt->bind_result($filmID, $filmName, $filmCertificate);
while ($stmt->fetch()) {
echo "$filmName $filmCertificate";
}
$stmt->close();

The Number of Rows returned

It is always useful to know the number of rows returned. The PHP mysqli meethod store_result() method and num_rows property will help us out here.

$stmt = $mysqli->prepare("SELECT filmID, filmName, filmCertificate FROM movies WHERE filmCertificate = ? ORDER BY filmName");
$stmt->bind_param('s', $_GET['filmCert']);
$stmt->execute();
$stmt->bind_result($filmID, $filmName, $filmCertificate);
$stmt->store_result();
$numRows = $stmt->num_rows;

Notice how the store_result() is called prior to the num_rows property been called. Order is important. You don't want your dessert before your soup.

Also it is important to place the store_result() before you fetch() the results of the query. If you only wanted to know if the query produced the results you could use:

$stmt = $mysqli->prepare("SELECT userName, password FROM user WHERE userName = ? AND password = ?");
$stmt->bind_param('ss', $_POST['userName'],  $_POST['password']);
$stmt->execute();
$stmt->store_result();
$numRows = $stmt->num_rows;
$stmt->fetch();
$stmt->close();

Notice how in the above there is no bind_result() as we just wanted to know how many results there were. This is often used on a login script as in the above simplified version.

Once the results of the query are stored using store_result(), free up memory by calling free_result().

$stmt->free_result();

Warning: Any call to fetch() to retrieve the values from a prepare statement must be made before close() or free_result().

$stmt = $mysqli->prepare("SELECT filmID, filmName, filmCertificate FROM movies WHERE filmCertificate = ? ORDER BY filmName");
$stmt->bind_param('s', $_GET['filmCert']);
$stmt->execute();
$stmt->bind_result($filmID, $filmName, $filmCertificate);
$stmt->store_result();
$numRows = $stmt->num_rows;

... some code later

echo "<p>Number of matches: {$numRows}</p>";
while ($stmt ->fetch()) {
	echo "{$filmName} {$filmCertificate}<br>";
}
$stmt->free_result();
$stmt->close();

Easy binding with get_result()

If you server has the mysqlnd driver installed you can make use of the get_result() method of a prepare statement. This can be used instead of bind_result and produces a resultset that can then turned into an array with a fetch() ie

$stmt = $mysqli->prepare("SELECT filmID, filmName, filmCertificate FROM movies WHERE filmCertificate = ? ORDER BY filmName");
$stmt->bind_param('s', $_GET['filmCert']);
$stmt->execute();
        
$result = $stmt->get_result();
        while ($row = $result->fetch){
	echo "{$row['filmName']} {$row['filmCertificate']}<br>";
}

Closing Things Off

To free up memory tidy up the end of your script by freeing and closing the prepare statement, and closing the database connection.

$mysqli->close()
Closes the database connection - in our examples that would be the variable $mysqli
$stmt->close()
Closes the prepare statement - in our examples that would be the variable $stmt
$stmt->free_result()
Frees stored result memory from the prepare statement - in our examples that would be the variable $stmt

20 Thoughts to “Using Object Oriented PHP with the mysqli Extension”

  1. thank you a lot. this was a useful tut.

  2. vandervidi

    Thank you! This is awesome

  3. awesome tutorial , perfect step by step explanation

  4. Dario

    That helped me a lot, keep it online 😀

  5. EmmsDan

    I am learn more about mysqli oop and as i was running a program it keeps telling me: Fatal error : Call to a member function bind_param() on a non-object in /home/ a8487913/public_html/m/
    inc/_f.php on line 13

    Here is my Code

    $host = 'host';
    $user = 'emmsdan';
    $pass = 'd75t78j2';
    $dba = 'database';
    // OOP way
    $db = new mysqli($host, $user, $pass, $dba);
    if(mysqli_connect_errno()) {
    die('The connection to the database could not be established.');
    }
    $stmt = $db->prepare("SELECT UserName, Email, Pix FROM Users WHERE Username OR Email LIKE %?%");
    $stmt->bind_param('s',$sn);
    $stmt->execute();
    $rsu = $stmt->get_result();
    while($row = $rsu->fetch_array()){
    $this->name = $row['UserName'];
    $this->mail = $row['Email'];
    $this->img = "";
    }

    1. admin

      Try placing the wildcards % around the bound variable rather than in the SQL ie

      $stmt = $db->prepare("SELECT UserName, Email, Pix FROM Users WHERE Username OR Email LIKE ?");
      $sn = "%".$sn."%";
      $stmt->bind_param('s',$sn);
      
  6. Gavaskar

    Thanks Very useful to me

  7. I will rewrite my coding using this knowlegde; looks very clear and OK, but I was searching for something good to use with a password storage in a database table (MySQL); I search for encryption : no items from the search.

    1. admin

      Watch this space. That is on my to do list.

  8. This is the best tutorial on prepare statement. Thanks

  9. Thanks much for the very helpful tutorial. I am struggling with oo php, but can't help wondering why you are not using "oo syntax" here?
    eg: " mysqli::store_result() ... etc. "
    Is it optional, then?
    RV

    1. admin

      The :: is used to access 'static' methods whereas -> is used for instance methods. In the examples here we have a 'mysqli' object/instance based on the class - see the connection file with the 'new mysqli'. Therefore we use the -> as we are calling an instance. Have a look at stack overflow for more on this

  10. One of the best tutorial I could find on OO mysqli. Thanks 🙂

  11. Moted

    Hey there, just came to say this is half OOP, half procedural. Still easy to understand the concept, so its appreciated 🙂

  12. Perfect explanation with examples. Thanks

  13. Dan

    Great tutorial! I've been entrenched in procedural style for years and have recently had to make the move to OOP for ease of query parameterization.
    One thing I keep running up against that I'm hoping you can help me with is this:
    I have a function that loops through a number of employees based on their job title and assigns certain training courses to them based on that job title.
    It seems that whenever I have my loop with a query inside it it works fine for the first one but then I get a fatal error on the second loop for 'call to member function bind_param() on a non-object'. Here's my code:

    foreach($newProgs as $newId){
    //get courseInfo
    $courseInfo = $mysqli->prepare("SELECT title, recert FROM courseInfo WHERE id=?");
    $courseInfo-> bind_param('i', $newId);
    if(!$courseInfo->execute()){
    exit( json_encode(array ('type'=>'error', 'message'=> "Failed to retrieve course information: " . $mysqli->error ) ) );
    } else {
    $courseInfoRS = $courseInfo->get_result();
    $courseInfoBit = $courseInfoRS->fetch_assoc();
    $cTitle = $courseInfoBit['title'];

    if($courseInfoBit['recert'] > 0){
    $certification = 'Y';
    } else {
    $certification = 'N';
    }
    $courseInfoRS->free_result();
    $courseInfo->close();
    }//end get courseInfo

    ....the rest of the function is here and works fine.
    }
    }

    Is there an issue with using the variable name $courseInfo over and over again in the loop? Or is something starring me in the face and I'm just missing it?

    1. admin

      You could try moving the prepare outside the loop and remove the $courseInfo->close(); ie:

      $courseInfo = $mysqli->prepare("SELECT title, recert FROM courseInfo WHERE id=?");
      foreach($newProgs as $newId){
      //get courseInfo
      $courseInfo-> bind_param('i', $newId);

      ....

      1. Dan

        I ended up finding a solution for this but I'll try your method out and see if it works. Thanks for the tip!

Leave a Comment