Mysqli helper function

1. Native mysqli solution (PHP > 8.2)
2. The main problems of mysqli
3. The saving grace
4. The code
5. A helper function for SELECT queries
6. Examples
7. Comments (2)

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.

Native mysqli solution (PHP > 8.2)

For the recent PHP versions there is a built-in function, mysqli_execute_query(). So for the examples below you can change prepared_query($db and prepared_select($db to $db->execute_query( like this:

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

without using any user-defined functions.

The main problems of mysqli

There are several problems with mysqli

• It is too verbose. What can be done in PDO in one line takes half a dozen in mysqli
• A lot of decent features are available with mysqlnd driver only (however, this issue is not anymore in 2018 and beyond)
  • For example, without mysqlnd it's impossible to get a usual associative array / object from a prepared statement directly
• A hell of a code you had to write to make a simple wrapper function that runs a given query with given parameters (a thing that can be done in PDO by simply sending an array with parameters into execute).

The saving grace

Luckily, PHP greatly evolved recently, 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 us a sane amount of code to write

• Verbosity can be reduced with a simple wrapper function
• Mysqlnd is now a standard
• there is a get_result() method that can give you a familiar associative array right from the prepared statement
• there is an argument unpacking operator that allows you to bind unknown number of parameters without a screenful of sophisticated code

The code

So here is the code:

function prepared_query($mysqli, $sql, $params, $types = "")
{
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

This function accepts four arguments. Three mandatory:

• Mysqli connection object (for the proper connection code see the corresponding chapter)
• an SQL query where question marks (placeholders) have to be put instead of PHP variables.
• an array with variables to be bound to respective placeholders

and one optional:

• a string with parameter types in case we'd decide to set them explicitly (almost never needed)

and the code goes as follows:

• 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, because Mysql will gladly accept almost any type of data as a string.
• then we are calling prepare() to prepare an SQL query with placeholders.
• then we are calling bind_param(), using $types variable defined before and $params array that contains all the variables to be used in the query.

• finally we are executing the query and returning the statement, al in one go:

  prepared_query($db, "UPDATE users SET active=1 WHERE email=?",[$email]);

Mission accomplished!

A helper function for SELECT queries

If you don't like that ->get_result() part that is just a routine for any SELECT query, you can create a sister function for SELECT queries, let's call it prepared_select():

function prepared_select($mysqli, $sql, $params = [], $types = "") {
    return prepared_query($mysqli, $sql, $params, $types)->get_result();
}

And now you can get rid of this get_result thingy:

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

Not a big deal but when you have to write a lot of queries...
Either way, it is entirely up to you whether to use such a function or not.

Examples

The following code snippet can be run as is, given there is a mysqli.php file in the current directory contains the code from the corresponding chapter with database credentials properly set.

<?php
require 'mysqli.php';

#Create a temporary table
$db->query("CREATE temporary TABLE tmp_mysqli_helper_test
              (id int auto_increment primary key, name varchar(9))");

# populate it with sample data
$sql = "INSERT INTO tmp_mysqli_helper_test (name) VALUES (?),(?),(?)";
$stmt = prepared_query($db, $sql, ['Sam','Bob','Joe']);
echo "Affected rows: $stmt->affected_rows\n";
echo "Last insert id: $db->insert_id\n";

# Getting rows in a loop
$sql = "SELECT * FROM tmp_mysqli_helper_test WHERE id > ?";
$result = prepared_select($db, $sql, [1]);
while ($row = $result->fetch_assoc())
{
    echo "{$row['id']}: {$row['name']}\n";
}

# Getting one row
$id  = 1;
$sql = "SELECT * FROM tmp_mysqli_helper_test WHERE id=?";
$row = prepared_select($db, $sql, [$id])->fetch_assoc();
echo "{$row['id']}: {$row['name']}\n";

# Update
$id = 1;
$new = 'Sue';
$sql = "UPDATE tmp_mysqli_helper_test SET name=? WHERE id=?";
$affected_rows = prepared_query($db, $sql, [$new, $id])->affected_rows;
echo "Affected rows: $affected_rows\n";

# Getting an array of rows
$start = 0;
$limit = 10;
$sql = "SELECT * FROM tmp_mysqli_helper_test LIMIT ?,?";
$all = prepared_select($db, $sql, [$start, $limit])->fetch_all(MYSQLI_ASSOC);
foreach ($all as $row)
{
    echo "{$row['id']}: {$row['name']}\n";
}

See this code online