- 22.9.3 MySQL Improved Extension (Mysqli)
- 22.9.3.1 Examples
- 22.9.3.2 Overview
- 22.9.3.3 Quick start guide
- 22.9.3.4 Installing/Configuring
- 22.9.3.5 The mysqli Extension and Persistent Connections
- 22.9.3.6 Predefined Constants
- 22.9.3.7 Notes
- 22.9.3.8 The MySQLi Extension Function Summary
- 22.9.3.9 The mysqli class (mysqli)
- 22.9.3.10 The mysqli_stmt class (mysqli_stmt)
- 22.9.3.10.1 mysqli_stmt::$affected_rows, mysqli_stmt_affected_rows
- 22.9.3.10.2 mysqli_stmt::attr_get, mysqli_stmt_attr_get
- 22.9.3.10.3 mysqli_stmt::attr_set, mysqli_stmt_attr_set
- 22.9.3.10.4 mysqli_stmt::bind_param, mysqli_stmt_bind_param
- 22.9.3.10.5 mysqli_stmt::bind_result, mysqli_stmt_bind_result
- 22.9.3.10.6 mysqli_stmt::close, mysqli_stmt_close
- 22.9.3.10.7 mysqli_stmt::data_seek, mysqli_stmt_data_seek
- 22.9.3.10.8 mysqli_stmt::$errno, mysqli_stmt_errno
- 22.9.3.10.9 mysqli_stmt::$error_list, mysqli_stmt_error_list
- 22.9.3.10.10 mysqli_stmt::$error, mysqli_stmt_error
- 22.9.3.10.11 mysqli_stmt::execute, mysqli_stmt_execute
- 22.9.3.10.12 mysqli_stmt::fetch, mysqli_stmt_fetch
- 22.9.3.10.13 mysqli_stmt::$field_count, mysqli_stmt_field_count
- 22.9.3.10.14 mysqli_stmt::free_result, mysqli_stmt_free_result
- 22.9.3.10.15 mysqli_stmt::get_result, mysqli_stmt_get_result
- 22.9.3.10.16 mysqli_stmt::get_warnings, mysqli_stmt_get_warnings
- 22.9.3.10.17 mysqli_stmt::$insert_id, mysqli_stmt_insert_id
- 22.9.3.10.18 mysqli_stmt::more_results, mysqli_stmt_more_results
- 22.9.3.10.19 mysqli_stmt::next_result, mysqli_stmt_next_result
- 22.9.3.10.20 mysqli_stmt::$num_rows, mysqli_stmt_num_rows
- 22.9.3.10.21 mysqli_stmt::$param_count, mysqli_stmt_param_count
- 22.9.3.10.22 mysqli_stmt::prepare, mysqli_stmt_prepare
- 22.9.3.10.23 mysqli_stmt::reset, mysqli_stmt_reset
- 22.9.3.10.24 mysqli_stmt::result_metadata, mysqli_stmt_result_metadata
- 22.9.3.10.25 mysqli_stmt::send_long_data, mysqli_stmt_send_long_data
- 22.9.3.10.26 mysqli_stmt::$sqlstate, mysqli_stmt_sqlstate
- 22.9.3.10.27 mysqli_stmt::store_result, mysqli_stmt_store_result
- 22.9.3.11 The mysqli_result class (mysqli_result)
- 22.9.3.12 The mysqli_driver class (mysqli_driver)
- 22.9.3.13 The mysqli_warning class (mysqli_warning)
- 22.9.3.14 The mysqli_sql_exception class (mysqli_sql_exception)
- 22.9.3.15 Aliases and deprecated Mysqli Functions
- 22.9.3.16 Changelog
Copyright 1997-2012 the PHP Documentation Group.
Represents a prepared statement.
mysqli_stmt {
mysqli_stmt Propertiesint mysqli_stmt->affected_rows ;int mysqli_stmt->errno ;array mysqli_stmt->error_list ;string mysqli_stmt->error ;int mysqli_stmt->field_count ;int mysqli_stmt->insert_id ;int mysqli_stmt->num_rows ;int mysqli_stmt->param_count ;string mysqli_stmt->sqlstate ;
Methodsint mysqli_stmt::attr_get(int attr);bool mysqli_stmt::attr_set(int attr,
int mode);bool mysqli_stmt::bind_param(string types,
mixed var1,
mixed ...);bool mysqli_stmt::bind_result(mixed var1,
mixed ...);bool mysqli_stmt::close();void mysqli_stmt::data_seek(int offset);bool mysqli_stmt::execute();bool mysqli_stmt::fetch();void mysqli_stmt::free_result();mysqli_result mysqli_stmt::get_result();object mysqli_stmt::get_warnings(mysqli_stmt stmt);mixed mysqli_stmt::prepare(string query);bool mysqli_stmt::reset();mysqli_result mysqli_stmt::result_metadata();bool mysqli_stmt::send_long_data(int param_nr,
string data);bool mysqli_stmt::store_result();
}
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$affected_rowsmysqli_stmt_affected_rowsReturns the total number of rows changed, deleted, or inserted by the last executed statement
Description
Object oriented style
int mysqli_stmt->affected_rows ;Procedural style
int mysqli_stmt_affected_rows(mysqli_stmt stmt);
Returns the number of rows affected by
INSERT, UPDATE, or
DELETE query.
This function only works with queries which update a table. In
order to get the number of rows from a SELECT query, use
mysqli_stmt_num_rows
instead.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE/DELETE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query has returned an error. NULL indicates an invalid argument was supplied to the function.
If the number of affected rows is greater than maximal PHP int value, the number of affected rows will be returned as a string value.
Examples
Example 22.149. Object oriented style
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* create temp table */
$mysqli->query("CREATE TEMPORARY TABLE myCountry LIKE Country");
$query = "INSERT INTO myCountry SELECT * FROM Country WHERE Code LIKE ?";
/* prepare statement */
if ($stmt = $mysqli->prepare($query)) {
/* Bind variable for placeholder */
$code = 'A%';
$stmt->bind_param("s", $code);
/* execute statement */
$stmt->execute();
printf("rows inserted: %d\n", $stmt->affected_rows);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.150. Procedural style
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* create temp table */
mysqli_query($link, "CREATE TEMPORARY TABLE myCountry LIKE Country");
$query = "INSERT INTO myCountry SELECT * FROM Country WHERE Code LIKE ?";
/* prepare statement */
if ($stmt = mysqli_prepare($link, $query)) {
/* Bind variable for placeholder */
$code = 'A%';
mysqli_stmt_bind_param($stmt, "s", $code);
/* execute statement */
mysqli_stmt_execute($stmt);
printf("rows inserted: %d\n", mysqli_stmt_affected_rows($stmt));
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
rows inserted: 17
See Also
mysqli_stmt_num_rows
|
mysqli_prepare
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::attr_getmysqli_stmt_attr_getUsed to get the current value of a statement attribute
Description
Object oriented style
int mysqli_stmt::attr_get(int attr);Procedural style
int mysqli_stmt_attr_get(mysqli_stmt stmt,
int attr);Gets the current value of a statement attribute.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.-
attr The attribute that you want to get.
Return Values
Returns
FALSE
if the attribute is not found, otherwise returns the value of
the attribute.
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::attr_setmysqli_stmt_attr_setUsed to modify the behavior of a prepared statement
Description
Object oriented style
bool mysqli_stmt::attr_set(int attr,
int mode);Procedural style
bool mysqli_stmt_attr_set(mysqli_stmt stmt,
int attr,
int mode);Used to modify the behavior of a prepared statement. This function may be called multiple times to set several attributes.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.-
attr The attribute that you want to set. It can have one of the following values:
Table 22.52. Attribute values
Character Description MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH If set to 1, causes mysqli_stmt_store_resultto update the metadataMYSQL_FIELD->max_lengthvalue.MYSQLI_STMT_ATTR_CURSOR_TYPE Type of cursor to open for statement when mysqli_stmt_executeis invoked.modecan beMYSQLI_CURSOR_TYPE_NO_CURSOR(the default) orMYSQLI_CURSOR_TYPE_READ_ONLY.MYSQLI_STMT_ATTR_PREFETCH_ROWS Number of rows to fetch from server at a time when using a cursor. modecan be in the range from 1 to the maximum value of unsigned long. The default is 1.If you use the
MYSQLI_STMT_ATTR_CURSOR_TYPEoption withMYSQLI_CURSOR_TYPE_READ_ONLY, a cursor is opened for the statement when you invokemysqli_stmt_execute. If there is already an open cursor from a previousmysqli_stmt_executecall, it closes the cursor before opening a new one.mysqli_stmt_resetalso closes any open cursor before preparing the statement for re-execution.mysqli_stmt_free_resultcloses any open cursor.If you open a cursor for a prepared statement,
mysqli_stmt_store_resultis unnecessary.-
mode The value to assign to the attribute.
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::bind_parammysqli_stmt_bind_paramBinds variables to a prepared statement as parameters
Description
Object oriented style
bool mysqli_stmt::bind_param(string types,
mixed var1,
mixed ...);Procedural style
bool mysqli_stmt_bind_param(mysqli_stmt stmt,
string types,
mixed var1,
mixed ...);
Bind variables for the parameter markers in the SQL statement
that was passed to
mysqli_prepare.
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.
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
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.-
types A string that contains one or more characters which specify the types for the corresponding bind variables:
-
var1 The number of variables and length of string
typesmust match the parameters in the statement.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
Examples
Example 22.151. Object oriented style
<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);
$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;
/* execute prepared statement */
$stmt->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);
/* close statement and connection */
$stmt->close();
/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", $mysqli->affected_rows);
/* close connection */
$mysqli->close();
?>
Example 22.152. Procedural style
<?php
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
/* check connection */
if (!$link) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$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;
/* execute prepared statement */
mysqli_stmt_execute($stmt);
printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));
/* close statement and connection */
mysqli_stmt_close($stmt);
/* Clean up table CountryLanguage */
mysqli_query($link, "DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", mysqli_affected_rows($link));
/* close connection */
mysqli_close($link);
?>
The above examples will output:
1 Row inserted. 1 Row deleted.
See Also
mysqli_stmt_bind_result
|
mysqli_stmt_execute
|
mysqli_stmt_fetch
|
mysqli_prepare
|
mysqli_stmt_send_long_data
|
mysqli_stmt_errno
|
mysqli_stmt_error
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::bind_resultmysqli_stmt_bind_resultBinds variables to a prepared statement for result storage
Description
Object oriented style
bool mysqli_stmt::bind_result(mixed var1,
mixed ...);Procedural style
bool mysqli_stmt_bind_result(mysqli_stmt stmt,
mixed var1,
mixed ...);Binds columns in the result set to variables.
When
mysqli_stmt_fetch
is called to fetch data, the MySQL client/server protocol places
the data for the bound columns into the specified variables
var1, ....
Note that all columns must be bound after
mysqli_stmt_execute
and prior to calling
mysqli_stmt_fetch.
Depending on column types bound variables can silently change
to the corresponding PHP type.
A column can be bound or rebound at any time, even after a
result set has been partially retrieved. The new binding takes
effect the next time
mysqli_stmt_fetch
is called.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.-
var1 The variable to be bound.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
Examples
Example 22.153. Object oriented style
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
$stmt->execute();
/* bind variables to prepared statement */
$stmt->bind_result($col1, $col2);
/* fetch values */
while ($stmt->fetch()) {
printf("%s %s\n", $col1, $col2);
}
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.154. Procedural style
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (!$link) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* prepare statement */
if ($stmt = mysqli_prepare($link, "SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
mysqli_stmt_execute($stmt);
/* bind variables to prepared statement */
mysqli_stmt_bind_result($stmt, $col1, $col2);
/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
printf("%s %s\n", $col1, $col2);
}
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
AFG Afghanistan ALB Albania DZA Algeria ASM American Samoa AND Andorra
See Also
mysqli_stmt_bind_param
|
mysqli_stmt_execute
|
mysqli_stmt_fetch
|
mysqli_prepare
|
mysqli_stmt_prepare
|
mysqli_stmt_init
|
mysqli_stmt_errno
|
mysqli_stmt_error
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::closemysqli_stmt_closeCloses a prepared statement
Description
Object oriented style
bool mysqli_stmt::close();Procedural style
bool mysqli_stmt_close(mysqli_stmt stmt);
Closes a prepared statement.
mysqli_stmt_close
also deallocates the statement handle. If the current statement
has pending or unread results, this function cancels them so
that the next query can be executed.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
See Also
mysqli_prepare
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::data_seekmysqli_stmt_data_seekSeeks to an arbitrary row in statement result set
Description
Object oriented style
void mysqli_stmt::data_seek(int offset);Procedural style
void mysqli_stmt_data_seek(mysqli_stmt stmt,
int offset);Seeks to an arbitrary result pointer in the statement result set.
mysqli_stmt_store_result
must be called prior to
mysqli_stmt_data_seek.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.-
offset Must be between zero and the total number of rows minus one (0..
mysqli_stmt_num_rows- 1).
Return Values
No value is returned.
Examples
Example 22.155. Object oriented style
<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT Name, CountryCode FROM City ORDER BY Name";
if ($stmt = $mysqli->prepare($query)) {
/* execute query */
$stmt->execute();
/* bind result variables */
$stmt->bind_result($name, $code);
/* store result */
$stmt->store_result();
/* seek to row no. 400 */
$stmt->data_seek(399);
/* fetch values */
$stmt->fetch();
printf ("City: %s Countrycode: %s\n", $name, $code);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.156. Procedural style
<?php
/* Open a connection */
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT Name, CountryCode FROM City ORDER BY Name";
if ($stmt = mysqli_prepare($link, $query)) {
/* execute query */
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $name, $code);
/* store result */
mysqli_stmt_store_result($stmt);
/* seek to row no. 400 */
mysqli_stmt_data_seek($stmt, 399);
/* fetch values */
mysqli_stmt_fetch($stmt);
printf ("City: %s Countrycode: %s\n", $name, $code);
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
City: Benin City Countrycode: NGA
See Also
mysqli_prepare
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$errnomysqli_stmt_errnoReturns the error code for the most recent statement call
Description
Object oriented style
int mysqli_stmt->errno ;Procedural style
int mysqli_stmt_errno(mysqli_stmt stmt);Returns the error code for the most recently invoked statement function that can succeed or fail.
Client error message numbers are listed in the MySQL
errmsg.h header file, server error message
numbers are listed in mysqld_error.h. In
the MySQL source distribution you can find a complete list of
error messages and error numbers in the file
Docs/mysqld_error.txt.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
An error code value. Zero means no error occurred.
Examples
Example 22.157. Object oriented style
<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TABLE myCountry LIKE Country");
$mysqli->query("INSERT INTO myCountry SELECT * FROM Country");
$query = "SELECT Name, Code FROM myCountry ORDER BY Name";
if ($stmt = $mysqli->prepare($query)) {
/* drop table */
$mysqli->query("DROP TABLE myCountry");
/* execute query */
$stmt->execute();
printf("Error: %d.\n", $stmt->errno);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.158. Procedural style
<?php
/* Open a connection */
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CREATE TABLE myCountry LIKE Country");
mysqli_query($link, "INSERT INTO myCountry SELECT * FROM Country");
$query = "SELECT Name, Code FROM myCountry ORDER BY Name";
if ($stmt = mysqli_prepare($link, $query)) {
/* drop table */
mysqli_query($link, "DROP TABLE myCountry");
/* execute query */
mysqli_stmt_execute($stmt);
printf("Error: %d.\n", mysqli_stmt_errno($stmt));
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Error: 1146.
See Also
mysqli_stmt_error
|
mysqli_stmt_sqlstate
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$error_listmysqli_stmt_error_listReturns a list of errors from the last statement executed
Description
Object oriented style
array mysqli_stmt->error_list ;Procedural style
array mysqli_stmt_error_list(mysqli_stmt stmt);Returns an array of errors for the most recently invoked statement function that can succeed or fail.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
A list of errors, each as an associative array containing the errno, error, and sqlstate.
Examples
Example 22.159. Object oriented style
<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TABLE myCountry LIKE Country");
$mysqli->query("INSERT INTO myCountry SELECT * FROM Country");
$query = "SELECT Name, Code FROM myCountry ORDER BY Name";
if ($stmt = $mysqli->prepare($query)) {
/* drop table */
$mysqli->query("DROP TABLE myCountry");
/* execute query */
$stmt->execute();
echo "Error:\n";
print_r($stmt->error_list);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.160. Procedural style
<?php
/* Open a connection */
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CREATE TABLE myCountry LIKE Country");
mysqli_query($link, "INSERT INTO myCountry SELECT * FROM Country");
$query = "SELECT Name, Code FROM myCountry ORDER BY Name";
if ($stmt = mysqli_prepare($link, $query)) {
/* drop table */
mysqli_query($link, "DROP TABLE myCountry");
/* execute query */
mysqli_stmt_execute($stmt);
echo "Error:\n";
print_r(mysql_stmt_error_list($stmt));
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Array
(
[0] => Array
(
[errno] => 1146
[sqlstate] => 42S02
[error] => Table 'world.myCountry' doesn't exist
)
)
See Also
mysqli_stmt_error
|
mysqli_stmt_errno
|
mysqli_stmt_sqlstate
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$errormysqli_stmt_errorReturns a string description for last statement error
Description
Object oriented style
string mysqli_stmt->error ;Procedural style
string mysqli_stmt_error(mysqli_stmt stmt);Returns a containing the error message for the most recently invoked statement function that can succeed or fail.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
A string that describes the error. An empty string if no error occurred.
Examples
Example 22.161. Object oriented style
<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TABLE myCountry LIKE Country");
$mysqli->query("INSERT INTO myCountry SELECT * FROM Country");
$query = "SELECT Name, Code FROM myCountry ORDER BY Name";
if ($stmt = $mysqli->prepare($query)) {
/* drop table */
$mysqli->query("DROP TABLE myCountry");
/* execute query */
$stmt->execute();
printf("Error: %s.\n", $stmt->error);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.162. Procedural style
<?php
/* Open a connection */
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CREATE TABLE myCountry LIKE Country");
mysqli_query($link, "INSERT INTO myCountry SELECT * FROM Country");
$query = "SELECT Name, Code FROM myCountry ORDER BY Name";
if ($stmt = mysqli_prepare($link, $query)) {
/* drop table */
mysqli_query($link, "DROP TABLE myCountry");
/* execute query */
mysqli_stmt_execute($stmt);
printf("Error: %s.\n", mysqli_stmt_error($stmt));
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Error: Table 'world.myCountry' doesn't exist.
See Also
mysqli_stmt_errno
|
mysqli_stmt_sqlstate
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::executemysqli_stmt_executeExecutes a prepared Query
Description
Object oriented style
bool mysqli_stmt::execute();Procedural style
bool mysqli_stmt_execute(mysqli_stmt stmt);
Executes a query that has been previously prepared using the
mysqli_prepare
function. When executed any parameter markers which exist will
automatically be replaced with the appropriate data.
If the statement is UPDATE,
DELETE, or INSERT, the
total number of affected rows can be determined by using the
mysqli_stmt_affected_rows
function. Likewise, if the query yields a result set the
mysqli_stmt_fetch
function is used.
When using
mysqli_stmt_execute,
the
mysqli_stmt_fetch
function must be used to fetch the data prior to performing
any additional queries.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
Examples
Example 22.163. Object oriented style
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TABLE myCity LIKE City");
/* Prepare an insert statement */
$query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("sss", $val1, $val2, $val3);
$val1 = 'Stuttgart';
$val2 = 'DEU';
$val3 = 'Baden-Wuerttemberg';
/* Execute the statement */
$stmt->execute();
$val1 = 'Bordeaux';
$val2 = 'FRA';
$val3 = 'Aquitaine';
/* Execute the statement */
$stmt->execute();
/* close statement */
$stmt->close();
/* retrieve all rows from myCity */
$query = "SELECT Name, CountryCode, District FROM myCity";
if ($result = $mysqli->query($query)) {
while ($row = $result->fetch_row()) {
printf("%s (%s,%s)\n", $row[0], $row[1], $row[2]);
}
/* free result set */
$result->close();
}
/* remove table */
$mysqli->query("DROP TABLE myCity");
/* close connection */
$mysqli->close();
?>
Example 22.164. Procedural style
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CREATE TABLE myCity LIKE City");
/* Prepare an insert statement */
$query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";
$stmt = mysqli_prepare($link, $query);
mysqli_stmt_bind_param($stmt, "sss", $val1, $val2, $val3);
$val1 = 'Stuttgart';
$val2 = 'DEU';
$val3 = 'Baden-Wuerttemberg';
/* Execute the statement */
mysqli_stmt_execute($stmt);
$val1 = 'Bordeaux';
$val2 = 'FRA';
$val3 = 'Aquitaine';
/* Execute the statement */
mysqli_stmt_execute($stmt);
/* close statement */
mysqli_stmt_close($stmt);
/* retrieve all rows from myCity */
$query = "SELECT Name, CountryCode, District FROM myCity";
if ($result = mysqli_query($link, $query)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s (%s,%s)\n", $row[0], $row[1], $row[2]);
}
/* free result set */
mysqli_free_result($result);
}
/* remove table */
mysqli_query($link, "DROP TABLE myCity");
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Stuttgart (DEU,Baden-Wuerttemberg) Bordeaux (FRA,Aquitaine)
See Also
mysqli_prepare
|
mysqli_stmt_bind_param
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::fetchmysqli_stmt_fetchFetch results from a prepared statement into the bound variables
Description
Object oriented style
bool mysqli_stmt::fetch();Procedural style
bool mysqli_stmt_fetch(mysqli_stmt stmt);
Fetch the result from a prepared statement into the variables
bound by
mysqli_stmt_bind_result.
Note that all columns must be bound by the application before
calling
mysqli_stmt_fetch.
Data are transferred unbuffered without calling
mysqli_stmt_store_result
which can decrease performance (but reduces memory cost).
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Table 22.54. Return Values
| Value | Description |
|---|---|
TRUE | Success. Data has been fetched |
FALSE | Error occurred |
NULL | No more rows/data exists or data truncation occurred |
Examples
Example 22.165. Object oriented style
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 150,5";
if ($stmt = $mysqli->prepare($query)) {
/* execute statement */
$stmt->execute();
/* bind result variables */
$stmt->bind_result($name, $code);
/* fetch values */
while ($stmt->fetch()) {
printf ("%s (%s)\n", $name, $code);
}
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.166. Procedural style
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 150,5";
if ($stmt = mysqli_prepare($link, $query)) {
/* execute statement */
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $name, $code);
/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
printf ("%s (%s)\n", $name, $code);
}
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Rockford (USA) Tallahassee (USA) Salinas (USA) Santa Clarita (USA) Springfield (USA)
See Also
mysqli_prepare
|
mysqli_stmt_errno
|
mysqli_stmt_error
|
mysqli_stmt_bind_result
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$field_countmysqli_stmt_field_countReturns the number of field in the given statement
Description
Object oriented style
int mysqli_stmt->field_count ;Procedural style
int mysqli_stmt_field_count(mysqli_stmt stmt);
This function is currently not documented; only its argument list is available.
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::free_resultmysqli_stmt_free_resultFrees stored result memory for the given statement handle
Description
Object oriented style
void mysqli_stmt::free_result();Procedural style
void mysqli_stmt_free_result(mysqli_stmt stmt);
Frees the result memory associated with the statement, which was
allocated by
mysqli_stmt_store_result.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
No value is returned.
See Also
mysqli_stmt_store_result
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::get_resultmysqli_stmt_get_resultGets a result set from a prepared statement
Description
Object oriented style
mysqli_result mysqli_stmt::get_result();Procedural style
mysqli_result mysqli_stmt_get_result(mysqli_stmt stmt);Call to return a result set from a prepared statement query.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns a resultset or
FALSE
on failure.
MySQL Native Driver Only
Available only with mysqlnd.
Examples
Example 22.167. Object oriented style
<?php
$mysqli = new mysqli("127.0.0.1", "user", "password", "world");
if($mysqli->connect_error)
{
die("$mysqli->connect_errno: $mysqli->connect_error");
}
$query = "SELECT Name, Population, Continent FROM Country WHERE Continent=? ORDER BY Name LIMIT 1";
$stmt = $mysqli->stmt_init();
if(!$stmt->prepare($query))
{
print "Failed to prepare statement\n";
}
else
{
$stmt->bind_param("s", $continent);
$continent_array = array('Europe','Africa','Asia','North America');
foreach($continent_array as $continent)
{
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM))
{
foreach ($row as $r)
{
print "$r ";
}
print "\n";
}
}
}
$stmt->close();
$mysqli->close();
?>
Example 22.168. Procedural style
<?php
$link = mysqli_connect("127.0.0.1", "user", "password", "world");
if (!$link)
{
$error = mysqli_connect_error();
$errno = mysqli_connect_errno();
print "$errno: $error\n";
exit();
}
$query = "SELECT Name, Population, Continent FROM Country WHERE Continent=? ORDER BY Name LIMIT 1";
$stmt = mysqli_stmt_init($link);
if(!mysqli_stmt_prepare($stmt, $query))
{
print "Failed to prepare statement\n";
}
else
{
mysqli_stmt_bind_param($stmt, "s", $continent);
$continent_array = array('Europe','Africa','Asia','North America');
foreach($continent_array as $continent)
{
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while ($row = mysqli_fetch_array($result, MYSQLI_NUM))
{
foreach ($row as $r)
{
print "$r ";
}
print "\n";
}
}
}
mysqli_stmt_close($stmt);
mysqli_close($link);
?>
The above examples will output:
Albania 3401200 Europe Algeria 31471000 Africa Afghanistan 22720000 Asia Anguilla 8000 North America
See Also
mysqli_prepare
|
mysqli_stmt_result_metadata
|
mysqli_stmt_fetch
|
mysqli_fetch_array
|
mysqli_stmt_store_result
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::get_warningsmysqli_stmt_get_warningsGet result of SHOW WARNINGS
Description
Object oriented style
object mysqli_stmt::get_warnings(mysqli_stmt stmt);Procedural style
object mysqli_stmt_get_warnings(mysqli_stmt stmt);
This function is currently not documented; only its argument list is available.
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$insert_idmysqli_stmt_insert_idGet the ID generated from the previous INSERT operation
Description
Object oriented style
int mysqli_stmt->insert_id ;Procedural style
mixed mysqli_stmt_insert_id(mysqli_stmt stmt);
This function is currently not documented; only its argument list is available.
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::more_resultsmysqli_stmt_more_resultsCheck if there are more query results from a multiple query
Description
Object oriented style (method):
public bool mysqli_stmt::more_results();Procedural style:
bool mysqli_stmt_more_results(mysql_stmt stmt);Checks if there are more query results from a multiple query.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns
TRUE
if more results exist, otherwise
FALSE
.
See Also
mysqli_stmt::next_result
|
mysqli::multi_query
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::next_resultmysqli_stmt_next_resultReads the next result from a multiple query
Description
Object oriented style (method):
public bool mysqli_stmt::next_result();Procedural style:
bool mysqli_stmt_next_result(mysql_stmt stmt);Reads the next result from a multiple query.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
Errors/Exceptions
Emits an
E_STRICT
level error if a result set does not exist, and suggests using
mysqli_stmt::more_results
in these cases, before calling
mysqli_stmt::next_result.
See Also
mysqli_stmt::more_results
|
mysqli::multi_query
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$num_rowsmysqli_stmt_num_rowsReturn the number of rows in statements result set
Description
Object oriented style
int mysqli_stmt->num_rows ;Procedural style
int mysqli_stmt_num_rows(mysqli_stmt stmt);
Returns the number of rows in the result set. The use of
mysqli_stmt_num_rows
depends on whether or not you used
mysqli_stmt_store_result
to buffer the entire result set in the statement handle.
If you use
mysqli_stmt_store_result,
mysqli_stmt_num_rows
may be called immediately.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
An integer representing the number of rows in result set.
Examples
Example 22.169. Object oriented style
<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT Name, CountryCode FROM City ORDER BY Name LIMIT 20";
if ($stmt = $mysqli->prepare($query)) {
/* execute query */
$stmt->execute();
/* store result */
$stmt->store_result();
printf("Number of rows: %d.\n", $stmt->num_rows);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.170. Procedural style
<?php
/* Open a connection */
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT Name, CountryCode FROM City ORDER BY Name LIMIT 20";
if ($stmt = mysqli_prepare($link, $query)) {
/* execute query */
mysqli_stmt_execute($stmt);
/* store result */
mysqli_stmt_store_result($stmt);
printf("Number of rows: %d.\n", mysqli_stmt_num_rows($stmt));
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Number of rows: 20.
See Also
mysqli_stmt_affected_rows
|
mysqli_prepare
|
mysqli_stmt_store_result
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$param_countmysqli_stmt_param_countReturns the number of parameter for the given statement
Description
Object oriented style
int mysqli_stmt->param_count ;Procedural style
int mysqli_stmt_param_count(mysqli_stmt stmt);Returns the number of parameter markers present in the prepared statement.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns an integer representing the number of parameters.
Examples
Example 22.171. Object oriented style
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if ($stmt = $mysqli->prepare("SELECT Name FROM Country WHERE Name=? OR Code=?")) {
$marker = $stmt->param_count;
printf("Statement has %d markers.\n", $marker);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.172. Procedural style
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if ($stmt = mysqli_prepare($link, "SELECT Name FROM Country WHERE Name=? OR Code=?")) {
$marker = mysqli_stmt_param_count($stmt);
printf("Statement has %d markers.\n", $marker);
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Statement has 2 markers.
See Also
mysqli_prepare
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::preparemysqli_stmt_preparePrepare an SQL statement for execution
Description
Object oriented style
mixed mysqli_stmt::prepare(string query);Procedural style
bool mysqli_stmt_prepare(mysqli_stmt stmt,
string query);Prepares the SQL query pointed to by the null-terminated string query.
The parameter markers must be bound to application variables
using
mysqli_stmt_bind_param
and/or
mysqli_stmt_bind_result
before executing the statement or fetching rows.
In the case where you pass a statement to
mysqli_stmt_prepare
that is longer than max_allowed_packet of
the server, the returned error codes are different depending
on whether you are using MySQL Native Driver
(mysqlnd) or MySQL Client Library
(libmysqlclient). The behavior is as
follows:
mysqlndon Linux returns an error code of 1153. The error message means “got a packet bigger thanmax_allowed_packetbytes”.mysqlndon Windows returns an error code 2006. This error message means “server has gone away”.libmysqlclienton all platforms returns an error code 2006. This error message means “server has gone away”.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.-
query The query, as a string. It must consist of a single SQL statement.
You can include one or more parameter markers in the SQL statement by embedding question mark (
?) characters at the appropriate positions.NoteYou should not add a terminating semicolon or
\gto the statement.NoteThe markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.
However, they are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement), or to specify both operands of a binary operator such as the
=equal sign. The latter restriction is necessary because it would be impossible to determine the parameter type. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
Examples
Example 22.173. Object oriented style
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
/* create a prepared statement */
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT District FROM City WHERE Name=?")) {
/* bind parameters for markers */
$stmt->bind_param("s", $city);
/* execute query */
$stmt->execute();
/* bind result variables */
$stmt->bind_result($district);
/* fetch value */
$stmt->fetch();
printf("%s is in district %s\n", $city, $district);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.174. Procedural style
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
/* create a prepared statement */
$stmt = mysqli_stmt_init($link);
if (mysqli_stmt_prepare($stmt, 'SELECT District FROM City WHERE Name=?')) {
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "s", $city);
/* execute query */
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $district);
/* fetch value */
mysqli_stmt_fetch($stmt);
printf("%s is in district %s\n", $city, $district);
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Amersfoort is in district Utrecht
See Also
mysqli_stmt_init
|
mysqli_stmt_execute
|
mysqli_stmt_fetch
|
mysqli_stmt_bind_param
|
mysqli_stmt_bind_result
|
mysqli_stmt_close
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::resetmysqli_stmt_resetResets a prepared statement
Description
Object oriented style
bool mysqli_stmt::reset();Procedural style
bool mysqli_stmt_reset(mysqli_stmt stmt);Resets a prepared statement on client and server to state after prepare.
It resets the statement on the server, data sent using
mysqli_stmt_send_long_data,
unbuffered result sets and current errors. It does not clear
bindings or stored result sets. Stored result sets will be
cleared when executing the prepared statement (or closing it).
To prepare a statement with another query use function
mysqli_stmt_prepare.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
See Also
mysqli_prepare
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::result_metadatamysqli_stmt_result_metadataReturns result set metadata from a prepared statement
Description
Object oriented style
mysqli_result mysqli_stmt::result_metadata();Procedural style
mysqli_result mysqli_stmt_result_metadata(mysqli_stmt stmt);
If a statement passed to
mysqli_prepare
is one that produces a result set,
mysqli_stmt_result_metadata
returns the result object that can be used to process the meta
information such as total number of fields and individual field
information.
This result set pointer can be passed as an argument to any of the field-based functions that process result set metadata, such as:
The result set structure should be freed when you are done with
it, which you can do by passing it to
mysqli_free_result
The result set returned by
mysqli_stmt_result_metadata
contains only metadata. It does not contain any row results.
The rows are obtained by using the statement handle with
mysqli_stmt_fetch.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns a result object or
FALSE
if an error occurred.
Examples
Example 22.175. Object oriented style
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "test");
$mysqli->query("DROP TABLE IF EXISTS friends");
$mysqli->query("CREATE TABLE friends (id int, name varchar(20))");
$mysqli->query("INSERT INTO friends VALUES (1,'Hartmut'), (2, 'Ulf')");
$stmt = $mysqli->prepare("SELECT id, name FROM friends");
$stmt->execute();
/* get resultset for metadata */
$result = $stmt->result_metadata();
/* retrieve field information from metadata result set */
$field = $result->fetch_field();
printf("Fieldname: %s\n", $field->name);
/* close resultset */
$result->close();
/* close connection */
$mysqli->close();
?>
Example 22.176. Procedural style
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "test");
mysqli_query($link, "DROP TABLE IF EXISTS friends");
mysqli_query($link, "CREATE TABLE friends (id int, name varchar(20))");
mysqli_query($link, "INSERT INTO friends VALUES (1,'Hartmut'), (2, 'Ulf')");
$stmt = mysqli_prepare($link, "SELECT id, name FROM friends");
mysqli_stmt_execute($stmt);
/* get resultset for metadata */
$result = mysqli_stmt_result_metadata($stmt);
/* retrieve field information from metadata result set */
$field = mysqli_fetch_field($result);
printf("Fieldname: %s\n", $field->name);
/* close resultset */
mysqli_free_result($result);
/* close connection */
mysqli_close($link);
?>
See Also
mysqli_prepare
|
mysqli_free_result
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::send_long_datamysqli_stmt_send_long_dataSend data in blocks
Description
Object oriented style
bool mysqli_stmt::send_long_data(int param_nr,
string data);Procedural style
bool mysqli_stmt_send_long_data(mysqli_stmt stmt,
int param_nr,
string data);
Allows to send parameter data to the server in pieces (or
chunks), e.g. if the size of a blob exceeds the size of
max_allowed_packet. This function can be
called multiple times to send the parts of a character or binary
data value for a column, which must be one of the TEXT or BLOB
datatypes.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.-
param_nr Indicates which parameter to associate the data with. Parameters are numbered beginning with 0.
-
data A string containing data to be sent.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
Examples
Example 22.177. Object oriented style
<?php
$stmt = $mysqli->prepare("INSERT INTO messages (message) VALUES (?)");
$null = NULL;
$stmt->bind_param("b", $null);
$fp = fopen("messages.txt", "r");
while (!feof($fp)) {
$stmt->send_long_data(0, fread($fp, 8192));
}
fclose($fp);
$stmt->execute();
?>
See Also
mysqli_prepare
|
mysqli_stmt_bind_param
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::$sqlstatemysqli_stmt_sqlstateReturns SQLSTATE error from previous statement operation
Description
Object oriented style
string mysqli_stmt->sqlstate ;Procedural style
string mysqli_stmt_sqlstate(mysqli_stmt stmt);
Returns a string containing the SQLSTATE error code for the most
recently invoked prepared statement function that can succeed or
fail. The error code consists of five characters.
'00000' means no error. The values
are specified by ANSI SQL and ODBC. For a list of possible
values, see
http://dev.mysql.com/doc/mysql/en/error-handling.html.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns a string containing the SQLSTATE error code for the last
error. The error code consists of five characters.
'00000' means no error.
Notes
Note that not all MySQL errors are yet mapped to
SQLSTATE's. The value HY000 (general
error) is used for unmapped errors.
Examples
Example 22.178. Object oriented style
<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TABLE myCountry LIKE Country");
$mysqli->query("INSERT INTO myCountry SELECT * FROM Country");
$query = "SELECT Name, Code FROM myCountry ORDER BY Name";
if ($stmt = $mysqli->prepare($query)) {
/* drop table */
$mysqli->query("DROP TABLE myCountry");
/* execute query */
$stmt->execute();
printf("Error: %s.\n", $stmt->sqlstate);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.179. Procedural style
<?php
/* Open a connection */
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CREATE TABLE myCountry LIKE Country");
mysqli_query($link, "INSERT INTO myCountry SELECT * FROM Country");
$query = "SELECT Name, Code FROM myCountry ORDER BY Name";
if ($stmt = mysqli_prepare($link, $query)) {
/* drop table */
mysqli_query($link, "DROP TABLE myCountry");
/* execute query */
mysqli_stmt_execute($stmt);
printf("Error: %s.\n", mysqli_stmt_sqlstate($stmt));
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Error: 42S02.
See Also
mysqli_stmt_errno
|
mysqli_stmt_error
|
Copyright 1997-2012 the PHP Documentation Group.
mysqli_stmt::store_resultmysqli_stmt_store_resultTransfers a result set from a prepared statement
Description
Object oriented style
bool mysqli_stmt::store_result();Procedural style
bool mysqli_stmt_store_result(mysqli_stmt stmt);
You must call
mysqli_stmt_store_result
for every query that successfully produces a result set
(SELECT, SHOW, DESCRIBE, EXPLAIN), and only
if you want to buffer the complete result set by the client, so
that the subsequent
mysqli_stmt_fetch
call returns buffered data.
It is unnecessary to call
mysqli_stmt_store_result
for other queries, but if you do, it will not harm or cause
any notable performance in all cases. You can detect whether
the query produced a result set by checking if
mysqli_stmt_result_metadata
returns NULL.
Parameters
-
stmt Procedural style only: A statement identifier returned by
mysqli_stmt_init.
Return Values
Returns
TRUE
on success or
FALSE
on failure.
Examples
Example 22.180. Object oriented style
<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT Name, CountryCode FROM City ORDER BY Name LIMIT 20";
if ($stmt = $mysqli->prepare($query)) {
/* execute query */
$stmt->execute();
/* store result */
$stmt->store_result();
printf("Number of rows: %d.\n", $stmt->num_rows);
/* free result */
$stmt->free_result();
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Example 22.181. Procedural style
<?php
/* Open a connection */
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT Name, CountryCode FROM City ORDER BY Name LIMIT 20";
if ($stmt = mysqli_prepare($link, $query)) {
/* execute query */
mysqli_stmt_execute($stmt);
/* store result */
mysqli_stmt_store_result($stmt);
printf("Number of rows: %d.\n", mysqli_stmt_num_rows($stmt));
/* free result */
mysqli_stmt_free_result($stmt);
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
The above examples will output:
Number of rows: 20.
See Also
mysqli_prepare
|
mysqli_stmt_result_metadata
|
mysqli_stmt_fetch
|
User Comments
Add your own comment.