| mysqli_stmt_bind_param with multiple parameters in an array [message #180739] |
Sat, 16 March 2013 00:47  |
burrowingowl
Messages: 3
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
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] |
Sat, 16 March 2013 02:45  |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 11:39 |
M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
|
Senior Member |
|
|
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 #180747 is a reply to message #180746] |
Sat, 16 March 2013 15:58 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 16:51 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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?
|
|
|
|
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]