MySQLi Prepared statements - Wrapper function for SELECT

Question

I recently started using MySQLi prepared statements. I did not liked how many rows of code that was needed for just a simple select statement. So I created a wrapper function, see the code below the questions below. Note: get_results() or PDO is not an option for me.

My questions are:

Will it slow down the performance noticeably?
Will it be more memory intensive because of the use of the result array?
Will the $stmt->close() before the return cause any problems? For example maybe the result array data is also are freed from memory?
Do I need to close or free anything else (except from closing the db connection)?
Do you see any other problems with the function or could it be improved?

Code:

class DatabaseHelper{

static function select($con, $query, $formats, $params){
    $a_params = array();

    $param_type = '';
    $n = count($formats);
    for($i = 0; $i < $n; $i++) {
        $param_type .= $formats[$i];
    }

    $a_params[] = & $param_type;

    for($i = 0; $i < $n; $i++) {
        $a_params[] = & $params[$i];
    }

    $stmt = $con->prepare($query);
    call_user_func_array(array($stmt, 'bind_param'), $a_params);
    $stmt->execute();

    $meta = $stmt->result_metadata();
    while ($field = $meta->fetch_field()) {
        $columns[] = &$row[$field->name];
    }

    call_user_func_array(array($stmt, 'bind_result'), $columns);

    while ($stmt->fetch()) {
        foreach($row as $key => $val) {
            $x[$key] = $val;
        }
        $results[] = $x;
    }
    $stmt->close();
    return $results;
}

}

Used like this for example:

$users = DatabaseHelper::select($conn, "SELECT name,username FROM users WHERE id > ?", "i", array(30));
foreach ($users as $row){
    echo $row['username'] . " ". $row['name'] . "<br />";
}

Your Common Sense Your Common Sense · Accepted Answer · 2016-12-22T11:06:56

Will it slow down the performance noticeably?

No.

Will it be more memory intensive because of the use of the result array?

No, as long as you are selecting sensible amounts of data. In a modern application you have to select all the data first anyway, as the business logic should be separated from display logic.

Will the $stmt->close() before the return cause any problems? For example maybe the result array data is also are freed from memory?

Why not to try and see?

Do I need to close or free anything else (except from closing the db connection)?

You rather don't have to close a statement either.

Do you see any other problems with the function or could it be improved?

First and foremost. As it's a class you are writing, and not a function, there is absolutely no point in passing the connection through a parameter. Make it a static property.
Also, I would suggest to make types the last parameter with default value. In most cases you don't have to nitpick with types - a default string will do.
Besides, as your PHP version is 5.6 you can use the splat operator just to reduce the amount of code. You can check this answer of mine for the details
I would also suggest to split your function into several methods - one to execute the query and others to get the results. It will let you to re-use the same code for all kinds of queries
make sure you are watching for mysqli errors as explained here

So, ideally you'd call your query this way

$users = DatabaseHelper::getAll("SELECT name,username FROM users WHERE id > ?", [30]);
foreach ($users as $row){
    echo $row['username'] . " ". $row['name'] . "<br />";
}

where getAll() method is using query() method internally to perform a query and then to fetch all the results. Similarly you may want to write getRow() and getOne() methods

MySQLi Prepared statements - Wrapper function for SELECT

1 Answers