How to use mysqli properly

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

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 the old stuff.

Prepared statements

The main reason why you were deprived from your beloved mysql_query() 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 a prepared statement. In order to do so you have to follow three simple 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 is a simple example:

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

Like it was described before, first we are preparing a query with question marks, then bind variables, then execute the statement and then getting the result.

The alleged simplicity of the old mysql code

Some people would complain prepared statements are more complex. Let me show you that it is simply not true. 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? check the following code then:

$sql "SELECT * FROM goods WHERE color=? AND 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 the old approach.


Related articles: