16

Please see below my code. I am attempting to bind an array of paramenters to my prepared statement. I've been looking around on the web and can see I have to use call_user_func_array but cannot get it to work. The error I get is: "First argument is expected to be a valid callback, 'Array' was given" I may be wrong but I'm assuming the first argument can be an an array and perhaps this error message is misleading. I think the issue is that my array is in someway at fault. Can anyone see what I am doing wrong? Thanks.

$type = array("s", "s");
$param = array("string1","anotherstring");

$stmt = $SQLConnection->prepare("INSERT INTO mytable (comp, addl) VALUES (?,?)");

$params = array_merge($type, $param);

call_user_func_array(array(&$stmt, 'bind_param'), $params);
$SQLConnection->execute();
Columbo
  • 2,896
  • 7
  • 44
  • 54

7 Answers7

20

It must be like this:

//connect
$mysqli = new mysqli($host, $user, $password, $db_name);

//prepare
$stmt = $mysqli->prepare("SELECT * FROM the_table WHERE field1= ? AND Field2= ?");

//Binding parameters. Types: s = string, i = integer, d = double,  b = blob
$params= array("ss","string_1","string_2");

//now we need to add references
$tmp = array();
foreach($params as $key => $value) $tmp[$key] = &$params[$key];
// now us the new array
call_user_func_array(array($stmt, 'bind_param'), $tmp);

$stmt->execute();

/* Fetch result to array */
$res = $stmt->get_result();
while($row = $res->fetch_array(MYSQLI_ASSOC)) {
  $a_data[]=$row;
}
print_r($a_data);

$stmt->close();
abd.agha
  • 209
  • 2
  • 2
14

Since PHP 5.6, you don't have to mess around with call_user_func_array() anymore.

Instead of:

$stmt->bind_param($param_types, $my_params_array);

you can just use the splat operator, like this:

$stmt->bind_param($param_types, ...$my_params_array); // exact code

ban-geoengineering
  • 18,324
  • 27
  • 171
  • 253
  • The first method is better. This one sucks because you have to maintain two arrays – TheRealChx101 Dec 15 '21 at 21:08
  • You can build a single array of params for `bind_param()`; the trick is to concatenate the types string in element `[0]` instead of pushing a new element onto the array. https://stackoverflow.com/a/51036322/2943403 – mickmackusa Apr 03 '22 at 20:46
9

I wouldn't know why you have to use call_user_func_array, but that's another story.

The only thing that could be wrong in my eyes is that you are using a reference to the object. Assuming you're using PHP 5.*, that is not necessary:

call_user_func_array(array($stmt, 'bind_param'), $params);
Franz
  • 11,353
  • 8
  • 48
  • 70
  • Thanks Franz, I'm using the call_user_func_array because the number of parameters to bind varies. I am using PHP5 but removing the reference staill produces the same result. – Columbo Dec 16 '09 at 11:14
  • Then that must mean something is wrong with your object. See this PHP "bug" report: http://bugs.php.net/bug.php?id=46229. What does `var_dump($stmt)` give you at that place? – Franz Dec 16 '09 at 11:19
  • Yes, I'm just getting bool(true) rather than the statement.Not sure why going to have a look. I suppose it's giving me a TRUe to say the statement preperation was a success. But I've seen people doing what I'm doing above in several examples. – Columbo Dec 16 '09 at 11:28
  • You were right it was an object issue. I used stmt_init when I didn't need to to create $SQLConnection. I was still expecting an object out of that but removing it did the trick. Thanks. – Columbo Dec 16 '09 at 12:03
  • 1
    I noticed that your value types are single items in an array...I'm assuming you are using mysqli_stmt::bind_param(), which takes its first argument as a string, so $type = array("s", "s") needs to be $type = "ss" as the string is parsed by each character. See the manual. On another note, I believe bind_param and bind_result still both require the value list to be passed by reference, but call_user_func_array() can no longer pass by ref as that would be a call-time pass_by_reference Fatal error, so this actually broke dynamic reference passing to the method in mysqli. – user253780 Apr 24 '16 at 23:11
1

If you get an error, you should try this:

call_user_func_array(array($stmt, 'bind_param'), refValues($params));

function refValues($arr){
    if (strnatcmp(phpversion(),'5.3') >= 0) {
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}
paulalexandru
  • 9,218
  • 7
  • 66
  • 94
1

Wasn't able to answer this on my own question because it got marked as dupe: here. But I think my final solution, which uses the answers in this question, works in my use case, might be helpful for someone.

My goals was to take a posted set of ID's and use them in a NOT IN MYSQL statement. Assuming array of 5 ID's posted.

  1. Count the number posted ID's to build the ? placeholders for NOT IN statement. Using $params_count = substr(str_repeat(',?', count($array_of_ids)), 1); gives the result: (?,?,?,?,?) to be used in SQL statement.

  2. Make function that takes ID's and type i or s etc. For me, they were all i so my function is simpler. return array that looks like this $params= array("iiiii",1,2,3,4,5) where the first value is the set of i's and the subsequent values are the ID's depending on total ID's passed into function.

    function build_bind_params($values, $bind_type) {
    $s = substr(str_repeat($bind_type, count($values)), 0);
    $bind_array = array();
    $bind_array[] = $s;
    foreach($values as $value) {
        $bind_array[] = $value;
    }
    return $bind_array; 
}

$params = build_bind_params($array_of_ids, "i");

  1. Then use foreach ($params as $key => $value) $tmp[$key] = &$params[$key]; to get the newly created $params formatted properly for binding.

  2. Then use call_user_func_array(array($stmt , 'bind_param') , $tmp); to properly bind the array.

  3. Then execute the $stmt

Jeff Solomon
  • 459
  • 6
  • 21
  • What is `substr([string], 0)` meant to achieve? You are trying to isolate the substring from the zero position to the end of the string; so the substring = the full string. Please edit so that others don't copy-paste. – mickmackusa Sep 25 '18 at 01:01
  • @mickmackusa Comparing point 1. with the line in question, I think the `0` should be `1`. – Teepeemm Jul 02 '19 at 15:26
1

Most of the above are not solutions without integrating the types along with the values before adding them to call_user_func_array(). This solution worked for me:

/* create a database connection */
$db = new mysqli($host, $user, $password, $db_name);

/* setup the sql, values, and types */
$sql="SELECT * FROM languages 
         WHERE language_code = ?
           AND charset = ?
         ORDER BY native_name";
$values = array($langCode, $charset);
$types = "ss";   

/* pass those variables to the execute() function defined below */
if ($rows = execute($sql, $values, $types))
{
   return $rows[0];
}

function execute($sql, $values='', $types='')
{
   /* prepare the sql before binding values and types */
   $stmt = $db->prepare($sql);

   /*combine the values and types into $inputArray */
   $inputArray[] = &$types;
   $j = count($values);
   for($i=0;$i<$j;$i++){
     $inputArray[] = &$values[$i];
   }
   /* add the combined values and types to call_user_func_array() for binding */
   call_user_func_array(array($stmt, 'bind_param'), $inputArray);
   $result = $stmt->execute();
   return $result;
}

Here's a reference to the full description this example is based on: http://big.info/2015/08/php-use-call_user_func_array-for-variable-number-of-parameters-arrays-in-prepared-statements.html

Greg Gay
  • 38
  • 3
0

Why would you want to call call_user_func_array(array($statement, 'bind_param'), $bind_arguments)? Because $bind_arguments is an array. You get to have one function that binds a statement to its queried parameters, no matter how many parameters you'd have otherwise.

Example of good code...

    <?php
            # link
        $dblink = new mysqli('HOSTNAME','USERNAME','PASSWORD','DATABASENAME');

            # example data
        $statement = $dblink->prepare("SELECT * from Person WHERE FirstName = ? AND MiddleName = ? AND LastName = ? and Age = ?");
        $recordvalues = ['John', 'H.', 'Smith', 25];
        $sqlbindstring = "sssi";    # String, String, String, Integer example

            # make the references
        $bind_arguments = [];
        $bind_arguments[] = $sqlbindstring;
        foreach ($recordvalues as $recordkey => $recordvalue)
        {
            $bind_arguments[] = & $recordvalues[$recordkey];    # bind to array ref, not to the temporary $recordvalue
        }

            # query the db
        call_user_func_array(array($statement, 'bind_param'), $bind_arguments);     # bind arguments
        $statement->execute();  # run statement
        $result = $statement->get_result(); # get results

            # get the results
        if($result) {
            while ($row = $result->fetch_assoc()) {
                print("\n\nMy row is...");
                print_r($row);
            }
        }
    ?>

Example of bad code...

    <?php

            # Same setup as above..

        $statement->prepare("SELECT * from Person WHERE FirstName = ? AND MiddleName = ? AND LastName = ? and Age = ?");
        $statement->bind('John', 'H.", 'Smith', 25);

    ?>

In the first example: You can pass as much or as little to the binding to be done, so that bind() might be called in only one line in your entire application. This scales well.

In the second example: You must write one bind() statement for every possible group of insertions for every possible record in your database. This scales poorly.

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133