Mysqli helper function

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

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.

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.

Extending mysqli

So far we are doing well but a function looks a bit awkward, especially given all the encouragement I am giving for the object syntax. Luckily, we can use such a handy OOP feature as inheritance! We can simply extend the existing class, adding a new method to vanilla mysqli:

class my_mysqli extends mysqli
{
    public function 
prepared_query($sql$params$types "")
    {
        
$types $types ?: str_repeat("s"count($params));
        
$stmt $this->prepare($sql);
        
$stmt->bind_param($types, ...$params);
        
$stmt->execute();
        return 
$stmt;
    }
    function 
prepared_select($sql$params = [], $types "")
    {
        return 
$this->prepared_query($sql$params$types)->get_result();
    }
}

then create an object using this class instead of vanilla mysqli:

mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
$db = new my_mysqli($host$user$pass$db$port);
$db->set_charset($charset);

and start using your two new shiny methods right away!

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 $db->prepared_query($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 $db->prepared_select($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->prepared_select($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 $db->prepared_query($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 $db->prepared_select($sql, [$start$limit])->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

Related articles: