[+/-]
mysqli_stmt->affected_rows
,
mysqli_stmt_affected_rows
mysqli_stmt::attr_get
,
mysqli_stmt_attr_get
mysqli_stmt::attr_set
,
mysqli_stmt_attr_set
mysqli_stmt::bind_param
,
mysqli_stmt_bind_param
mysqli_stmt::bind_result
,
mysqli_stmt_bind_result
mysqli_stmt::close
,
mysqli_stmt_close
mysqli_stmt::data_seek
,
mysqli_stmt_data_seek
mysqli_stmt->errno
,
mysqli_stmt_errno
mysqli_stmt->error
,
mysqli_stmt_error
mysqli_stmt::execute
,
mysqli_stmt_execute
mysqli_stmt::fetch
,
mysqli_stmt_fetch
mysqli_stmt->field_count
,
mysqli_stmt_field_count
mysqli_stmt::free_result
,
mysqli_stmt_free_result
mysqli_stmt::get_warnings
,
mysqli_stmt_get_warnings
mysqli_stmt->insert_id
,
mysqli_stmt_insert_id
mysqli_stmt::num_rows
,
mysqli_stmt_num_rows
mysqli_stmt->param_count
,
mysqli_stmt_param_count
mysqli_stmt::prepare
,
mysqli_stmt_prepare
mysqli_stmt::reset
,
mysqli_stmt_reset
mysqli_stmt::result_metadata
,
mysqli_stmt_result_metadata
mysqli_stmt::send_long_data
,
mysqli_stmt_send_long_data
mysqli_stmt::sqlstate
,
mysqli_stmt_sqlstate
mysqli_stmt::store_result
,
mysqli_stmt_store_result
Copyright 1997-2008 the PHP Documentation Group.
Represents a prepared statement.
MySQLi_STMT {
MySQLi_STMT Propertiesint affected_rows ;
int errno ;
string error ;
int field_count ;
int insert_id ;
int num_rows ;
int param_count ;
string sqlstate ;
Methodsint mysqli_stmt_affected_rows(mysqli_stmt stmt);
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);
int mysqli_stmt_errno(mysqli_stmt stmt);
string mysqli_stmt_error(mysqli_stmt stmt);
bool mysqli_stmt::execute();
bool mysqli_stmt::fetch();
int mysqli_stmt_field_count(mysqli_stmt stmt);
void mysqli_stmt::free_result();
object mysqli_stmt::get_warnings(mysqli_stmt stmt);
mixed mysqli_stmt_insert_id(mysqli_stmt stmt);
int mysqli_stmt_num_rows(mysqli_stmt stmt);
int mysqli_stmt_param_count(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);string mysqli_stmt_sqlstate(mysqli_stmt stmt);
bool mysqli_stmt::store_result();
}
Copyright 1997-2008 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 (property):
mysqli_stmt {int 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 21.145. 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 21.146. 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 example will output:
rows inserted: 17
See Also
mysqli_stmt_num_rows
|
mysqli_prepare
|
Copyright 1997-2008 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 (method):
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-2008 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 (method):
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 21.11. 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 unsigned
long. 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.
Copyright 1997-2008 the PHP Documentation Group.
mysqli_stmt::bind_param
mysqli_stmt_bind_param
Binds variables to a prepared statement as parameters
Description
Object oriented style (method):
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:
Table 21.12. 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 21.147. 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 21.148. 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 example 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-2008 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 (method):
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 21.149. 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 21.150. 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 example 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-2008 the PHP Documentation Group.
mysqli_stmt::close
mysqli_stmt_close
Closes a prepared statement
Description
Object oriented style (method):
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-2008 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 (method):
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 21.151. 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 21.152. 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 example will output:
City: Benin City Countrycode: NGA
See Also
mysqli_prepare
|
Copyright 1997-2008 the PHP Documentation Group.
mysqli_stmt->errno
mysqli_stmt_errno
Returns the error code for the most recent statement call
Description
Object oriented style (property):
mysqli_stmt {int 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 21.153. 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 21.154. 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 example will output:
Error: 1146.
See Also
mysqli_stmt_error
|
mysqli_stmt_sqlstate
|
Copyright 1997-2008 the PHP Documentation Group.
mysqli_stmt->error
mysqli_stmt_error
Returns a string description for last statement error
Description
Object oriented style (property):
mysqli_stmt {string 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 21.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(); } $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 21.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(); } 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 example will output:
Error: Table 'world.myCountry' doesn't exist.
See Also
mysqli_stmt_errno
|
mysqli_stmt_sqlstate
|
Copyright 1997-2008 the PHP Documentation Group.
mysqli_stmt::execute
mysqli_stmt_execute
Executes a prepared Query
Description
Object oriented style (method):
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 21.157. 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 21.158. 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 example will output:
Stuttgart (DEU,Baden-Wuerttemberg) Bordeaux (FRA,Aquitaine)
See Also
mysqli_prepare
|
mysqli_stmt_bind_param
|
Copyright 1997-2008 the PHP Documentation Group.
mysqli_stmt::fetch
mysqli_stmt_fetch
Fetch results from a prepared statement into the bound variables
Description
Object oriented style (method):
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 21.13. 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 21.159. 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 21.160. 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 example 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-2008 the PHP Documentation Group.
mysqli_stmt->field_count
mysqli_stmt_field_count
Returns the number of field in the given statement
Description
mysqli_stmt {int field_count ;
}
int mysqli_stmt_field_count(mysqli_stmt stmt);
This function is currently not documented; only its argument list is available.
Copyright 1997-2008 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 (method):
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-2008 the PHP Documentation Group.
mysqli_stmt::get_warnings
mysqli_stmt_get_warnings
Get result of SHOW WARNINGS
Description
object mysqli_stmt::get_warnings(mysqli_stmt stmt);
object mysqli_stmt_get_warnings(mysqli_stmt stmt);
This function is currently not documented; only its argument list is available.
Copyright 1997-2008 the PHP Documentation Group.
mysqli_stmt->insert_id
mysqli_stmt_insert_id
Get the ID generated from the previous INSERT operation
Description
mysqli_stmt {int insert_id ;
}
mixed mysqli_stmt_insert_id(mysqli_stmt stmt);
This function is currently not documented; only its argument list is available.
Copyright 1997-2008 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 (property):
mysqli_stmt {int 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 21.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(); } $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 21.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(); } $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 example will output:
Number of rows: 20.
See Also
mysqli_stmt_affected_rows
|
mysqli_prepare
|
mysqli_stmt_store_result
|
Copyright 1997-2008 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 (property):
mysqli_stmt {int 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 21.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(); } 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 21.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(); } 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 example will output:
Statement has 2 markers.
See Also
mysqli_prepare
|
Copyright 1997-2008 the PHP Documentation Group.
mysqli_stmt::prepare
mysqli_stmt_prepare
Prepare a SQL statement for execution
Description
Object oriented style (method)
mixed mysqli_stmt::prepare(string query);
Procedure 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.
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.
You should not add a terminating semicolon or
\g
to the statement.
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 21.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(); } $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 21.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(); } $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 example 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-2008 the PHP Documentation Group.
mysqli_stmt::reset
mysqli_stmt_reset
Resets a prepared statement
Description
Object oriented style (method):
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-2008 the PHP Documentation Group.
mysqli_stmt::result_metadata
mysqli_stmt_result_metadata
Returns result set metadata from a prepared statement
Description
Object oriented style (method):
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:
mysqli_num_fields
mysqli_fetch_field
mysqli_fetch_field_direct
mysqli_fetch_fields
mysqli_field_count
mysqli_field_seek
mysqli_field_tell
mysqli_free_result
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 21.167. 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 21.168. 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-2008 the PHP Documentation Group.
mysqli_stmt::send_long_data
mysqli_stmt_send_long_data
Send data in blocks
Description
Object oriented style (method)
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 21.169. 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-2008 the PHP Documentation Group.
mysqli_stmt::sqlstate
mysqli_stmt_sqlstate
Returns SQLSTATE error from previous statement operation
Description
Object oriented style (property):
mysqli_stmt {string 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 21.170. 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 21.171. 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 example will output:
Error: 42S02.
See Also
mysqli_stmt_errno
|
mysqli_stmt_error
|
Copyright 1997-2008 the PHP Documentation Group.
mysqli_stmt::store_result
mysqli_stmt_store_result
Transfers a result set from a prepared statement
Description
Object oriented style (method):
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 21.172. 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 21.173. 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 example will output:
Number of rows: 20.
See Also
mysqli_prepare
|
mysqli_stmt_result_metadata
|
mysqli_stmt_fetch
|
User Comments
Add your own comment.