Procedural function for prepared MySQL statements in PHP I'm refactoring procedural code, switching to prepared MySQL statements using mysqli_query(). I do understand writing OO would be better, but it's out of scope for this task.

Use the below function as follows:

list($result, $numrows, $err) = my_mysqli_stmt($db_handler, $query, $params);

Returns array with three values, $result, $numrows and $err.

$result is either false on error, or contains empty or multi-dimensional array.

$numrows returns number of affacted rows, number of returned rows or 0 on error.

$err returns mysqli_error() or mysqli_stmt_error() error.

function my_dbstmt($handler, $query, $params=array())
    {   
    $stmt = mysqli_prepare($handler, $query);

    if($stmt===false)
        {
        return array(false, 0, mysqli_error($handler));
        }

    //First two parameter of mysqli_stmt_bind_param
    $refarg = array($stmt, array2typestring($params));

    //create array of parameters' references
    foreach ($params as $key =&gt; $value)
        {
        $refarg[] = &amp;$params[$key];
        }

    if(!empty($params))
        {
        //bind parameters with dynamic length
        call_user_func_array("mysqli_stmt_bind_param", $refarg);
        }

    $exec = mysqli_stmt_execute($stmt);

    if($exec===false)
        {
        return array(false, 0, mysqli_stmt_error($stmt));
        }

    $result = mysqli_stmt_get_result($stmt);

    if($result===false) 
        {
        //not a result, but successfully executed
        $numrows = mysqli_stmt_affected_rows($stmt);
        return array(true, $numrows, null);
        }
    else 
        {
        mysqli_stmt_store_result($stmt);

        $data = array();

        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
            {
            $data[] = $row;
            }

        $numrows = count($data);
        return array($data, $numrows, null);
        }
    }


For completeness (detail of above referenced function:

function array2typestring($array)
    {
    $types = array();

    foreach ($array as $key =&gt; $value)  
        {
        $types[] = 's';
        }

    $types = implode('', $types);
    return $types;
    }


Example usage:

$db_handler = mysqli_connect("p:$dblocal", $dbuser, $dbpass) or die();
$query = "SELECT title FROM books WHERE id=?";
$params = array($bookid);
list($result, $numrows, $err) = my_dbstmt($db_handler, $query, $params);


Result:

$result holds multi-dimensional array with books' title (just one in this case). $numrows is 1. $err is null.

Catching errors would work as follows:

if($result===false) {print("Error finding book")}
elseif($numrows==0) {print("Book not found")}
elseif($numrows&gt;0) {print("Book found")}
The main thing that needs to be refactored here is the approach itself. Calling a function like this 

list($result, $numrows, $err) = my_mysqli_stmt($db_handler, $query, $params);


is absolutely not the way to go.

Definitely, there should be only one result, especially because this function returns superfluous variables which you don't really need.

$numrows is really useless as any time you need the count, you can count $result itself.

Returning $err is wrong too. You need to understand how a web-site works. If you take a look an any site around, including this one, none of them tells you silly errors like "Error finding a book". If an error occurs, the site just shuts down completely, because a database error is a disaster, and nothing useful will be shown anyway. that's why in case of error only a generalized page with excuses is shown. So in the end, in case of error, your function should return nothing, but actually throw an error to tell PHP that something terrible happened. 

Therefore, the proper usage of such a function would be 

$query = "SELECT title FROM books WHERE id=?";
$result = my_dbstmt($db_handler, $query, [$bookid]);

if(!$result) print("Book not found");
else print("Book found");


This is the most important thing about your function, while everything else is just nitpicks in comparison: 


There is a built-in function str_repeat which is doing exactly the same as your array2typestring.
There is an argument unpacking operator which is doing exactly the same as your referencing magic
there is a mysqli_fetch_all() function which is...  so you get the idea.


using all this knowledge, you can write your function like this

function my_dbstmt($handler, $query, $params = [])
{
    $stmt = mysqli_prepare($handler, $query);
    $types = str_repeat('s', count($params));
    mysqli_stmt_bind_param($stmt, $types, ...$params);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    return mysqli_fetch_all($result);
}


The function above supports only SELECT queries. To make it support other queries is not a good idea, and better be implemented using different methods within the same class - the thing OOP is good for. But as you decided to stick with procedural, it could be done with different result type returned by this function: for SELECT queries it will return an array and for everything else the number of affected rows.

function my_dbstmt($handler, $query, $params = [])
{
    if ($params)
    {
        $stmt = mysqli_prepare($handler, $query);
        $types = str_repeat('s', count($params));
        mysqli_stmt_bind_param($stmt, $types, ...$params);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
    } else {
        $result = mysqli_query($handler, $query);
    }
    if ($result)
    {
        return mysqli_fetch_all($result);
    } else {
        return mysqli_affected_rows($result);
    }
}


Now this function can be used with DML queries as well and even with any other type of query, like CREATE TABLE or SHOW PROCESSLIST.

Now you can ask what about errors. It should be no concern of this function at all. All database errors should be converted to PHP errors and treated exactly the same way. To convert mysqli errors to PHP errors you need only one command - mysqli_report(). So in the end your code should look like

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db_handler = mysqli_connect($dblocal, $dbuser, $dbpass, $dbname)

$query = "SELECT title FROM books WHERE id=?";
$result = my_dbstmt($db_handler, $query, [$bookid]);

if(!$result) echo "Book not found";
echo print $result[0]['title'];


and for the DML query it would be 

$query = "DELETE FROM books WHERE id=?";
$result = my_dbstmt($db_handler, $query, [$bookid]);
echo "$result books has been deleted";


Note that you'd better avoid blunt use of persistent connections as it will give you nothing you imagine but only problems.