How to use mysqli properly

  1. Prepared statements
  2. The alleged simplicity of the old mysql code
  3. Comments

One of the worst delusions that PHP folks ever shared, is one that reads "mysqli is as simple as old mysql_query, and the transition is as simple as just adding "i" to the function names".
Unfortunately, almost every word in this statement is a dangerous mistake. In fact, there are a lot of essential differences, that make the proper use of mysqli completely different from old stuff.

Prepared statements

The main reason why you were deprived from your beloved mysql_query() way is the fact that this honest function doesn't support prepared statements. And there is absolutely no point in continuing this dangerous practice with mysqli. Means you ought to use prepared statements with mysqli, which makes the transition a complete rewrite of the every database interaction.

Therefore, all your database interactions that involve a variable should be performed using prepared statements. In order to do so you have to follow three steps:

  1. Prepare your sql query, adding ? marks where a variable would have been used
  2. Bind these variables to the previously prepared statement
  3. Execute the statement

Here you will find some examples for the most frequent cases:

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

The alleged simplicity of the old mysql code

In fact, nowhere your old code was simple. Let's take a typical example:

$color mysql_real_escape_string($_POST['color']);
$qty   intval($_POST['qty']);

$sql "SELECT * FROM goods WHERE color='$color' AND qty > '$qty'";
$res mysql_query($sql) or trigger_error(mysql_error());

$data = array();
while (
$row mysql_fetch_assoc()) {
    
$data[] = $row;
}

Still calling this code simple?

Well, in fact, it's rather a bloated bunch of lines, most of which are repeated in every database interaction. If you leave only meaningful part, it will make one single line of code:

$data DB::query("SELECT * FROM goods WHERE color=? AND qty > ?"$_POST['color'], $_POST['qty']);

This code one could call simple indeed.
But of course it is cheating, as we were using an external piece of software, a database wrapper. But still, with vanilla mysqli we can make our code shorter

$sql "SELECT * FROM goods WHERE color='$color' AND qty > '$qty'";
$stmt mysqli->prepare($sql);
$stmt->bind_param("ss"$_POST['color'], $_POST['qty']);
$stmt->execute();
$data $stmt->get_result()->fetch_all();

As you can see, the proper usage of mysqli is not only 100% safe but also more tidy than old approach.


Related articles: