Can php PDO fetch two results sets? And If yes, what is better 1 result set or more than 1?

Question

If is posible, how can I fetch two results sets:

$sth=$dbh->prepare("SELECT * FROM tb1 WHERE cond1;
                    SELECT * from tb2 Where cond2");
$sth->execute();
$row=$sth->fetchAll();  print_r ($row);

These are two completely different tables (no fiels in common).

Answer 1

Yes PDO can fetch two (or more) rowsets, as long as the database you are using supports it. I think MS SQL Server and MySQL both support this functionality, but at the time of writing SQLite does not.

The function you want is PDOStatement::nextRowset

So in your example above, you might do something like;

$sth = $dbh->prepare("SELECT * FROM tb1 WHERE cond1;
                      SELECT * FROM tb2 WHERE cond2");
$sth->execute();
$rowset1 = $sth->fetchAll();
$sth->nextRowset();
$rowset2 = $sth->fetchAll();

print_r($rowset1);
print_r($rowset2);

It's perfectly reasonable for a single stored procedure to return more than one rowset.

Answer 2

$rowset[] = $sth->fetchAll(PDO::FETCH_OBJ);
WHILE($sth->nextRowset()) {
    $rowset[] = $sth->fetchAll(PDO::FETCH_OBJ);
}

Now your $rowset will be an array. You may use count() to find out how many rowsets you have. And use foreach loop to get each rowset