[PHP] Named Parameter support for mysqli

[TimeBomb]

Hey all, I created this function a month or two ago to provide named parameter support for mysqli. It works similarly to PDO.

Pass in a query string similar to what you would send to PDO, as well as a key-value array of the parameters, and it will setup the query string and parameter array to correctly work with mysqli.

    /*
     * Provides named parameter support for mysqli. Rewrites a PDO-like query string and parameter array to work with mysqli.
     *
     * @param string $queryStr
     * @param array $params
     */
    function parseNamedParams(&$queryStr, &$params)
    {
        $array = array();
        if ($c = preg_match_all('/(:\w+)/is', $queryStr, $matches)) { // To match words starting with colon
            $list = $matches[0]; // $matches is two-dimensional array, we only need first element
            foreach($list as $value) { // We'll replace each parameter in the query string with a '?' (as to comply with mysqli), and make sure the value is in the correct order.
                $queryStr = str_replace($value, '?', $queryStr);
                $array[] = $params[$value];
            }
            $params = $array;
        }
    } 

It works by putting each array element in the correct order based on the order of the named parameters in the query string.
Example:

$query = 'SELECT * from users WHERE email = :email AND name = :name';
$params = array(':name' => 'John Smith', ':email' => 'email@example.com');
parseNamedParams($query, $params);
// $query now equals: SELECT * from users WHERE email = ? AND name = ?
// $params now equals: array(0 => 'email@example.com', 1 => 'John Smith') 

The function references the passed arguments, meaning that you don't need to worry about the returned value of this function.

The function is designed solely to parse named parameters for use when binding parameters to a mysqli statement.

It will not run a query for you. The function could be modified to handle parameter types(required by mysqli_stmt_bind_param) by keeping the values together with supplied parameter types.

Personally, I dynamically bind the parameters via automatically detecting and setting the type of the parameter, and then using call_user_func_array with mysqli_stmt_bind_param.

How you do it is up to you. If you want to learn more about dynamically binding parameters with mysqli, click here.

G'luck.

P.S. I will probably be moving to PDO soon... after I do some more benchmarking.

[MentaL]