How to bind mysqli bind_param arguments dynamicall

Question

I have been learning to use prepared and bound statements for my sql queries, and I have come out with this so far, it works ok but it is not dynamic at all when comes to multiple parameters or when there no parameter needed,

public function get_result($sql,$parameter)
    {
        # create a prepared statement
    $stmt = $this->mysqli->prepare($sql);

        # bind parameters for markers
    # but this is not dynamic enough...
        $stmt->bind_param("s", $parameter);

        # execute query 
        $stmt->execute();

    # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
        $meta = $stmt->result_metadata(); 

        while ($field = $meta->fetch_field()) { 
            $var = $field->name; 
            $$var = null; 
            $parameters[$field->name] = &$$var; 
        }

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

        while($stmt->fetch()) 
        { 
            return $parameters;
            //print_r($parameters);      
        }


        # close statement
        $stmt->close();
    }

This is how I call the object classes,

$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);

Sometimes I don't need to pass in any parameters,

$sql = "
SELECT *
FROM root_contacts_cfm
";

print_r($output->get_result($sql));

Sometimes I need only one parameters,

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql,'1'));

Sometimes I need only more than one parameters,

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql,'1','Tk'));

So, I believe that this line is not dynamic enough for the dynamic tasks above,

$stmt->bind_param("s", $parameter);

To build a bind_param dynamically, I have found this on other posts online.

call_user_func_array(array(&$stmt, 'bind_params'), $array_of_params);

And I tried to modify some code from php.net but I am getting nowhere,

if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+ 
    { 
        $refs = array(); 
        foreach($arr as $key => $value) 
            $array_of_param[$key] = &$arr[$key]; 

       call_user_func_array(array(&$stmt, 'bind_params'), $array_of_params);

     }

Why? Any ideas how I can make it work?

Or maybe there are better solutions?

Answer 1

I found a nice mysqli class, it can handle dynamic parameters, and easy to use

https://github.com/ajillion/PHP-MySQLi-Database-Class

You could refer the source code how it dynamic build the query

https://github.com/ajillion/PHP-MySQLi-Database-Class/blob/master/MysqliDb.php

Answer 2

With PHP 5.6 or higher:

$stmt->bind_param(str_repeat("s", count($data)), ...$data);

With PHP 5.5 or lower you might (and I did) expect the following to work:

call_user_func_array(
    array($stmt, "bind_param"),
    array_merge(array(str_repeat("s", count($data))), $data));

...but mysqli_stmt::bind_param expects its parameters to be references whereas this passes a list of values.

You can work around this (although it's an ugly workaround) by first creating an array of references to the original array.

$references_to_data = array();
foreach ($data as &$reference) { $references_to_data[] = &$reference; }
unset($reference);
call_user_func_array(
    array($stmt, "bind_param"),
    array_merge(array(str_repeat("s", count($data))), $references_to_data));

Answer 3

Or maybe there are better solutions??

This answer doesn't really help you much, but you should seriously consider switching to PDO from mysqli.

The main reason for this is because PDO does what you're trying to do in mysqli with built-in functions. In addition to having manual param binding, the execute method can take an array of arguments instead.

PDO is easy to extend, and adding convenience methods to fetch-everything-and-return instead of doing the prepare-execute dance is very easy.

Answer 4

found the answer for mysqli:

public function get_result($sql,$types = null,$params = null)
    {
        # create a prepared statement
        $stmt = $this->mysqli->prepare($sql);

        # bind parameters for markers
        # but this is not dynamic enough...
        //$stmt->bind_param("s", $parameter);

        if($types&&$params)
        {
            $bind_names[] = $types;
            for ($i=0; $i<count($params);$i++) 
            {
                $bind_name = 'bind' . $i;
                $$bind_name = $params[$i];
                $bind_names[] = &$$bind_name;
            }
            $return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
        }

        # execute query 
        $stmt->execute();

        # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
        $meta = $stmt->result_metadata(); 

        while ($field = $meta->fetch_field()) { 
            $var = $field->name; 
            $$var = null; 
            $parameters[$field->name] = &$$var; 
        }

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

        while($stmt->fetch()) 
        { 
            return $parameters;
            //print_r($parameters);      
        }


        # the commented lines below will return values but not arrays
        # bind result variables
        //$stmt->bind_result($id); 

        # fetch value
        //$stmt->fetch(); 

        # return the value
        //return $id; 

        # close statement
        $stmt->close();
    }

then:

$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);

$sql = "
SELECT *
FROM root_contacts_cfm
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql));

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql,'s',array('1')));

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql, 'ss',array('1','Tk')));

mysqli is so lame when comes to this. I think I should be migrating to PDO!

Answer 5

I solved it by applying a system similar to that of the PDO. The SQL placeholders are strings that start with the double-point character. Ex .:

:id, :name, or :last_name

Then you can specify the data type directly inside the placeholder string by adding the specification letters immediately after the double-point and appending an underline character before the mnemonic variable. Ex .:

:i_id (i=integer), :s_name or :s_last_name (s=string)

If no type character is added, then the function will determine the type of the data by analyzing the php variable holding the data. Ex .:

$id = 1 // interpreted as an integer
$name = "John" // interpreted as a string

The function returns an array of types and an array of values with which you can execute the php function mysqli_stmt_bind_param() in a loop.

$sql = 'SELECT * FROM table WHERE code = :code AND (type = :i_type OR color = ":s_color")';
$data = array(':code' => 1, ':i_type' => 12, ':s_color' => 'blue');

$pattern = '|(:[a-zA-Z0-9_\-]+)|';
if (preg_match_all($pattern, $sql, $matches)) {
    $arr = $matches[1];
    foreach ($arr as $word) {
        if (strlen($word) > 2 && $word[2] == '_') {
            $bindType[] = $word[1];
        } else {
            switch (gettype($data[$word])) {
                case 'NULL':
                case 'string':
                    $bindType[] = 's';
                    break;
                case 'boolean':
                case 'integer':
                    $bindType[] = 'i';
                    break;
                case 'double':
                    $bindType[] = 'd';
                    break;
                case 'blob':
                    $bindType[] = 'b';
                    break;
                default:
                    $bindType[] = 's';
                    break;
            }
        }    
        $bindValue[] = $data[$word];
    }    
    $sql = preg_replace($pattern, '?', $sql);
}

echo $sql.'<br>';
print_r($bindType);
echo '<br>';
print_r($bindValue);

Answer 6

Using PHP 5.6 you can do this easy with help of unpacking operator(...$var) and use get_result() insted of bind_result()

public function get_result($sql,$types = null,$params = null) {
    $stmt = $this->mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);

    if(!$stmt->execute()) return false;
    return $stmt->get_result();

}

Example:

$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);


$sql = "SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id = ?
        AND root_contacts_cfm.cnt_firstname = ?
        ORDER BY cnt_id DESC";

$res = $output->get_result($sql, 'ss',array('1','Tk'));
while($row = res->fetch_assoc()){
   echo $row['fieldName'] .'<br>';
}