[RESOLVED] Mysqli->prepare statement and IN (?)

[frabble]

Hi all,

First post and all that, so hello!
I have been stuck with this issue for hours and would really appreciate some help.
Basically, I am trying to retrieve a list of events made by a users friends. The code detailed below may help explain it a bit better.

//we have our friends in an array
$friends = array('test@test','more@tests','even@more');

//function to get events from our friends
function getEvents( $friends )
{
    global $mysqli; //include mysqli object
    $status = 1; //1 means friend is confirmed
    $q = array_fill(0,count($friends),'?');
    $param = implode(',',$q); //create x ?'s for our prepare statement
    $sql = "SELECT id,email,date,event FROM events WHERE email IN ({$param}) ORDER BY id DESC LIMIT 30";
    if ($stmt = $mysqli->prepare($sql))
    {
        call_user_func_array(array($stmt, 'bind_param'), $friends);
        $stmt->execute();
        $stmt->bind_result($gid,$gEmail,$gDate,$gEvent);
        while($stmt->fetch())
        {
             //loop and display events from our friends
        }
     }
}

But what outputs is

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given in C:\code\php\site\default.php on line 309

To give you more of an idea, Line 309 is

call_user_func_array(array($stmt, 'bind_param'), $friends);

Can someone please help me? Thanks in advance!

Brad

[NogDog]

The values in the $friends array need to be references. You could do something like:

$friendEmails = array('test@test','more@tests','even@more');
$friends = array();
foreach($friendEmails as $key => $value)
{
   $friends[] = &$friendEmails[$key]; // assign as references
}

getEvents($friends);

[frabble]

NogDog;10933599 wrote:

The values in the $friends array need to be references. You could do something like:

$friendEmails = array('test@test','more@tests','even@more');
$friends = array();
foreach($friendEmails as $key => $value)
{
   $friends[] = &$friendEmails[$key]; // assign as references
}

getEvents($friends);

Many thanks for the reply NogDog. I feel I am closer now with your help. The error I am getting now is

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in C:\code\php\site\default.php on line 313

For a bit more information, the result of

var_dump($friends);

is

array(2) { [0]=> &string(18) "user@email" [1]=> &string(19) "user2@email" }

and

echo $param;

gives me

?,?

Thanks again.

Brad

[NogDog]

Looks like it should work. Here's a debug version I just tried:

function getEvents( $friends )
{
    global $mysqli; //include mysqli object
    $status = 1; //1 means friend is confirmed
    $q = array_fill(0,count($friends),'?');
    $param = implode(',',$q); //create x ?'s for our prepare statement
    $sql = "SELECT id,email,date,event FROM events WHERE email IN ({$param}) ORDER BY id DESC LIMIT 30";
    // start debug
    echo "<pre>";
    var_dump($sql);
    var_dump($param);
    var_dump($friends);
    echo "</pre>";
    exit;
    // end debug
    if ($stmt = $mysqli->prepare($sql))
    {
        call_user_func_array(array($stmt, 'bind_param'), $friends);
        $stmt->execute();
        $stmt->bind_result($gid,$gEmail,$gDate,$gEvent);
        while($stmt->fetch())
        {
             //loop and display events from our friends
        }
     }
}

$friendEmails = array('test@test','more@tests','even@more');
$friends = array();
foreach($friendEmails as $key => $value)
{
   $friends[] = &$friendEmails[$key]; // assign as references
}

getEvents($friends);

And the output:

string(87) "SELECT id,email,date,event FROM events WHERE email IN (?,?,?) ORDER BY id DESC LIMIT 30"
string(5) "?,?,?"
array(3) {
  [0]=>
  &string(9) "test@test"
  [1]=>
  &string(10) "more@tests"
  [2]=>
  &string(9) "even@more"
}

[frabble]

NogDog;10933612 wrote:

Looks like it should work. Here's a debug version I just tried:

function getEvents( $friends )
{
    global $mysqli; //include mysqli object
    $status = 1; //1 means friend is confirmed
    $q = array_fill(0,count($friends),'?');
    $param = implode(',',$q); //create x ?'s for our prepare statement
    $sql = "SELECT id,email,date,event FROM events WHERE email IN ({$param}) ORDER BY id DESC LIMIT 30";
    // start debug
    echo "<pre>";
    var_dump($sql);
    var_dump($param);
    var_dump($friends);
    echo "</pre>";
    exit;
    // end debug
    if ($stmt = $mysqli->prepare($sql))
    {
        call_user_func_array(array($stmt, 'bind_param'), $friends);
        $stmt->execute();
        $stmt->bind_result($gid,$gEmail,$gDate,$gEvent);
        while($stmt->fetch())
        {
             //loop and display events from our friends
        }
     }
}

$friendEmails = array('test@test','more@tests','even@more');
$friends = array();
foreach($friendEmails as $key => $value)
{
   $friends[] = &$friendEmails[$key]; // assign as references
}

getEvents($friends);

And the output:

string(87) "SELECT id,email,date,event FROM events WHERE email IN (?,?,?) ORDER BY id DESC LIMIT 30"
string(5) "?,?,?"
array(3) {
  [0]=>
  &string(9) "test@test"
  [1]=>
  &string(10) "more@tests"
  [2]=>
  &string(9) "even@more"
}

Thanks once again NogDog. Perhaps there's a bug with my PHP version (5.3.0) and call_user_func_array() ?
I'll try updating to 5.2.11 and see if that helps.

Brad

[NogDog]

Hmmm...when I've seen this sort of thing before, it was with bind_result(), but I've not seen it done with bind_param(), which requires 2 parameters. Perhaps that's the problem?

[frabble]

This is true. Generally I use bind_param(param_types,param_var).
Though the call_user_func_array() way I'm trying to do seems to work for others.
I'll keep hacking away and let you know my findings.

Cheers,

Brad

[frabble]

Yoho,

Solved! You need to append the param type to the beginning of the $friends array so the output looks like

array(3) { [0]=> string(2) "ss" [1]=> &string(18) "user@email" [2]=> &string(19) "user2@email" }

To accomplish this, my code looked like

$s = str_repeat('s',count($friends));
array_unshift($friends,$s);
getEvents( $friends );

function getEvents( $friends ) 
{ 
    global $mysqli; //include mysqli object 
    $status = 1; //1 means friend is confirmed 
    $q = array_fill(0,count($friends),'?'); 
    $param = implode(',',$q); //create x ?'s for our prepare statement 
    $sql = "SELECT id,email,date,event FROM events WHERE email IN ({$param}) ORDER BY id DESC LIMIT 30"; 
    if ($stmt = $mysqli->prepare($sql)) 
    { 
        call_user_func_array(array($stmt, 'bind_param'), $friends); 
        $stmt->execute(); 
        $stmt->bind_result($gid,$gEmail,$gDate,$gEvent); 
        while($stmt->fetch()) 
        { 
             //loop and display events from our friends 
        } 
     } 
}

Many thanks for your help and interest NogDog.

Cheers,

Brad

[NogDog]

Thanks for the update. I added a link to your solution from my blog post on the same general technique with bind_param().

🙂

[frabble]

No problem - I just hope it saves someone a day of pulling their hair out.