Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #8675 > unrolled thread

mysqli_stmt_bind_param with multiple parameters in an array

Back to article view | Back to comp.lang.php

Contents

  mysqli_stmt_bind_param with multiple parameters in an array burrowingowl@gmail.com - 2013-03-15 17:47 -0700
    Re: mysqli_stmt_bind_param with multiple parameters in an array Jerry Stuckle <jstucklex@attglobal.net> - 2013-03-15 22:45 -0400
    Re: mysqli_stmt_bind_param with multiple parameters in an array "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2013-03-16 12:39 +0100
    Re: mysqli_stmt_bind_param with multiple parameters in an array Chris Cosner <burrowingowl@gmail.com> - 2013-03-16 08:46 -0700
      Re: mysqli_stmt_bind_param with multiple parameters in an array Jerry Stuckle <jstucklex@attglobal.net> - 2013-03-16 11:58 -0400
      Re: mysqli_stmt_bind_param with multiple parameters in an array "Peter H. Coffin" <hellsop@ninehells.com> - 2013-03-16 11:51 -0500
      Re: mysqli_stmt_bind_param with multiple parameters in an array "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2013-03-16 18:04 +0100
    Re: mysqli_stmt_bind_param with multiple parameters in an array Chris Cosner <burrowingowl@gmail.com> - 2013-03-16 11:42 -0700

#8675 — mysqli_stmt_bind_param with multiple parameters in an array

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_following,author_1_last_name,author_1_middle,author_1_preceding,author_1_role,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.

[toc] | [next] | [standalone]

#8676

On 3/15/2013 8:47 PM, burrowingowl@gmail.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_following,author_1_last_name,author_1_middle,author_1_preceding,author_1_role,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@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#8678

Am 16.03.2013 01:47, schrieb burrowingowl@gmail.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_following,author_1_last_name,author_1_middle,author_1_preceding,author_1_role,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.

[toc] | [prev] | [next] | [standalone]

#8682

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?

[toc] | [prev] | [next] | [standalone]

#8683

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@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#8685

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?  

[toc] | [prev] | [next] | [standalone]

#8686

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.

[toc] | [prev] | [next] | [standalone]

#8688

Thanks. My questions have been answered.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web