Mysqli tutorial (how to use it properly)

Connection
Object and procedural interface
Executing queries with PHP variables. Prepared statements

Bind in execute
Mysqli_execute_query

Prepared SELECT queries
Fetching query results

Getting a single row
Getting multiple rows in a loop
Using query results

Executing queries without variables
Error handling
Number of rows returned by SELECT statement
Number of rows affected by data modification queries
Comments (19)

Connection

The importance the proper connection code is often overlooked, and it's often being diminished to just a single line. Whereas connecting the right way can solve a multitude of problems, from weird characters to cryptic error messages.

Given your code is the usual procedural PHP, here is a simple mysqli connection code to be included in your scripts:

$host     = '127.0.0.1';
$db       = 'test';
$user     = 'root';
$password = '';
$port     = 3306;
$charset  = 'utf8mb4';

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli($host, $user, $password, $db, $port);
$db->set_charset($charset);
$db->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

The full explanation can be found in the dedicated article How to connect using mysqli (as well as many useful hints), but just a small citation to highlight most important parts:

setting the proper character set for the connection will eliminate the whole class of errors, such as weird characters/question marks instead of your data, empty json_encode() output, problems with storing emojis, etc.

setting the proper error reporting mode will eliminate the cryptic error messages like mysqli_fetch_assoc() expects parameter... / Call to a member function bind_param()..., giving you the actual error message from MySQL instead.

Object and procedural interface

One small but important note: mysqli bears one unique feature: all its functions can be accessed using both object and procedural syntax. Means each function can be called either as a function or as an object's method:

mysqli_query($mysqli, $query); // procedural syntax
$mysqli->query($query); // object syntax

The only difference is that for the object syntax we take the function's parameter ($mysqli for example), add the object operator (->) and then call the actual method name, cutting off the redundant "mysqli_" part. Note that you don't need to know OOP in order to use the object syntax: it's just the way the same function call is written.

Both methods are totally interchangeable, the difference is only in the style. You can use either. Both styles can be even mixed in the code but that would be a very bad practice, style-wise.

Given the object style is more concise, without constant repetitions (i.e. mysqli_stmt_get_result($stmt) vs. $stmt->get_result()) I highly recommend it. And so it will be used in this article.

Executing queries with PHP variables. Prepared statements

One of the main reasons why old mysql ext was removed from PHP is the fact it didn't support prepared statements, so PHP variables inevitably had to be added right into SQL. But there is absolutely no point in continuing this dangerous practice with mysqli. Means you ought to use prepared statements, which makes the transition a complete rewrite of the every database interaction.

Why should we use prepared statements? For such a simple reason that when the data is added directly to the SQL statement, it may corrupt the query. With consequences ranging from a syntax error to SQL injection. Unlike infamous "escaping" approach that works only for strings and can be often overlooked, forgotten or misused, prepared statements let us formulate a simple but bullet-proof three-step instruction:

Prepare your sql query, adding ? marks where a variable would have been used
Bind these variables to the previously prepared statement, setting the type for each
Execute the statement

Here is a simple example for the INSERT query:

$stmt = $db->prepare("INSERT INTO users (email, password) VALUES (?,?)");
$stmt->bind_param("ss", $email, $password_hash);
$stmt->execute();

As you can see, nothing complex or confusing, just the three steps described above.

Let's take a closer look at the prepared statement, using UPDATE query for example

$sql = "UPDATE users SET name=?, email=?, password=? WHERE id=?";
$stmt= $conn->prepare($sql);
$stmt->bind_param("sssi", $name, $email, $password, $id);
$stmt->execute();

What is going on here?

$sql = "UPDATE users SET name=?, email=?, password=? WHERE id=?";

Like it was said above, first we are writing SQL, where all variables are substituted with question marks.

IMPORTANT! there should be no quotes around question marks, you are adding placeholders, not strings.

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

Then, the query gets prepared. The idea is very smart. To avoid even a possibility of SQL injection or a syntax error caused by the input data, the query and the data are sent to database server separately. So it goes on here: with prepare() we are sending the query to database server ahead. A special variable contains a mysqli statement is created as a result. We would use this variable from now on.

$stmt->bind_param("sssi", $name, $email, $password, $id);

Then variables must be bound to the statement. The call consists of two parts - the string with types and the list of variables. With mysqli, you have to designate the type for each bound variable. It is represented by a single letter in the first parameter. The number of letters should be always equal to the number of variables. The possible types are

i for integer
d for double (float)
s for string
b for blobs

So you can tell now that "sssi" means "there would be 3 variables of string type and the last one of integer type". And then, naturally, four variables obediently follow.

a quick tip: MySQL will gladly accept all variables as strings, so don't go nuts finding the correct type for a certain variable, simply using "s" for all.

$stmt->execute();

Then the query finally gets executed. Means variables get sent to database server and the query is actually executed.

IMPORTANT! You shouldn't check the execution result. Given you have the proper connection code mentioned above, in case of error mysqli will raise an error automatically.

Just for sake of completeness, here is the DELETE query example, but I hope you've got the idea already:

$sql = "DELETE FROM users WHERE id=?";
$stmt= $conn->prepare($sql);
$stmt->bind_param("s", $id);
$stmt->execute();

Bind in execute

Note that starting from PHP 8.1 the bind_param part can be omitted and variables can be sent directly to execute (in the form of array):

$stmt = $db->prepare("INSERT INTO users (email, password) VALUES (?,?)");
$stmt->execute([$email, $password_hash]);

In this case all variables are bound as strings.

Mysqli_execute_query

Finally, starting from version 8.2, PHP got an excellent function, execute_query(), which lets to execute a prepared statement in one go:

$db->execute_query("INSERT INTO users (email, password) VALUES (?,?)", [$email, $password_hash]);

In this case all variables are also bound as strings.

It case your PHP version is less than 8.2, here you can learn how to add a temporary substitution for this indispensable function.

Prepared SELECT queries

SELECT queries executed exactly the same way as described above, except for one small addition: we need to get the query result so it can be used to fetch the returned data:

$stmt = $db->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();

here, the get_result() method returns an instance of the special mysqli_result class that allows us to fetch the returned rows as arrays or objects.

Note that if it says that get_result() is not found, you will need to tick some checkbox labeled php_mysqlnd in the PHP configuration section in your ISPMAnager.

Note that execute_query() method already returns the mysqli_result instance, so you don't have to call it explicitly (which makes it even more useful!):

$row = $db->execute_query("SELECT * FROM user WHERE email = ?", [$email])->fetch_assoc();

Fetching query results

To get the rows returned by SELECT query we are using a special variable being instance of mysqli_result class. All fetching functions work with this variable.

The generic method for either fetching a single row or multiple rows would be using one of mydsli fetch functions:

fetch_row() returns enumerated array
fetch_assoc() returns associative array
fetch_object() returns an object

Getting a single row

If we need to fetch just a single row, then we should call just one of the above functions. For example:

$stmt = $db->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
echo $row['username'];

or, using execute_query():

$row = $db->execute_query("SELECT * FROM user WHERE email = ?", [$email])->fetch_assoc();
echo $row['username'];

Getting multiple rows in a loop

That's an interesting part, because to get multiple rows we will use the same function we were using to get a single row! All thanks to one simple detail: when the row is fetched, the internal pointer in the result set is moved one position further, so the next call to the same function will return the next row - and so on, until there are no rows in the resultset, when the fetch function will return null. So we can use the while loop to get all rows:

$users = [];
$sql = "SELECT * FROM users ORDER BY id DESC LIMIT 0, 10";
$result = $db->query($sql);
while ($row = $result->fetch_assoc()) {
    $users[] = $row;
}

Here we are getting all the returned rows into array $users.

A tip: mysqli has a handy function that returns all the rows into array in a single call: mysqli_fetch_all(). By default it uses fetch_row() method with numeric indices, so if you want array elements become associative arrays, use the MYSQLI_ASSOC constant as a parameter:

$sql = "SELECT * FROM categories";
$result = $db->query($sql);
$data = $result->fetch_all(MYSQLI_ASSOC);

Using query results

Please note that echoing the query results right away is frowned upon nowadays. You are supposed to collect all the necessary data first, and only then start with output. Which is going to be just a regular foreach, unrelated to any database stuff:

// get the data
$data = $result->fetch_all(MYSQLI_ASSOC);
// close the PHP tag to avoid all the hassle with echoing HTML
?>
<head>
<body>
<?php if ($data): ?>
  <table>
    <tr><th>Name</th><th>Score</th></tr>
<?php foreach ($data as $row): ?>
      <tr>
        <td><?= htmlspecialchars($row['name'])</td>
        <td><?= htmlspecialchars($row['score'])</td>
      </tr>
<?php endforeach ?>
  </table>
<?php elseif: ?>
  No data!
<?php endif ?>?>

Executing queries without variables

When a query is entirely hardcoded, i.e. no PHP variables are used in it, one can use the query() function. It already returns the mysqli_result object and any fetch function can be chained to it:

$menu = $db->query("SELECT * FROM menu")->fetch_all();
$count = $db->query("SELECT count(*) FROM users")->fetch_row()[0];

Error handling

Error handling is the most important yet somewhat surprising part. Despite what numerous articles and examples say, as a rule, you shouldn't write any error handling code at all. It sounds very unusual but that's exactly how things must be done. Most of time all you need to do is just report the error. And mysqli/PHP already can do it for you, no help required. Therefore, you shouldn't write any code that verifies the query execution result - in case of error mysqli will report it automatically, thanks to the mysqli_report() function call mentioned above. Thus, all database interaction errors will be processed uniformly, exactly the same way as all other PHP errors, which is extremely convenient, allowing to process all errors in a single place, as opposed to writing the dedicated handling code for the every single query. Again, the full explanation of this principle can be found in another article, dedicated to PHP error reporting.

On a rare occasion when you really need to handle the error, that is, to perform some action in case of error instead of just reporting it, then wrap your query(es) in a try..catch.

Number of rows returned by SELECT statement

There is no use for the familiar mysqli_num_rows() function. If you think of it, you can always use the data itself, to see whether your query returned any rows:

$user = $result->fetch_assoc();
if ($user) {
    // found!
}

the same goes for the multiple rows, thanks to a handy function mysqli_fetch_all() that can get you an array of all selected rows in one go.

And of course you should never select more rows then can be processed on a single page. This applies either to the query that selects the actual rows and - especially - to the query that is used specifically to select the number of rows. In the latter case, a SELECT count(*) must be used instead.

Number of rows affected by data modification queries

Unlike the above, the number of rows affected by the INSERT, UPDATE and DELETE query could be quite useful. What is interesting, mysqli has not one but two facilities that can return such a number.

One of them is familiar affected_rows property:

$db->query("DELETE FROM users");
echo $db->affected_rows();

But one must remember that MySQL will return here only the number of rows that were actually changed. If a row were found but the new data is the same as old, MySQL won't count such a row towards that number. Luckily, mysqli has an unique mysqli_info() function which returns separate results for the the number of rows found and affected by a DML query. In any other driver, including PDO, you can have either one or another but not both at once. Although it returns a string, a simple code could be written to parse its result into a neat array.

So, a bit more readable version of this code can be made into a simple function like this

function mysqli_info_array($mysqli) {
    preg_match_all('~: (\d+)~', $mysqli->info, $matches); 
    return [
        'matched' => (int)$matches[1][0],
        'changed' => (int)$matches[1][1],
        'warnings' => (int)$matches[1][2],
    ];
}

and after that one will be able check the number of found and affected rows separately. For example, given we are updating just a single row,

$db->query("update test set i=2");
$info = mysqli_info_array($db);
if ($info['matched'] === 0) {
    $result = "No rows were matched";
} elseif($info['changed'] === 0) {
    $result = "Rows were found but not updated";
} else {
    $result = "Rows were found and updated";
}

Comments:

Neil E Dempster, 29.03.23 14:33
In my earlier comment I neglected to ask: While PHP has a built-in, easy one-liner to determine affected rows from an UPDATE
```
$count = mysqli_affected_rows($db);
```
what am I doing wrong with your prepared statements (mysqli) to fail to get it to work? The code only results in -1 (error) even though the UPDATE is successful. I would like to learn from this so if you could provide some insights here, I would be very thankful. Here is the prepared query:
```
$sql_update = "UPDATE tbl_sup SET supervisor_name=? WHERE session_id = ?";
prepared_query($dbc, $sql_update, [$supervisor_name, $session_id]);
```
Reply:
Hello Neil!

Thank you for this question. Indeed that's a strange behavior that in the end looks rather expected.

When $stmt gets destroyed after the function call, it seems it affects the affected rows as well. To solve the problem simply assign the function result to a variable. It doesn't have to be used, it should just exist. See the difference:
```
$sql_update = "UPDATE tbl_sup SET supervisor_name=? WHERE session_id = ?";
$tmp = prepared_query($dbc, $sql_update, [$supervisor_name, $session_id]);
echo mysqli_affected_rows($dbc); // 1
$sql_update = "UPDATE tbl_sup SET supervisor_name=? WHERE session_id = ?";
prepared_query($dbc, $sql_update, [$supervisor_name, $session_id]);
echo mysqli_affected_rows($dbc); // -1
```

Neil E Dempster, 29.03.23 01:47

After many hours of trying to get the PHP native

$count = mysqli_affected_rows($dbc);

to work with your (very impressive) Prepared Statements method, I gave up and went to Plan B (using your code as a startpoint). Although obscenely long, at least this works:

$query_response = mysqli_info($dbc);
$pattern = '~Rows matched: (?<matched>\d+)  Changed: (? <changed>\d+)  Warnings: (?<warnings>\d+)~';
preg_match($pattern, $query_response, $matches);
$info = array_filter($matches, "is_string", ARRAY_FILTER_USE_KEY);

if(intval($info['changed']) === 1) {
    $query_update = array('success' => true);
} else {
    $query_update = array('success' => false);
}
return $query_update;

robot, 24.01.23 13:21

im robot
Kimbo, 08.11.22 04:15

hitting a wall here with php & mysql, getting no data for rows that 100% certainly do exist, troubleshooting suggestions?

Reply:
Hello Kimbo!

Yes, I've got a couple texts in this regard.

First, this answer on Stack Overflow, https://stackoverflow.com/a/57986043/285587 that sums up nearly thousand similar questions. And second, this text here that can give you an idea what to do to debug your data, https://phpdelusions.net/pdo/mcve

Hope some it it would help!
David, 30.07.22 08:30

Thanks very much! This is an excellent tutorial! Absolutely the best I've found so far. Love it!

Neil E Dempster, 09.11.21 12:43

Hello. Your prepared statements are exceptional and have worked every time except for a recursive query that works using the 'old' while loop but when I use your prepared statement it brings in an empty array. Any direction here? Thank you!

$sql_query = "SELECT w.kp_id AS id, w.user_id AS sponsor_id, w.position, w.area, w.urgency, 
IF(w.criteria1='false', null, 'true') AS criteria1, 
IF(w.criteria2='false', null, 'true') AS criteria2, 
IF(w.criteria3='false', null, 'true') AS criteria3, 
IF(w.ro='false', null, 'true') AS ro, 
w.span, m.first_name, m.last_name, 
CONCAT(m.first_name,' ',m.last_name) AS sponsor_name FROM worksheet_1_1b w 
INNER JOIN master m ON w.user_id = m.email_address 
WHERE user_id IN
(WITH RECURSIVE allchilds AS (SELECT jobholder_uid, positionmgr_uid, position_level FROM positions WHERE positionmgr_uid = ?
UNION ALL
SELECT p.jobholder_uid, p.positionmgr_uid, p.position_level FROM positions p INNER JOIN allchilds ac ON ac.jobholder_uid = p.positionmgr_uid)
SELECT jobholder_uid FROM allchilds ac WHERE position_level > 1)";
$stmt = prepared_query($dbc, $sql_query, [$user_id]);
$child_mgmt_levels = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

Reply:

Hello Neil!

This function is hardly responsible for the empty result as it runs the same while loop under the hood.

Please double check if the query returns the result and $user_id has the correct value.

Neil E Dempster, 17.10.21 16:20

Hello. Should I use mysqli_free_result($stmt) after using your prepared statements? I did not see (or, maybe missed) this in your amazing tutorial(s). Thank you.
Neil E Dempster, 14.10.21 22:22
Hello. I am using your helper script and I've added the "s" to ensure I get back a string but this code still returns the ID as an int. I've also tried json_encode but I still get an int not a string. What am I missing? Thank you!
```
$sql = "SELECT kp_id AS id FROM worksheet_1_1b WHERE user_id = ?";
$stmt = prepared_query($dbc, $sql, [$user_id], "s");
$ws_session = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
```
Reply:
Hello Neil.

That's quite a peculiar request because people usually want the opposite - to have an id returned as int.

I don't know the immediate solution. "s" in the bind_param only affects the input, but not the input.

May be you can change your requirement and settle with an int. Not sure what having it as string could be good for.
Neil, 27.05.21 16:20

I read a suggestion in a PHP forum that instead of supplying all of the credentials each time you connect to a DB that you set the credentials in the PHP.ini file and use 'safe mode.' The theory was by having it configured there you will make your source code safe from including a password. What are your thoughts on that? Thank you. Here are the config settings described:

sql.safe_mode = On mysqli.default_host = "127.0.0.1" mysqli.default_port = "3306" mysqli.default_user = "root" mysqli.default_pw = "Password123"

Reply:
That's also a very good question.

Incidentally, I wrote a comprehensive answer on Stack Overflow on the matter, https://stackoverflow.com/a/65950036/285587

In short, there is nothing wrong in having a database password in the source code, as long as this file is not in the git.

Other methods are also acceptable (such as .env, sever config, etc.) but again, an ol' good PHP file is all right too. and I'd say for ae beginner it's the most convenient method.

Regarding sql.safe_mode, i see it has been removed long time ago. Looks like your source a bit dated ;)

tven, 03.05.21 05:09

<?php $servername = "localhost"; $username = "root"; $password = ""; $database = "phpDB"; //create connection $conn = new mysqli($servername, $username, $password, $database);

//check connection
if($conn->connect_error){
    die("Connection failed: ".$conn->connect_error);
}

function crud($choice, $values){
    //create
    if($choice == 1){
        $sql = "INSERT INTO accounts(username, password, email) VALUES ('".$values['username']."','".password_hash($values['password'], PASSWORD_DEFAULT)."','".$values["email"]."')";
    }//read
    else if($choice == 2){

    }//update
    else if($choice == 3){

    }//delete
    else if($choice == 4){

    }
}
$conn->close();

How am I able to have a reusable code with this function with two parameters?

reza, 21.05.20 17:12

In PHP 7.2 Error deadly Reply returned

$result = $mysqli->query("SELECT id,fieldname from tblcustomfields WHERE type='client'");

Reply:
I have no idea what a deadly reply is. It is specific to your system. Make sure you created a mysqli connection as explained in this article

reza, 21.05.20 16:13

Hello engineer

How to change this piece of code to mysqli

$sql = full_query("SELECT id,fieldname from tblcustomfields WHERE type='client' " );
$cf = array(); 
if(mysql_num_rows($q)){
    while($o = mysql_fetch_object($q)){
        $cf[$o->id] = $o->fieldname;                
    }   
}
unset($sql);

Thanks for helping me

Reply:

Hello reza

There is nothing much to change apart from the fact this code doesn't seem to make sense. Anyway it would be like

$result = $mysqli->query("SELECT id,fieldname from tblcustomfields WHERE type='client'");
$cf = array(); 
while($o = mysqli_fetch_object($result)){
    $cf[$o->id] = $o->fieldname;                
}

where $mysqli is your mysqli connection.

Phil, 16.04.20 20:47

Which is better for performance mysqli or PDO?
Tao, 03.08.19 14:50

Hi! Love how you easily explain how MySQL works, but I have a question, what is "s" and "ss"? Didn't understand why did you add that on bind_param. Thanks!
Dharman, 26.06.19 02:00

Is $stmt->get_result->fetch_assoc(); an intentional typo to fight plagiarism or an honest mistake? I have seen few typos in your articles, but I assumed that if they were in a paragraph they were intentional to combat plagiarism, however if it is a code sample it could be accidental. Would it be possible to fix this one?

Reply:
Hello Dharman, nice to see you here!

No, all typos are not intentional, so I'll be grateful if you drop a line if you see one.
Usually I test the code before posting it but sometimes I neglect this rule and so the outcome :(

Thank you for this, it is fixed now.
Heather, 16.11.18 02:13

My server folks recently upgraded the mysql engine from MyISAM to InnoDB. I have a PHP script that queries the database, pulls a value, and passes it through a form to an script that updates the database. Now, $_POST will pass the correct value for older data, but will pass a blank value for anything that was put into the database since the switch, Any ideas why this would be happening?

Reply:
Hello Heather!

The issue probably has nothing to do with the database engine (Innodb) but I would say it does something to do with encodings.

You have to debug your process and spot the exact point where the value becomes empty. You should check every step, including all operations before and after saving the value in the database.

I could only guess the possible cause. Judging by symptoms, it could be htmlspecialchars() without the encoding parameter - it will silently make an empty string.

Feel free to check back if you won't find an answer, I'll try to think of something else
Dhananjay Kumar Yadav, 17.09.18 10:05

Well explained the concept of mysqli prepare statement.This blog is very specific and tells us the exact thing without beating around the bush.
R2D2, 25.05.18 16:22

Hi, i found a dead link in https://phpdelusions.net/mysqli , called "database wrapper" and linking to https://phpdelusions.net/pdo/wrapper Cheers Marty
luckyme, 04.04.18 08:10

Invalid argument supplied for foreach() can u solve this plz...

Reply:
Hello luckyme

You need to check a variable passed to foreach. It should be an array but looks like it isn't

Mysqli tutorial (how to use it properly)

Connection

Object and procedural interface

Executing queries with PHP variables. Prepared statements

Bind in execute

Mysqli_execute_query

Prepared SELECT queries

Fetching query results

Getting a single row

Getting multiple rows in a loop

Using query results

Executing queries without variables

Error handling

Number of rows returned by SELECT statement

Number of rows affected by data modification queries

Related articles:

Got a question?

SEE ALSO:

Latest comments:

Add a comment

Comments:

Reply:

Reply:

Reply:

Reply:

Reply:

Reply:

Reply:

Reply:

Reply:

Reply: