Spec-Zone .ru
спецификации, руководства, описания, API

22.9.3.10. The mysqli_stmt class (mysqli_stmt)

Copyright 1997-2012 the PHP Documentation Group.

Represents a prepared statement.

 mysqli_stmt {
mysqli_stmtProperties int 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 ;
Methods int 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();
}

22.9.3.10.1. mysqli_stmt::$affected_rows,mysqli_stmt_affected_rows

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$affected_rows

    mysqli_stmt_affected_rows

    Returns 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.

Note

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

22.9.3.10.2. mysqli_stmt::attr_get,mysqli_stmt_attr_get

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::attr_get

    mysqli_stmt_attr_get

    Used 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.

22.9.3.10.3. mysqli_stmt::attr_set,mysqli_stmt_attr_set

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::attr_set

    mysqli_stmt_attr_set

    Used 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_result to update the metadata MYSQL_FIELD->max_length value.
MYSQLI_STMT_ATTR_CURSOR_TYPE Type of cursor to open for statement when mysqli_stmt_execute is invoked. mode can be MYSQLI_CURSOR_TYPE_NO_CURSOR (the default) or MYSQLI_CURSOR_TYPE_READ_ONLY.
MYSQLI_STMT_ATTR_PREFETCH_ROWS Number of rows to fetch from server at a time when using a cursor. mode can be in the range from 1 to the maximum value of unsignedlong. The default is 1.

If you use the MYSQLI_STMT_ATTR_CURSOR_TYPE option with MYSQLI_CURSOR_TYPE_READ_ONLY, a cursor is opened for the statement when you invoke mysqli_stmt_execute. If there is already an open cursor from a previous mysqli_stmt_execute call, it closes the cursor before opening a new one. mysqli_stmt_reset also closes any open cursor before preparing the statement for re-execution. mysqli_stmt_free_result closes any open cursor.

If you open a cursor for a prepared statement, mysqli_stmt_store_result is unnecessary.

mode

The value to assign to the attribute.

22.9.3.10.4. mysqli_stmt::bind_param,mysqli_stmt_bind_param

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::bind_param

    mysqli_stmt_bind_param

    Binds 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.

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

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:

Table 22.53. Type specification chars

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

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.

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

22.9.3.10.5. mysqli_stmt::bind_result,mysqli_stmt_bind_result

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::bind_result

    mysqli_stmt_bind_result

    Binds 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

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 AfghanistanALB AlbaniaDZA AlgeriaASM American SamoaAND 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

22.9.3.10.6. mysqli_stmt::close,mysqli_stmt_close

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::close

    mysqli_stmt_close

    Closes 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

22.9.3.10.7. mysqli_stmt::data_seek,mysqli_stmt_data_seek

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::data_seek

    mysqli_stmt_data_seek

    Seeks 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

22.9.3.10.8. mysqli_stmt::$errno,mysqli_stmt_errno

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$errno

    mysqli_stmt_errno

    Returns 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

22.9.3.10.9. mysqli_stmt::$error_list,mysqli_stmt_error_list

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$error_list

    mysqli_stmt_error_list

    Returns 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

22.9.3.10.10. mysqli_stmt::$error,mysqli_stmt_error

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$error

    mysqli_stmt_error

    Returns 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

22.9.3.10.11. mysqli_stmt::execute,mysqli_stmt_execute

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::execute

    mysqli_stmt_execute

    Executes 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.

Note

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

22.9.3.10.12. mysqli_stmt::fetch,mysqli_stmt_fetch

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::fetch

    mysqli_stmt_fetch

    Fetch 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

Note that all columns must be bound by the application before calling mysqli_stmt_fetch.

Note

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

22.9.3.10.13. mysqli_stmt::$field_count,mysqli_stmt_field_count

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$field_count

    mysqli_stmt_field_count

    Returns 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);
Warning

This function iscurrently not documented; only its argument list is available.

22.9.3.10.14. mysqli_stmt::free_result,mysqli_stmt_free_result

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::free_result

    mysqli_stmt_free_result

    Frees 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

22.9.3.10.15. mysqli_stmt::get_result,mysqli_stmt_get_result

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::get_result

    mysqli_stmt_get_result

    Gets 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

22.9.3.10.16. mysqli_stmt::get_warnings,mysqli_stmt_get_warnings

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::get_warnings

    mysqli_stmt_get_warnings

    Get 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);
Warning

This function iscurrently not documented; only its argument list is available.

22.9.3.10.17. mysqli_stmt::$insert_id,mysqli_stmt_insert_id

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$insert_id

    mysqli_stmt_insert_id

    Get 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);
Warning

This function iscurrently not documented; only its argument list is available.

22.9.3.10.18. mysqli_stmt::more_results,mysqli_stmt_more_results

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::more_results

    mysqli_stmt_more_results

    Check 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

22.9.3.10.19. mysqli_stmt::next_result,mysqli_stmt_next_result

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::next_result

    mysqli_stmt_next_result

    Reads 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

22.9.3.10.20. mysqli_stmt::$num_rows,mysqli_stmt_num_rows

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$num_rows

    mysqli_stmt_num_rows

    Return 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

22.9.3.10.21. mysqli_stmt::$param_count,mysqli_stmt_param_count

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$param_count

    mysqli_stmt_param_count

    Returns 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

22.9.3.10.22. mysqli_stmt::prepare,mysqli_stmt_prepare

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::prepare

    mysqli_stmt_prepare

    Prepare 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.

Note

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:

  • mysqlnd on Linux returns an error code of 1153. The error message means "got a packet bigger than max_allowed_packet bytes".

  • mysqlnd on Windows returns an error code 2006. This error message means "server has gone away".

  • libmysqlclient on 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.

Note

You should not add a terminating semicolon or \g to the statement.

Note

The 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

22.9.3.10.23. mysqli_stmt::reset,mysqli_stmt_reset

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::reset

    mysqli_stmt_reset

    Resets 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

22.9.3.10.24. mysqli_stmt::result_metadata,mysqli_stmt_result_metadata

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::result_metadata

    mysqli_stmt_result_metadata

    Returns 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.

Note

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

Note

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

22.9.3.10.25. mysqli_stmt::send_long_data,mysqli_stmt_send_long_data

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::send_long_data

    mysqli_stmt_send_long_data

    Send 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

22.9.3.10.26. mysqli_stmt::$sqlstate,mysqli_stmt_sqlstate

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::$sqlstate

    mysqli_stmt_sqlstate

    Returns 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

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

22.9.3.10.27. mysqli_stmt::store_result,mysqli_stmt_store_result

Copyright 1997-2012 the PHP Documentation Group.

  • mysqli_stmt::store_result

    mysqli_stmt_store_result

    Transfers 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.

Note

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