error using call_user_func_array

[simolahansen]

I have been trying to use call_user_func_array()but dont seem to get it right.

$sql_sentence = "SELECT * FROM test_table WHERE Fag = ? AND (" . $sql_subject . ") AND (" . $sql_grade . ") AND (" . $sql_type . ")";
$sql = $conn->prepare($sql_sentence);

$allarrays = array_merge(array('ssss'),array($_POST['Fag']),$_POST['subject'],$_POST['Grade'],$_POST['Type']);

//testing to see if it prints out right values of array.
for ($x = 0; $x < count($allarrays); $x++) {
echo "The number $x is $allarrays[$x] <br>";
}

call_user_func_array(array($sql, "bind_param"),$allarrays);
$sql->execute();

But I get this error Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, and it points it to line call_user_func....

When I print out the SQL sentence it looks like this: SELECT * FROM test_table WHERE Fag = ? AND (Subject LIKE ?) AND (Grade LIKE ?) AND (Type LIKE ?) and all array-values are right, and the first array value is 'ssss', and after the first array comes four arrays with different values.

Have I done anything wrong here? 

[requinix]

You can't use bind_param like that.

 

Just call it normally.

$sql->bind_param('ssss', $_POST['Fag'], $_POST['subject'], $_POST['Grade'], $_POST['Type']);

[simolahansen]

I use it like this because it is dynamic number of arguments in the bind_param.

 

You can also see it here: http://stackoverflow.com/questions/755036/what-is-a-simple-solution-for-dynamic-mysqli-bind-param-arguments-in-php

 

Cant i do this?

[requinix]

"Dynamic" means you don't know how many variables there are to bind. Your code is not dynamic: there are exactly four variables.

 

Or are you talking about different code that you didn't post?

[simolahansen]

No, because $_Post['Grade'] is an array. So I dont know how many arrays it has.

[requinix]

Then

array('ssss')

that is wrong: you need one 's' for every subject, grade, and type to compare.

 

What's the rest of your code before this point?

[Jacques1]

The call_user_func_array() gymnastics for dynamic parameters are obsolete. PHP 5.6 introduced the splat operator, so you can pass the parameter array directly to bind_param().

$placeholders = '';    // the dynamic placeholders
$params = [];          // the dynamic parameters

$stmt->bind_param($placeholders, ...$params);    // note the "..." syntax

Your current query makes no sense, though. You cannot apply the LIKE operator to multiple values. Why do you even use LIKE all the time? There aren't any search patterns in your code, just plain strings. I guess what you actually want is the IN operator:

SELECT
    *  -- select *specific* columns, not just everything
FROM
   test_table
WHERE
   fag = ?
   AND subject = ?
   AND grade IN (?, ?, ?, ...)    -- add one placeholder for each grade
   AND type = ?

[simolahansen]

 

The call_user_func_array() gymnastics for dynamic parameters are obsolete. PHP 5.6 introduced the splat operator, so you can pass the parameter array directly to bind_param().

$placeholders = '';    // the dynamic placeholders
$params = [];          // the dynamic parameters

$stmt->bind_param($placeholders, ...$params);    // note the "..." syntax

Your current query makes no sense, though. You cannot apply the LIKE operator to multiple values. Why do you even use LIKE all the time? There aren't any search patterns in your code, just plain strings. I guess what you actually want is the IN operator:

SELECT
    *  -- select *specific* columns, not just everything
FROM
   test_table
WHERE
   fag = ?
   AND subject = ?
   AND grade IN (?, ?, ?, ...)    -- add one placeholder for each grade
   AND type = ?

But there is many instructions out there which says you could write it like I have. Here is one: http://stackoverflow.com/questions/17870999/bind-multiple-parameters-into-mysqli-query

Who is right?

[simolahansen]

Then

array('ssss')

that is wrong: you need one 's' for every subject, grade, and type to compare.

 

What's the rest of your code before this point?

 

Well, I have made a function that counts number of arrays and repeats number of 's'. So that I get the right number of 'ssss'.

[requinix]

But there is many instructions out there which says you could write it like I have. Here is one: http://stackoverflow.com/questions/17870999/bind-multiple-parameters-into-mysqli-query

Who is right?

The ... operator has better performance for passing multiple arguments than call_user_func_array, but that is not the main issue with the code you have. The SO link you have there demonstrates a key principle that your script needs to have for it to work correctly:

$bind[$key] = &$values[$key];

references. You need to construct an array of all the arguments to bind using references - you cannot simply merge arrays together. Then you can use ... on the array when you call bind_param.

 

Well, I have made a function that counts number of arrays and repeats number of 's'. So that I get the right number of 'ssss'.

The function is only good if you actually use it, and I don't see you doing that in the code you've shown so far. Which is to say nothing about how using a function for that is silly.

 

So are you saying the code you posted is not the code you're using?

[Jacques1]

For clarification: The splat operator doesn't need explicit references. So you can in fact have a simple array_merge().

 

But there is many instructions out there which says you could write it like I have.

 

You can use call_user_func(). But this requires much more code, is much more complex and exceeds your current level of understanding (as well as your willingness to actually study the example code). Why on earth would you take that approach?

[requinix]

For clarification: The splat operator doesn't need explicit references. So you can in fact have a simple array_merge().

Actually no: the operator supports unpacking references but does not create them automatically, so if the function expects references then the source array must contain references.

 

https://3v4l.org/aUNRG

 

An array_merge will work if the passed arrays contain references,

array_merge([&$ref1], [&$ref2]) // -> [&$ref1, &$ref2]

but since $_POST doesn't have them there needs to be some code written to create them.

[Jacques1]

No. The operator automatically passes the arguments by-value or by-reference according to the function definition. And that's all you need to make bind_param() work:

// a simple array of values; no references here
$args = ['foo', 'bar'];

$databaseConnection = mysqli_connect($host, $user, $password, $database);

$stmt = $databaseConnection->prepare('SELECT ?, ?');
$stmt->bind_param('ss', ...$args);
$stmt->execute();

$stmt->bind_result($res_1, $res_2);
$stmt->fetch();

var_dump($res_1, $res_2);    // foo, bar

I've used this in practice.

[simolahansen]

No. The operator automatically passes the arguments by-value or by-reference according to the function definition. And that's all you need to make bind_param() work:

// a simple array of values; no references here
$args = ['foo', 'bar'];

$databaseConnection = mysqli_connect($host, $user, $password, $database);

$stmt = $databaseConnection->prepare('SELECT ?, ?');
$stmt->bind_param('ss', ...$args);
$stmt->execute();

$stmt->bind_result($res_1, $res_2);
$stmt->fetch();

var_dump($res_1, $res_2);    // foo, bar

I've used this in practice.

 

Thank you. This seems to work, but  I would really like to use LIKE operator, and now that seems like the problem. 

 

When I print my SQL-sentence I can get this:

SELECT * FROM test_table WHERE Fag = ? AND (Subject = ?) AND (Grade = ? OR Grade = ?) AND (Type = ? OR Type = ?)

And with this I get no error. But trying to add LIKE %?% I get an error.  

 

So when my SQL-sentence is lie this:

SELECT * FROM test_opplegg WHERE Fag = ? AND (Subject LIKE %?%) AND (Grade LIKE %?% OR Grade LIKE %?%) AND (Type LIKE %?% OR Type LIKE %?%)

I get the error:

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean

 

The reason I want this is that Grade[3] can be "2,3,7,". So by using %-operator I cant find where the post is %2,% OR %7,%

[simolahansen]

I found it out. The wilcards must be inside the value itself or the array in this case!

 

Thank you!

[requinix]

No. The operator automatically passes the arguments by-value or by-reference according to the function definition...

Ah. We're both right because we're talking about two different things.

 

I'm saying that it does not create references on the individual elements within. And it does not:

function individual_ref(&$a) { $a++; }
$a = 1;
individual_ref(...[$a]);
echo $a; // 1

You're saying that it does allow references on the array itself. And it does:

function vararg_ref(&...$args) { $args[0]++; }
$args = [1];
vararg_ref(...$args);
echo $args[0]; // 2

bind_param is the second case with the argument being a vararg, so as long as the expanded array is a variable then it works (your version). It does not work if the expanded array is not a variable, regardless of whether its contents are variables or not (my version).

 

("work" meaning it's the proper use of references with no warnings, not that the code successfully executes the way it's expected to)

 

Important distinction about the former point: the reference is to the array, not to its contents:

function vararg_ref(&...$args) { $args[0]++; }
$a = 1;
$args = [$a];
vararg_ref(...$args);
echo $a; // 1
echo $args[0]; // 2

That means

$foo = 'foo';
$bar = 'bar';

// a simple array of variables
$args = [$foo, $bar];

$databaseConnection = mysqli_connect($host, $user, $password, $database);

$stmt = $databaseConnection->prepare('SELECT ?, ?');
$stmt->bind_param('ss', ...$args);
$stmt->execute();

$stmt->bind_result($res_1, $res_2);
$stmt->fetch();

var_dump($res_1, $res_2);    // foo, bar

// update source variables and try again

$foo = 'FOO';
$bar = 'BAR';
$stmt->execute();
$stmt->fetch();

var_dump($res_1, $res_2);    // foo, bar

// however,

$args[0] = $foo;
$args[1] = $bar;
$stmt->execute();
$stmt->fetch();

var_dump($res_1, $res_2);    // FOO, BAR

And one more comment:

function vararg_ref(&...$args) { $args[0]++; }
$a = 1;
vararg_ref(...[$a]);
echo $a; // 1

Given that $args is by-ref, you'd expect the code to warn because [$a] is not reference-able. It does not. Sounds like a bug but it could have been a deliberate decision.