mysqli_stmt_bind_param - expects references

Question

I understand that we have to pass references to mysqli_stmt_bind_param. I am doing the following

    $ref = refValues($data);
    function refValues($arr){
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
            var_dump(implode(",", $refs));
            return $refs;
    return $arr;
}

I am having all of my values in an array. I am using the above function to get the references. Got the above answer from SO

My PHP version is 5.6

I am binding the params in the following way.

mysqli_stmt_bind_param($stmt, $types, $ref);

$stmt is a statement created through mysqli_prepare. It returns error number 0.

$types is nothing but $types = str_pad('',count($data),'s');

I have verified $types data also. It returns expected number of types. i.e ssssssss

If I execute, I am getting the following error.

 Only variables should be passed by reference in test.php

I found this solution in SO. I cannot assign 100 variables. I am not thinking that is feasible.

I found another alternative is call_user_func_arrary.

$values =  refValues($data);
call_user_func_array(array($stmt, 'bind_param'), $values);

It returns number of bind type doesn't match number of values. It is weird for me. I have verified the array and values. Both counts are matching. I am not aware of internal implementation of call_user_func_array.

Please let me know is there any way to solve this efficiently.

Well, first off, `function refValues($arr){` creates a copy of `$data` — Xorifelse, Feb 01 '17 at 06:21
@Xorifelse Please see the updated link. 34 upvotes for the answer. Doesn't it create references? — Gibbs, Feb 01 '17 at 06:24
In your code `$ref` is an array (value) that contains references. — u_mulder, Feb 01 '17 at 06:29
@u_mulder Yeah. I understand that. Thanks. Please let me know when references are already there why that func expecting references? Am I doing anything wrong? — Gibbs, Feb 01 '17 at 06:31

u_mulder · Accepted Answer · 2017-02-01T08:23:18.247

This line

mysqli_stmt_bind_param($stmt, $types, $ref);

means that you have one reference to bind.

Why? Let's see:

first argument is a statement
second argument is a string with types
following arguments are references to values which should be binded.

As you pass one argument (it is $ref) - you are trying to bind only one value. And $ref is not a reference, it is array of values which are refernces. See the difference? Array of references vs reference.

So, you took second approach, and it is a right one:

$values =  refValues($data);
call_user_func_array(array($stmt, 'bind_param'), $values);

What's the error here? You didn't pass types $types:

// do not assign anything to a variable
// pass results of `refValues` directly to `call_user_func_array`
call_user_func_array(array($stmt, 'bind_param'), array_merge(array($types), refValues($data)));

What do we do here: we are trying to call $stmt->bind_param and pass to this function arguments as array.

What are the arguments of $stmt->bind_param?

first argument is types ($types)
following arguments are references to values ($values)

Now it should work.

I am getting ` Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given` Any help? — Gibbs, Feb 01 '17 at 07:01

score 2 · Answer 2 · answered Feb 01 '17 at 08:16

There are two possible ways to avoid this hassle:

Use PDO. Your current problem is only the first out of many WTFs you will have with mysqli. In this particular case it would be as simple and natural as
```
$stmt = $db->prepare($sql);
$stmt->execute($data);
```
Okay, you have such a whim of using mysqli. Then, as long as you are using a supported PHP version, you can use a splat or a three dot operator:
```
$stmt = $db->prepare($sql);
$types = str_repeat('s', count($data));
$statement->bind_param($types, ...$data);
$statement->execute();
```

Thanks. I'll think about upgrade to PDO. – Gibbs Feb 01 '17 at 08:40 — Gibbs, Feb 01 '17 at 08:40

mysqli_stmt_bind_param - expects references

2 Answers2