Mysqli made simple

  1. Main problems of mysqli
  2. The saving grace
  3. The code
  4. Examples

During recent years I spend a considerable effort telling the PHP community that PDO is much better choice than mysqli for a beginner.

But many people still prefer to use mysqli and there is a way to make it less painful than it usually is.

Main problems of mysqli

There are several problems with mysqli

The saving grace

Luckily, PHP greatly evolved these years, making most of the problems solved. Still we will need a simple wrapper function (but to be honest, we need such for PDO as well) but at least such a wrapper function / class would take a sane amount of code

The code

So here is the code:

function mysqli($mysqli$sql$params = [], $types "")
    if (!
$params) {
$types $types ?: str_repeat("s"count($params));
$stmt $mysqli->prepare($sql);
$stmt->bind_param($types, ...$params);

This function accepts four arguments. Two mandatory:

and two optional:

In a function, we are running the usual query() method if there are no parameters, and a prepared query otherwise.

For the prepared query, first we are defining the $types variable - it should be either a string supplied in the last parameter or a string consists of "s" symbols.
Then we are calling prepare() and then bind_param(), which is done in one simple call thanks to the argument unpacking operator.
Finally we executing the query and returning the statement. After that, we can get whatever information returned by query using the method chaining.


Getting an array of rows

$start 0;
$limit 10;
$sql "SELECT * FROM users LIMIT ?,?";
$user_list mysqli($mysqli$sql, [$start$limit])->get_result->fetch_all(MYSQLI_ASSOC);

As a result we will get an array that consists of all the rows returned by the query. Compare this tiny snippet to the amount of code you are usually bound to write!

Getting a single row is just as simple:

$id 10;
$sql "SELECT * FROM users WHERE id=?";
$user_data mysqli($mysqli$sql, [$id])->get_result->fetch_assoc();

Now we have an associative array with user data!

Insert and update queries are also very easy

$sql "INSERT INTO users SET username=?, password=?";
mysqli($mysqli$sql, [$username,$password]);

$sql "DELETE FROM users WHERE id=?";
$affected mysqli($mysqli$sql, [$id])->affected_rows();

Related articles: