FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » mysqli_stmt_bind_param with multiple parameters in an array
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
mysqli_stmt_bind_param with multiple parameters in an array [message #180739] Fri, 15 March 2013 20:47 Go to next message
burrowingowl is currently offline  burrowingowl
Messages: 3
Registered: March 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Goal: Insert a row into a mysql table using mysqli and bound parameters.

$stmt = mysqli_prepare($dbhandle, "INSERT INTO work (".$SQL_fields.") VALUES
($SQL_params_text)");

This results in the following query, for example:
INSERT INTO work (abstract,announcement,apparatus_description,author_1_first_name,author_1_f ollowing,author_1_last_name,author_1_middle,author_1_preceding,author_1_rol e,author_2_first_name) VALUES(?,?,?,?,?,?,?,?,?,?)

This list has been truncated for testing purposes. In practice it will be over 100 fields, heavy on text.

$SQL_params_text is ?,?,?, with the number ? matching the number of fields.
$sqltypes is a string like 'ssssssssss'
The array $SQL_values holds the data.

I have tried
mysqli_stmt_bind_param($stmt,$sqltypes,$SQL_values);

and
$msbp = array( $stmt, $sqltypes, $SQL_values);
call_user_func_array( 'mysqli_stmt_bind_param', $msbp);

I have also tried the above while wrapping $SQL_values in
function fn_refValues($arr){
$refs = array();
foreach($arr as $key => $value){
$refs[$key] = &$arr[$key];
}
return $refs;
}

Without the wrapper I get:
> PHP Warning: mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables in ...

This is not true. I've checked it several ways. It just doesn't like $SQL_values as a plain old array. Maybe it's counting the whole array as 1, vs. 10 items in the type definition string.

And with the wrapper to make the values into references I get:
> PHP Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given in ...

Parameter three is fn_refValues($SQL_values) in this case.

Simple fix, or am I just going about this the wrong way?

Thanks.
Re: mysqli_stmt_bind_param with multiple parameters in an array [message #180740 is a reply to message #180739] Fri, 15 March 2013 22:45 Go to previous messageGo to next message
Jerry Stuckle is currently offline  Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 3/15/2013 8:47 PM, burrowingowl(at)gmail(dot)com wrote:
> Goal: Insert a row into a mysql table using mysqli and bound parameters.
>
> $stmt = mysqli_prepare($dbhandle, "INSERT INTO work (".$SQL_fields.") VALUES
> ($SQL_params_text)");
>
> This results in the following query, for example:
> INSERT INTO work (abstract,announcement,apparatus_description,author_1_first_name,author_1_f ollowing,author_1_last_name,author_1_middle,author_1_preceding,author_1_rol e,author_2_first_name) VALUES(?,?,?,?,?,?,?,?,?,?)
>
> This list has been truncated for testing purposes. In practice it will be over 100 fields, heavy on text.
>
> $SQL_params_text is ?,?,?, with the number ? matching the number of fields.
> $sqltypes is a string like 'ssssssssss'
> The array $SQL_values holds the data.
>
> I have tried
> mysqli_stmt_bind_param($stmt,$sqltypes,$SQL_values);
>
> and
> $msbp = array( $stmt, $sqltypes, $SQL_values);
> call_user_func_array( 'mysqli_stmt_bind_param', $msbp);
>
> I have also tried the above while wrapping $SQL_values in
> function fn_refValues($arr){
> $refs = array();
> foreach($arr as $key => $value){
> $refs[$key] = &$arr[$key];
> }
> return $refs;
> }
>
> Without the wrapper I get:
>> PHP Warning: mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables in ...
>
> This is not true. I've checked it several ways. It just doesn't like $SQL_values as a plain old array. Maybe it's counting the whole array as 1, vs. 10 items in the type definition string.
>
> And with the wrapper to make the values into references I get:
>> PHP Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given in ...
>
> Parameter three is fn_refValues($SQL_values) in this case.
>
> Simple fix, or am I just going about this the wrong way?
>
> Thanks.
>

You can't pass the values as an array. You need to pass them as
individual items.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
Re: mysqli_stmt_bind_param with multiple parameters in an array [message #180742 is a reply to message #180739] Sat, 16 March 2013 07:39 Go to previous messageGo to next message
M. Strobel is currently offline  M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Am 16.03.2013 01:47, schrieb burrowingowl(at)gmail(dot)com:
> Goal: Insert a row into a mysql table using mysqli and bound parameters.
>
> $stmt = mysqli_prepare($dbhandle, "INSERT INTO work (".$SQL_fields.") VALUES
> ($SQL_params_text)");
>
> This results in the following query, for example:
> INSERT INTO work (abstract,announcement,apparatus_description,author_1_first_name,author_1_f ollowing,author_1_last_name,author_1_middle,author_1_preceding,author_1_rol e,author_2_first_name) VALUES(?,?,?,?,?,?,?,?,?,?)
>
> This list has been truncated for testing purposes. In practice it will be over 100 fields, heavy on text.
>
> $SQL_params_text is ?,?,?, with the number ? matching the number of fields.
> $sqltypes is a string like 'ssssssssss'
> The array $SQL_values holds the data.
>
> I have tried
> mysqli_stmt_bind_param($stmt,$sqltypes,$SQL_values);
>
> and
> $msbp = array( $stmt, $sqltypes, $SQL_values);
> call_user_func_array( 'mysqli_stmt_bind_param', $msbp);
>
> I have also tried the above while wrapping $SQL_values in
> function fn_refValues($arr){
> $refs = array();
> foreach($arr as $key => $value){
> $refs[$key] = &$arr[$key];
> }
> return $refs;
> }
>
> Without the wrapper I get:
>> PHP Warning: mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables in ...
>
> This is not true. I've checked it several ways. It just doesn't like $SQL_values as a plain old array. Maybe it's counting the whole array as 1, vs. 10 items in the type definition string.
>
> And with the wrapper to make the values into references I get:
>> PHP Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given in ...
>

Look up working examples of mysqli_stmt_bind_param() on the net. The & operator is
of limited use in modern PHP, use only if your code does not work without it.

And you should ask yourself how you came to believe you could put an array where
individual variables are needed.

/Str.
Re: mysqli_stmt_bind_param with multiple parameters in an array [message #180746 is a reply to message #180739] Sat, 16 March 2013 11:46 Go to previous messageGo to next message
burrowingowl is currently offline  burrowingowl
Messages: 3
Registered: March 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Thanks, guys.

So I definitely cannot drop an array in there.

Yet I don't want to create 100+ variables for this INSERT.

Not possible with bound parameters?
Re: mysqli_stmt_bind_param with multiple parameters in an array [message #180747 is a reply to message #180746] Sat, 16 March 2013 11:58 Go to previous messageGo to next message
Jerry Stuckle is currently offline  Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 3/16/2013 11:46 AM, Chris Cosner wrote:
> Thanks, guys.
>
> So I definitely cannot drop an array in there.
>
> Yet I don't want to create 100+ variables for this INSERT.
>
> Not possible with bound parameters?
>

If you're gonna use the language, you gotta follow the rules. If you
don't use bound parameters, ensure you validate EVERY parameter being
passed, and escape ALL STRINGS.

But I don't see why you have so many parameters. It sounds like you
have a serious database design problem. This is also indicated by your
column names.

First thing you should do is learn how to properly normalize a database.
That will make things much easier.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
Re: mysqli_stmt_bind_param with multiple parameters in an array [message #180749 is a reply to message #180746] Sat, 16 March 2013 12:51 Go to previous messageGo to next message
Peter H. Coffin is currently offline  Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On Sat, 16 Mar 2013 08:46:57 -0700 (PDT), Chris Cosner wrote:
> Thanks, guys.
>
> So I definitely cannot drop an array in there.
>
> Yet I don't want to create 100+ variables for this INSERT.
>
> Not possible with bound parameters?

Cannot use an array for this. Can implode an array to a string using a
comma as a separator then use the string to construct the query you want
to bind. The usual caveats about "if this array is constructed using
data from outside your app, it can be a vector for an injection attack
on your DB" apply. Which is a common reason for people to be using bound
statements in the first place, so think carefully.

--
A: Maybe because some people are too annoyed by top-posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
Re: mysqli_stmt_bind_param with multiple parameters in an array [message #180750 is a reply to message #180746] Sat, 16 March 2013 13:04 Go to previous messageGo to next message
M. Strobel is currently offline  M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Am 16.03.2013 16:46, schrieb Chris Cosner:
> Thanks, guys.
>
> So I definitely cannot drop an array in there.
>
> Yet I don't want to create 100+ variables for this INSERT.
>
> Not possible with bound parameters?
>
http://www.php.net/manual/en/pdostatement.execute.php

Possible with PDO.

100+ columns? Never seen this since '88!

/Str.
Re: mysqli_stmt_bind_param with multiple parameters in an array [message #180752 is a reply to message #180739] Sat, 16 March 2013 14:42 Go to previous message
burrowingowl is currently offline  burrowingowl
Messages: 3
Registered: March 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Thanks. My questions have been answered.
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: question about class getters
Next Topic: script works fine now
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 11 06:37:53 EST 2017

Total time taken to generate the page: 0.00853 seconds