Mysql_Data_Seek Pdo Equivalent

mysql_data_seek pdo equivalent

The usual answer is: do your data seek directly in the array PDOStatement::fetchAll... But it is WRONG IF the query fetches a lot of data (!).

There are 2 real solutions,

1) if database permits use PDO::FETCH_ORI_ABS or PDO::FETCH_ORI_REL,
example,

$result = $sth->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, 973);

(EDIT) But, as commented by @ChoiZ, have a PDO-MySQL limitation: "MySQL does not support cursors" (outside stored programs) "and the driver cannot emulate them for you"... Try later or with MySQL's forks, like MariaDB.

2) use the database solution (a kind of pagination). Example:

SELECT a, b FROM table LIMIT 1, 973 

how use mysql_data_seek with PDO?

the PDO 'cursor' default is PDO::CURSOR_FWDONLY that means that cursor can't back to zero like it happens with mysql_data_seek to allow cursor back to zero it necessary define use 'scrollable cursor'

example:

$db->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

before use it like this:

$row0 = $result->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, 0);

PHP PDO mysql_data_seek

From manual;

Fetches a row from a result set associated with a PDOStatement object. The fetch_style parameter determines how PDO returns the row.

You need a stmt that was created by PDO::prepare method.

Let's see this;

// assuming $pdo was created before as well
$sth = $pdo->prepare("SELECT name, colour FROM fruit");
// exec here
$sth->execute();
// get a row here
$row = $sth->fetch(PDO::FETCH_ASSOC);
// here probably you'll get a print out like
// Array([name] => Banana, [color] => Yellow)
print_r($row);

See more details here: PHP PDOStatement::fetch

how use mysql_data_seek with PDO?

the PDO 'cursor' default is PDO::CURSOR_FWDONLY that means that cursor can't back to zero like it happens with mysql_data_seek to allow cursor back to zero it necessary define use 'scrollable cursor'

example:

$db->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

before use it like this:

$row0 = $result->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, 0);

Is it possible to rewind a PDO result?

I'm pretty sure this is database dependent. Because of that, it is something you should try to avoid. However, I think you can achieve what you want by enabling buffered queries. If that doesn't work, you can always pull the result into an array with fetchAll. Both solutions have implications for your applications performance, so think twice about it, if the resultsets are large.

---

Related Topics