Mysqli helper function

1. Native mysqli solution
2. Temporary substitution
3. Examples
4. Previous version of this article, now obsoleted
5. Comments (2)

Previously this article featured a userland function, prepared_query(), but there is no reason to use it anymore.

Native mysqli solution

PHP 8.2 got a great new function, mysqli_execute_query(), which does all the job of prepare/bind/ execute in one go (which makes mysqli better than PDO in this regard, as the latter doesn't have such function (yet)).

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

without using any user-defined functions.

Temporary substitution

but if your PHP version is not that recent, you can still use this function by creating a temporary userland implementation. The code below extends the mysqli class adding this new method

$mysqli_class_name = 'mysqli';
if (version_compare(PHP_VERSION, '8.2', '<')) {

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    function mysqli_execute_query(mysqli $mysqli, string $query, ?array $params = null)
    {
        $stmt = $mysqli->prepare($query);
        if ($params) {
            $types = str_repeat("s", count($params));
            $stmt->bind_param($types, ...$params);
        }
        $stmt->execute();
        return $stmt->get_result();
    }    

    class my_mysqli extends mysqli {
        public function execute_query(string $query, ?array $params = null)
        {
            return mysqli_execute_query($this, $query, $params);
        }
    }
    $mysqli_class_name = 'my_mysqli';
}
$mysqli = new $mysqli_class_name($hostname, $username, $password, $dbname);

You can add a code like this to where your database connection is created and it would universally work for any PHP version >= 7.0. In case current PHP version is below 8.2, it will use the extended version of mysqli class, and the original class otherwise.

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 (?),(?),(?)";
$db->execute_query($sql, ['Sam','Bob','Joe']);
echo "Affected rows: $db->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 = $db->execute_query($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 = $db->execute_query($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=?";
$db->execute_query($sql, [$new, $id]);
echo "Affected rows: $db->affected_rows\n";

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

See this code online

Previous version of this article, now obsoleted

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.

*Example

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