Php prepared statements bind parameters

Содержание

mysqli_stmt_bind_param
Parameters
Return Values
Errors/Exceptions
Examples
See Also
User Contributed Notes 40 notes

mysqli_stmt_bind_param

Bind variables for the parameter markers in the SQL statement prepared by mysqli_prepare() or mysqli_stmt_prepare() .

Note:

If data size of a variable exceeds max. allowed packet size (max_allowed_packet), you have to specify b in types and use mysqli_stmt_send_long_data() to send the data in packets.

Note:

Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array() . Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values.

Parameters

Procedural style only: A mysqli_stmt object returned by mysqli_stmt_init() .

A string that contains one or more characters which specify the types for the corresponding bind variables:

Type specification chars

Character	Description
i	corresponding variable has type int
d	corresponding variable has type float
s	corresponding variable has type string
b	corresponding variable is a blob and will be sent in packets

The number of variables and length of string types must match the parameters in the statement.

Return Values

Returns true on success or false on failure.

Errors/Exceptions

If mysqli error reporting is enabled ( MYSQLI_REPORT_ERROR ) and the requested operation fails, a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT , a mysqli_sql_exception is thrown instead.

Examples

Example #1 mysqli_stmt::bind_param() example

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( ‘localhost’ , ‘my_user’ , ‘my_password’ , ‘world’ );

$stmt = $mysqli -> prepare ( «INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)» );
$stmt -> bind_param ( ‘sssd’ , $code , $language , $official , $percent );

$code = ‘DEU’ ;
$language = ‘Bavarian’ ;
$official = «F» ;
$percent = 11.2 ;

printf ( «%d row inserted.\n» , $stmt -> affected_rows );

/* Clean up table CountryLanguage */
$mysqli -> query ( «DELETE FROM CountryLanguage WHERE Language=’Bavarian'» );
printf ( «%d row deleted.\n» , $mysqli -> affected_rows );

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$link = mysqli_connect ( ‘localhost’ , ‘my_user’ , ‘my_password’ , ‘world’ );

$stmt = mysqli_prepare ( $link , «INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)» );
mysqli_stmt_bind_param ( $stmt , ‘sssd’ , $code , $language , $official , $percent );

$code = ‘DEU’ ;
$language = ‘Bavarian’ ;
$official = «F» ;
$percent = 11.2 ;

printf ( «%d row inserted.\n» , mysqli_stmt_affected_rows ( $stmt ));

/* Clean up table CountryLanguage */
mysqli_query ( $link , «DELETE FROM CountryLanguage WHERE Language=’Bavarian'» );
printf ( «%d row deleted.\n» , mysqli_affected_rows ( $link ));

The above examples will output:

1 row inserted. 1 row deleted.

Example #2 Using . to provide arguments

The . operator can be used to provide variable-length argument list, e.g. in a WHERE IN clause.

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( ‘localhost’ , ‘my_user’ , ‘my_password’ , ‘world’ );

$stmt = $mysqli -> prepare ( «SELECT Language FROM CountryLanguage WHERE CountryCode IN (?, ?)» );
/* Using . to provide arguments */
$stmt -> bind_param ( ‘ss’ , . [ ‘DEU’ , ‘POL’ ]);
$stmt -> execute ();
$stmt -> store_result ();

printf ( «%d rows found.\n» , $stmt -> num_rows ());

The above examples will output:

User Contributed Notes 40 notes

There are some things to note when working with mysqli::bind_param() and array-elements.
Re-assigning an array will break any references, no matter if the keys are identical.
You have to explicitly reassign every single value in an array, for the references to be kept.
Best shown in an example:
function getData () <
return array(
0 =>array(
«name» => «test_0» ,
«email» => «test_0@example.com»
),
1 =>array(
«name» => «test_1» ,
«email» => «test_1@example.com»
)
);
>
$db = new mysqli ( «localhost» , «root» , «» , «tests» );
$sql = «INSERT INTO `user` SET `name`=?,`email`=?» ;
$res = $db -> prepare ( $sql );
// If you bind array-elements to a prepared statement, the array has to be declared first with the used keys:
$arr = array( «name» => «» , «email» => «» );
$res -> bind_param ( «ss» , $arr [ ‘name’ ], $arr [ ’email’ ]);
//So far the introduction.

/*
Example 1 (wont work as expected, creates two empty entries)
Re-assigning the array in the while()-head generates a new array, whereas references from bind_param stick to the old array
*/
foreach( getData () as $arr ) <
$res -> execute ();
>

/*
Example 2 (will work as expected)
Re-assigning every single value explicitly keeps the references alive
*/
foreach( getData () as $tempArr ) <
foreach( $tempArr as $k => $v ) <
$arr [ $k ] = $v ;
>
$res -> execute ();
>
?>

Blob and null handling aside, a couple of notes on how param values are automatically converted and forwarded on to the Mysql engine based on your type string argument:

1) PHP will automatically convert the value behind the scenes to the underlying type corresponding to your binding type string. i.e.:

$var = true ;
bind_param ( ‘i’ , $var ); // forwarded to Mysql as 1

2) Though PHP numbers cannot be reliably cast to (int) if larger than PHP_INT_MAX, behind the scenes, the value will be converted anyway to at most long long depending on the size. This means that keeping in mind precision limits and avoiding manually casting the variable to (int) first, you can still use the ‘i’ binding type for larger numbers. i.e.:

$var = ‘429496729479896’ ;
bind_param ( ‘i’ , $var ); // forwarded to Mysql as 429496729479900

3) You can default to ‘s’ for most parameter arguments in most cases. The value will then be automatically cast to string on the back-end before being passed to the Mysql engine. Mysql will then perform its own conversions with values it receives from PHP on execute. This allows you to bind not only to larger numbers without concern for precision, but also to objects as long as that object has a ‘__toString’ method.

This auto-string casting behavior greatly improves things like datetime handling. For example: if you extended DateTime class to add a __toString method which outputs the datetime format expected by Mysql, you can just bind to that DateTime_Extended object using type ‘s’. i.e.:

// DateTime_Extended has __toString defined to return the Mysql formatted datetime
$var = new DateTime_Extended ;
bind_param ( ‘s’ , $var ); // forwarded to Mysql as ‘2011-03-14 17:00:01’

Hi, I just write a function to do all my sql statements based on all the others comments in this page, maybe it can be useful for someone else 🙂

execSQL($sql, $parameters, $close);

$sql = Statement to execute;
$parameters = array of type and values of the parameters (if any)
$close = true to close $stmt (in inserts) false to return an array with the values;

execSQL(«SELECT * FROM table WHERE array(‘i’, $id), false);

execSQL(«SELECT * FROM table», array(), false);

execSQL(«INSERT INTO table(id, name) VALUES (. )», array(‘ss’, $id, $name), true);

function execSQL ( $sql , $params , $close ) $mysqli = new mysqli ( «localhost» , «user» , «pass» , «db» );

$stmt = $mysqli -> prepare ( $sql ) or die ( «Failed to prepared the statement!» );

call_user_func_array (array( $stmt , ‘bind_param’ ), refValues ( $params ));

if( $close ) $result = $mysqli -> affected_rows ;
> else $meta = $stmt -> result_metadata ();

while ( $field = $meta -> fetch_field () ) $parameters [] = & $row [ $field -> name ];
>

call_user_func_array (array( $stmt , ‘bind_result’ ), refValues ( $parameters ));

while ( $stmt -> fetch () ) <
$x = array();
foreach( $row as $key => $val ) <
$x [ $key ] = $val ;
>
$results [] = $x ;
>

$stmt -> close ();
$mysqli -> close ();

function refValues ( $arr ) if ( strnatcmp ( phpversion (), ‘5.3’ ) >= 0 ) //Reference is required for PHP 5.3+
$refs = array();
foreach( $arr as $key => $value )
$refs [ $key ] = & $arr [ $key ];
return $refs ;
>
return $arr ;
>
?>

Regards

A few notes on this function.

If you specify type «i» (integer), the maximum value it allows you to have is 2^32-1 or 2147483647. So, if you are using UNSIGNED INTEGER or BIGINT in your database, then you are better off using «s» (string) for this.

Here’s a quick summary:
(UN)SIGNED TINYINT: I
(UN)SIGNED SMALLINT: I
(UN)SIGNED MEDIUMINT: I
SIGNED INT: I
UNSIGNED INT: S
(UN)SIGNED BIGINT: S

(VAR)CHAR, (TINY/SMALL/MEDIUM/BIG)TEXT/BLOB should all have S.

FLOAT/REAL/DOUBLE (PRECISION) should all be D.

That advice was for MySQL. I have not looked into other database software.

For those learning mysqli::prepare and mysqli_stmt::bind_params for the first time, here is a commented block of code which executes prepared queries and returns data in a similar format to the return values of mysqli_query. I tried to minimize unnecessary classes, objects, or overhead for two reasons:
1) facilitate learning
2) allow relativity interchangeable use with mysqli_query

My goal is to lower the learning curve for whoever is starting out with these family of functions. I am by no means an expert coder/scripter, so I am sure there are improvements and perhaps a few bugs, but I hope not =)

/*
Function: mysqli_prepared_query()
Executes prepared querys given query syntax, and bind parameters
Returns data in array format

Arguments:
mysqli_link
mysqli_prepare query
mysqli_stmt_bind_param argmuent list in the form array($typeDefinitinonString, $var1 [, mixed $. ])

Return values:
When given SELECT, SHOW, DESCRIBE or EXPLAIN statements: returns table data in the form resultArray[row number][associated field name]
Returns number of rows affacted when given other queries
Returns FALSE on error
*/
function mysqli_prepared_query ( $link , $sql , $bindParams = FALSE ) if( $stmt = mysqli_prepare ( $link , $sql )) if ( $bindParams ) <
$bindParamsMethod = new ReflectionMethod ( ‘mysqli_stmt’ , ‘bind_param’ ); //allows for call to mysqli_stmt->bind_param using variable argument list
$bindParamsReferences = array(); //will act as arguments list for mysqli_stmt->bind_param

$typeDefinitionString = array_shift ( $bindParams );
foreach( $bindParams as $key => $value ) $bindParamsReferences [ $key ] = & $bindParams [ $key ];
>

array_unshift ( $bindParamsReferences , $typeDefinitionString ); //returns typeDefinition as the first element of the string
$bindParamsMethod -> invokeArgs ( $stmt , $bindParamsReferences ); //calls mysqli_stmt->bind_param suing $bindParamsRereferences as the argument list
>
if( mysqli_stmt_execute ( $stmt )) $resultMetaData = mysqli_stmt_result_metadata ( $stmt );
if( $resultMetaData ) <
$stmtRow = array(); //this will be a result row returned from mysqli_stmt_fetch($stmt)
$rowReferences = array(); //this will reference $stmtRow and be passed to mysqli_bind_results
while ( $field = mysqli_fetch_field ( $resultMetaData )) <
$rowReferences [] = & $stmtRow [ $field -> name ];
>
mysqli_free_result ( $resultMetaData );
$bindResultMethod = new ReflectionMethod ( ‘mysqli_stmt’ , ‘bind_result’ );
$bindResultMethod -> invokeArgs ( $stmt , $rowReferences ); //calls mysqli_stmt_bind_result($stmt,[$rowReferences]) using object-oriented style
$result = array();
while( mysqli_stmt_fetch ( $stmt )) foreach( $stmtRow as $key => $value ) < //variables must be assigned by value, so $result[] = $stmtRow does not work (not really sure why, something with referencing in $stmtRow)
$row [ $key ] = $value ;
>
$result [] = $row ;
>
mysqli_stmt_free_result ( $stmt );
> else $result = mysqli_stmt_affected_rows ( $stmt );
>
mysqli_stmt_close ( $stmt );
> else $result = FALSE ;
>
> else $result = FALSE ;
>
return $result ;
>

Here’s hoping the PHP gods don’t smite me.

Источник

Читайте также: Php check file is closed