is it possible to make multiple fetch() actions on a single instance of PDOStatement Object?

Question

example:

$query = $mydb->query('PRAGMA table_info("mytable")');
//variable query has now type of PDOStatement Object
print_r($query->fetchAll(PDO::FETCH_COLUMN,1)); // this result is ok
print_r($query->fetchAll(PDO::FETCH_COLUMN,2)); // but this result gives empty array

so is there any way to reuse statement object ?

Answer 1

A PDOStatement Object returns false when there are no more rows available. The call to fetchAll covers all rows which would then always return false on any following attempt to fetch. This limits the reuse of the statement object. You can use the PDOStatement::bindColumn to achieve what it looks as if you are attempting in your example.

$query = $mydb->query('PRAGMA table_info("mytable")');
// Variables passed to be bound are passed by reference.
// Columns are based on a *1* index or can be referenced by name.
$query->bindColumn(1, $bound_column1);
$query->bindColumn(2, $bound_column2);

$query->fetchAll();
print_r($bound_column1);
print_r($bound_column2);

Answer 2

Sure, you can re-use the statement object.

But think about this for a moment: If you already have fetched all ^{PHP Manual}, why do you expect that the second fetch all statement can still return something?

print_r($query->fetchAll(PDO::FETCH_COLUMN,1)); // this result is ok
print_r($query->fetchAll(PDO::FETCH_COLUMN,2)); // but this result gives empty array

PDO has a cursor that advances. Once at the end, there is nothing you can do but close the cursor ^{PHP Manual} and execute again.

You can re-use the statement object for that.

But I don't think you want/need to actually do that for your code. Instead, fetch all columns and then access them in the returned data by their index:

print_r($query->fetchAll(PDO::FETCH_NUM)); // this result is ok

You will see in the output that you now have 0-indexed column numbers per each row, so you can access the columns within the return data by their column-index - row per row.