After executing a statement that returns one or more result
sets, use one of the methods available in the PDO API to iterate through
the returned rows. The PDO API also provides methods to fetch a single
column from one or more rows in the result set.
Before you begin
You
must have a statement resource returned by either the PDO::query or PDOStatement::execute method
that has one or more associated result sets.
Procedure
To fetch data from a result set:
- Fetch data from a result set by calling one of the fetch
methods:
- To return a single row from a result set as an array or object,
call the PDOStatement::fetch method.
- To return all of the rows from the result set as an array
of arrays or objects, call the PDOStatement::fetchAll method.
By default, PDO returns each row as an array indexed by the
column name and 0-indexed column position in the row. To request a
different return style, specify one of the PDO::FETCH_* constants
as the first parameter when you call the PDOStatement::fetch method:- PDO::FETCH_ASSOC
- Returns an array indexed by column name as returned in your result
set.
- PDO::FETCH_BOTH (default)
- Returns an array indexed by both column name and 0-indexed column
number as returned in your result set
- PDO::FETCH_BOUND
- Returns TRUE and assigns the values of the columns in your result
set to the PHP variables to which they were bound with the PDOStatement::bindParam method.
- PDO::FETCH_CLASS
- Returns a new instance of the requested class, mapping the columns
of the result set to named properties in the class.
- PDO::FETCH_INTO
- Updates an existing instance of the requested class, mapping the
columns of the result set to named properties in the class.
- PDO::FETCH_LAZY
- Combines PDO::FETCH_BOTH and PDO::FETCH_OBJ,
creating the object variable names as they are accessed.
- PDO::FETCH_NUM
- Returns an array indexed by column number as returned in your
result set, starting at column 0.
- PDO::FETCH_OBJ
- Returns an anonymous object with property names that correspond
to the column names returned in your result set.
If you requested a scrollable cursor when you called
the
PDO::query or
PDOStatement::execute method,
you can pass the listed optional parameters that control which rows
are returned to the caller:
- One of the PDO::FETCH_ORI_* constants that represents
the fetch orientation of the fetch request:
- PDO::FETCH_ORI_NEXT (default)
- Fetches the next row in the result set.
- PDO::FETCH_ORI_PRIOR
- Fetches the previous row in the result set.
- PDO::FETCH_ORI_FIRST
- Fetches the first row in the result set.
- PDO::FETCH_ORI_LAST
- Fetches the last row in the result set.
- PDO::FETCH_ORI_ABS
- Fetches the absolute row in the result set. Requires a positive
integer as the third argument to the PDOStatement::fetch method.
- PDO::FETCH_ORI_REL
- Fetches the relative row in the result set. Requires a positive
or negative integer as the third argument to the PDOStatement::fetch method.
- An integer requesting the absolute or relative row in the result
set, corresponding to the fetch orientation requested in the second
argument to the PDOStatement::fetch method.
- Optional: Fetch a single column from one or
more rows in a result set by calling one of the listed methods:
- To return a single column from a single row in the result
set:
Call the PDOStatement::fetchColumn method,
specifying the column you want to retrieve as the first argument of
the method. Column numbers start at 0. If you do not specify a column,
the PDOStatement::fetchColumn returns the first
column in the row.
- To return an array that contains a single column from all
of the remaining rows in the result set:
Call the PDOStatement::fetchAll method,
passing the PDO::FETCH_COLUMN constant as the first argument, and
the column you want to retrieve as the second argument. Column numbers
start at 0. If you do not specify a column, calling PDOStatement::fetchAll(PDO::FETCH_COLUMN) returns
the first column in the row.
For more information about the PDO API, see http://php.net/manual/en/book.pdo.php.
Example
Return an array indexed by column number.
$stmt = $conn->query("SELECT firstnme, lastname FROM employee");
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
print "Name: <p>{$row[0] $row[1]}</p>";
}
What to do next
When
you are ready to close the connection to the database, set the PDO
object to NULL. The connection closes automatically when the PHP script
finishes.