How to bind mysqli bind_param arguments dynamically in PHP?

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?

Solution 1

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!

Solution 2

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>';
}

Solution 3

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));

Solution 4

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.

View more solutions

Comments

[Julian F. Weinert]

The problem is that PDO is much slower than mysqli. And even slower than the older mysql.

[Elias Van Ootegem]

@Julian: To say PDO is much slower is a bit much. The difference in performance is too small to be likely to be the main bottleneck (if noticeable at all)

[Julian F. Weinert]

Thanks for correcting. I read a benchmark where the difference was more significant. And my own tests had more remarkable results.

[r3wt]

@Julian it's been slow as flippin christmas everytime i used it. mysqli is so much faster i can't justify the convenience. but i'm leaning towards it.

[Gabriel Alack]

Null values in your params array will break your dynamic binding call FYI

[Minding]

The variable $bind_name is acually unused and $$bind_name makes the code confusing, while $$ does not have any special meaning in PHP. The same goes for $var and $$var.

[I try so hard but I cry harder]

NOTE: From the PHP manual: "Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array(). Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values." Source: php.net/manual/en/mysqli-stmt.bind-param.php

[Your Common Sense]

this approach has been relevant ten years ago. You may want to notice the existing answer that takes ten times less code and requires no additional functions at all. It's generally quite a good idea to learn from the answers that already exist.

[Jeff Solomon]

Awesome, thanks for that; so friendly of you. I'm just a beginner, give me a break.

[Your Common Sense]

Sorry for bothering you again, but I just noticed a problem in your code. binding a single parameter for the IN operator won't give you any good results, effectively using only the first value from the comma-separated string. In your case, WHERE status IN ("1,2,3,4") is actually WHERE status = 1. You need to split that string and use separate values, generating the equal number of placeholders dynamically, as shown here

[Jeff Solomon]

@YourCommonSense ok, now that was very helpful, thank you. I just noticed that the query wasn't returning the desired results and wasn't sure why. I guess I spend some time trying some of the other mentioned solutions, although I couldn't fully make sense of them as they aren't fully articulated. Will take me some hacking through it to figure out. But thanks, I appreciate you actually reading through my work.

[Your Common Sense]

Here is your function rewritten doing the correct binding for the array. It is using a simple helper function to run mysqli queries I wrote

[Jeff Solomon]

Oh man, that's a lot cleaner. Thanks for helping me out; I'm sure you have better things to do so I really appreciate it.