Mysqli helper function

  1. Main problems of mysqli
  2. The saving grace
  3. The code
  4. A helper function for SELECT queries
  5. Comments

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 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:

In this function, 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 variable that contains all the variables to be used in the query.
Finally we executing the query and returning the statement.

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
$conn->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($conn$sql, ['Sam','Bob','Joe']);
echo 
"Affected rows: $stmt->affected_rows\n";
echo 
"Last insert id: $conn->insert_id\n";

# Getting rows in a loop
$sql "SELECT * FROM tmp_mysqli_helper_test WHERE id > ?";
$res prepared_query($conn$sql, [1])->get_result();
while (
$row $res->fetch_assoc())
{
    echo 
"{$row['id']}{$row['name']}\n";
}

# Getting one row
$id  1;
$sql "SELECT * FROM tmp_mysqli_helper_test WHERE id=?";
$row prepared_query($conn$sql, [$id])->get_result()->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($conn$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_query($conn$sql, [$start$limit])->get_result()->fetch_all(MYSQLI_ASSOC);
foreach (
$all as $row)
{
    echo 
"{$row['id']}{$row['name']}\n";
}

It should produce the following output:

Affected rows3
Last insert id
1
2
Bob
3
Joe
1
Sam
Affected rows
1
1
Sue
2
Bob
3
Joe

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 mysqli_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.


Related articles: