PHP - MySQLi Prepared Statements: Bind Param Problem

[Mko]

Hey all,

I'm currently trying to create a successful MySQLi Prepared Statement. Here's what I have so far:

$stmt = $database->stmt_init();

$query = "UPDATE table SET group = (?), username = (?)";
$types = 'is';
$vars = $vbulletin->GPC['user']['usergroupid'].', '.$vbulletin->GPC['user']['username'].', ';

$query .= " WHERE userid = (?)";
$types .= 'i';
$vars .= $vbulletin->GPC['userid'];

//Debugging
echo 'Query: '.$query.'<br />';
echo 'Types: '.$types.'<br />';
echo 'Types - DATA TYPE: '.gettype($types).'<br />';
echo 'Vars: '.$vars.'<br />';
echo 'Vars - DATA TYPE: '.gettype($vars);

$stmt->prepare($query);
$stmt->bind_param($types, $vars);
$stmt->execute();
$stmt->close();

$database->close();

 

As you can see, I'm trying to append values to the $query, $types, and $vars variables. After doing so, I then use them in the prepared statements.

 

However, when I execute this code, I get this error (also contains debugging echos):

Query: UPDATE table SET group = (?), username = (?) WHERE userid = (?)
Types: isi
Types - DATA TYPE: string
Vars: 2, username, 12345
Vars - DATA TYPE: string
Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in [path]/admincp/user.php(1055) : eval()'d code on line 74

 

That leads me to becoming stuck. I have no idea what is causing this issue, and I am also stumped as to how to fix it :/

 

 

Any help is much appreciated!

 

Thanks,

Mark

[Christian F.]

$vars need to be an array.

[rythemton]

There is a difference between:

"first text, second text"

and:

"first text", "second text"

 

Your are using the first in your prepare statement, but PHP is expecting the second. Your other option is to use an array as ChristianF suggested.

[Mko]

$vars need to be an array.

My new code looks like:

$stmt = $database->stmt_init();

$query = "UPDATE table SET group = (?), username = (?)";
$types = 'is';
$vars = array("$vbulletin->GPC['user']['usergroupid']", "$vbulletin->GPC['user']['username']");

$query .= " WHERE userid = (?)";
$types .= 'i';
array_push($vars, "$vbulletin->GPC['userid']");

//Debugging
echo 'Query: '.$query.'<br />';
echo 'Types: '.$types.'<br />';
echo 'Types - DATA TYPE: '.gettype($types).'<br />';
echo 'Vars: '.$vars.'<br />';
echo 'Vars - DATA TYPE: '.gettype($vars);

$sql_stmt = mysqli_prepare($database, $query);
call_user_func_array('mysqli_stmt_bind_param', array_merge(array($sql_stmt, $types), $vars));
mysqli_stmt_execute($sql_stmt); 

$database->close();

 

Yet, when it runs, it says this error:

Query: UPDATE characters SET mgroup = (?), name = (?) WHERE id = (?)
Types: isi
Types - DATA TYPE: string
Vars: Array
Vars - DATA TYPE: array
Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given in [path]/admincp/user.php(1055) : eval()'d code on line 76

 

Here is like 76:

call_user_func_array('mysqli_stmt_bind_param', array_merge(array($sql_stmt, $types), $vars));

 

 

 

There is a difference between:

"first text, second text"

and:

"first text", "second text"

 

Your are using the first in your prepare statement, but PHP is expecting the second. Your other option is to use an array as ChristianF suggested.

 

So, you would suggest the following code:

$stmt = $database->stmt_init();

$query = "UPDATE table SET group = (?), username = (?)";
$types = 'is';
$vars = "".$vbulletin->GPC['user']['usergroupid']."", "".$vbulletin->GPC['user']['username'].", ";

$query .= " WHERE userid = (?)";
$types .= 'i';
$vars .= "".$vbulletin->GPC['userid']."";

//Debugging
echo 'Query: '.$query.'<br />';
echo 'Types: '.$types.'<br />';
echo 'Types - DATA TYPE: '.gettype($types).'<br />';
echo 'Vars: '.$vars.'<br />';
echo 'Vars - DATA TYPE: '.gettype($vars);

$stmt->prepare($query);
$stmt->bind_param($types, $vars);
$stmt->execute();

$stmt->close();

$database->close();

 

 

 

 

 

 

 

 

 

 

[rythemton]

So, you would suggest the following code:

$stmt = $database->stmt_init();

$query = "UPDATE table SET group = (?), username = (?)";
$types = 'is';
$vars = "".$vbulletin->GPC['user']['usergroupid']."", "".$vbulletin->GPC['user']['username'].", ";

$query .= " WHERE userid = (?)";
$types .= 'i';
$vars .= "".$vbulletin->GPC['userid']."";

//Debugging
echo 'Query: '.$query.'<br />';
echo 'Types: '.$types.'<br />';
echo 'Types - DATA TYPE: '.gettype($types).'<br />';
echo 'Vars: '.$vars.'<br />';
echo 'Vars - DATA TYPE: '.gettype($vars);

$stmt->prepare($query);
$stmt->bind_param($types, $vars);
$stmt->execute();

$stmt->close();

$database->close();

 

No. I'm suggesting the following:

$stmt = $database->stmt_init();

$query = "UPDATE table SET group = (?), username = (?)";
$types = 'is';

$query .= " WHERE userid = (?)";
$types .= 'i';

//Debugging
echo 'Query: '.$query.'<br />';
echo 'Types: '.$types.'<br />';
echo 'Types - DATA TYPE: '.gettype($types).'<br />';

$stmt->prepare($query);
$stmt->bind_param($types, $vbulletin->GPC['user']['usergroupid'], $vbulletin->GPC['user']['username'], $vbulletin->GPC['userid']);
$stmt->execute();

$stmt->close();

$database->close();

 

And now that I've looked at the manual, I don't think mysqli will allow arrays in bind statements.

[Mko]

No. I'm suggesting the following:

$stmt = $database->stmt_init();

$query = "UPDATE table SET group = (?), username = (?)";
$types = 'is';

$query .= " WHERE userid = (?)";
$types .= 'i';

//Debugging
echo 'Query: '.$query.'<br />';
echo 'Types: '.$types.'<br />';
echo 'Types - DATA TYPE: '.gettype($types).'<br />';

$stmt->prepare($query);
$stmt->bind_param($types, $vbulletin->GPC['user']['usergroupid'], $vbulletin->GPC['user']['username'], $vbulletin->GPC['userid']);
$stmt->execute();

$stmt->close();

$database->close();

 

And now that I've looked at the manual, I don't think mysqli will allow arrays in bind statements.

 

Well, the main purpose and intention I had with this code is to be able to bind params from if statements...like if a user changed their password, there would be a 's' added to the params along side a $password variable.

 

I've looked at many places...but I've only found one POTENTIAL solution...http://www.php.net/manual/en/mysqli-stmt.bind-param.php#92283

How does that code look?