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

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

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:

and one optional:

and the code goes as follows:

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


Related articles: