Equivalent of mysqli_data_seek in PDO?

I wonder if there is an equivalent of mysqli_data_seek() function in PDO. I want to be able to fetch rows from a result set in an arbitrary order, for example fetch the 5th row immediately without fetching the previous 4 first. I understand that buffered queries would have to be used. Is this possible?

i think you’d be better off doing this in sql

Would you be particularly averse to fetching all of the results (PDO throws them into an array) then jumping around?

:nono: PDO is SQL

Anyway Lemon, mysql_data_seek is incorporated into PDO::statement->fetch

http://us.php.net/manual/en/pdostatement.fetch.php

Specifically, like this


// $p is a pdo object created previously

$s = $p->query($sql, $params);

// Since the row count is 0 indexed, the 5th row is 4 here.
$targetRow = $s->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT, 4);

My gut tells me though that you are wanting shuffled results. You can get the rows in random order with by including “ORDER BY RAND()” in the query, then iterate normally without having to seek arbitrarily.

on a side note ORDER BY RAND() sorts the entire result set, this doesn’t scale at all.

This coming from a guy who doesn’t know that PDO is an SQL access library. :rolleyes:

Ok, that’s a bit harsh. Still, ORDER BY RAND() does what it says. Allowing MySQL to do the shuffling is faster than the PHP shuffle function and is the preferred method of doing a row shuffle. Scaling is not an issue - the result set’s size doesn’t change whether the order is random or not. If you only want one result at random from a table ORDER BY RAND() LIMIT 1 is about as sound as you can get.

That’s also what I found but the documentation for PDO::FETCH_ORI_* is pretty scanty and I wasn’t sure what it was for - these are cursors, right? I have found information that cursors are database specific and are not implemented for mysql. Will this work with mysql?

My gut tells me though that you are wanting shuffled results. You can get the rows in random order with by including “ORDER BY RAND()” in the query, then iterate normally without having to seek arbitrarily.

Well, your guess could be right except that I don’t know yet the exact purpose now. This for my active record implementation where I send a query to the db and a set of objects is returned. Previously, it was an array of objects but later I changed it to iterator so as not to have to load all the objects into memory. So I just want to make this iterator as close to arrays as possible so I can do this:


// $result is an iterator object, suitable for foreach{}
$a = $result[4];

With mysqli I simply used mysqli_data_seek to get the desired row, but I don’t know about PDO. If cursors work with mysql is it necessary to use buffered queries?

I don’t recall ever needing using $result[$x] much so it’s not important but I’d be happy to port this to PDO, just for the sake of learning. Generally, I want to be able to access the result set like an array.

I could use fetchAll() but then all the result set would be loaded into memory - not good for large results. The nice thing about mysqli buffered queries was that the buffer was separate from the main php memory so that I could load a lot of data into the buffer without the danger of exceeding allocated php memory limit.

I think MySQL implements a cursor and that it points to the next row to be read in, but I’m not sure. I know however that PDO abstracts this sort of thing anyway. If you call for a cursor and it isn’t needed it simply won’t be used.

As for buffered vs. unbuffered, PDO can be set into buffered mode.

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY);

http://php.net/manual/en/pdo.setattribute.php

I haven’t used query buffers in awhile so my memory on them is a bit foggy, but my guess would be that you’ll need to turn buffering on, then issue the seek as you described.

Thanks for the info but it looks like this won’t work. I tried:


$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

$sql = "SELECT * FROM test_table ORDER BY id";
$stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, 4);
var_dump($row);

And I always get the first row from the set. Also, I have found this bug: http://bugs.php.net/bug.php?id=34625 - basically it means mysql doesn’t support cursors.

So I am back where I started: I’m looking for a mysqli_data_seek() for PDO buffered queries.

Michael I’m doing this mainly because I thought you were rather rude. I thought wow you’re 100% wrong, but I figured I’d give you the benefit of the doubt and try your code. Maybe you can explain to me what I did wrong:

<?php
	$dsn = 'mysql:dbname=forum;host=localhost';
    $user = REMOVED;
    $password = REMOVED;

    try {
        $dbh = new PDO($dsn, $user, $password);
		$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);		
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }
    
    $sql = 'SELECT * FROM `post`';
    $s = $dbh->query($sql);

    print '<pre>';
    $targetRow = $s->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT, 4);
    print_r($targetRow);    
?>

Now from what I gathered from your post, this should give me the fourth row in the entire result set. It didn’t but rather returned the 1st result as Lemon Juice pointed out. So there’s were you were wrong in the first point.
Next where you’re wrong again is PDO is SQL. I’ll explain this logically and prove concretely that you’re wrong. The query is execute when the function query is called, this is pretty much the documentation in the query. So clearly the 4th offset constraint is executed after the query is executed. Now since you probably don’t believe me you could turn on your general_query log in mysql, but for simplicity I included mine from the above code sample:


101029 22:44:58    11 Connect   REMOVED@localhost on forum
                           11 Query     SELECT * FROM `post`
                           11 Quit

So as you see here the query that was executed, was as expected. So my comment :

seems rather accurate.

Now on to how RAND() works and why it isn’t advised on large tables. Say you have a table with 5000 rows called mehTable. When you run the query

SELECT * from `mehTable` ORDER RAND() LIMIT 5

.
Mysql does not grab 5 different rows chosen randomly from the table. It randoms the complete 5000 rows, and selects the first five of those. See blog post here that says this. You can also read the comments in the documentation on the MySQL document page.

Lemon, getting the 4th offset:
According the MySQL documentation

"To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;"

This doesn’t seem like an idea solution.

After long searching I learned that there is no way to do data seek in PDO even if I use buffered queries. Also, there’s no equivalent to num_rows. All this means I won’t be able to do many convenient operations on my objects:


// $books will become an object that implements Iterator, Countable, ArrayAccess
$books = BookPeer::doSelect($criteria);

// count
$info = "There are " . count($books) . " displayed.";

// iterate
foreach ($books as $book) {
  $price += $book->getPrice();
}

// get the second book
$secondBook = $books[1];

// iterate again
reset($books);  // probably not needed for foreach, but can be used for key(), current(), etc.

foreach ($books as $book) {
  $weight += $book->getWeight();
}

With PDO out of all these I could only use the foreach iterator - only once. count() uses num_rows. $books[$x] uses data_seek(). reset() uses data_seek(). To implement these features with PDO I would need to use workarounds that would decrease performance and eat more memory. Therefore I decided to stay with mysqli.

It’s a pity though, because I wanted very much to use PDO but it looks like the only reason for this would be that PDO is becoming the ‘standard’ nowadays and has nicer OO interface. Apart from that it would only give me problems and more work. I’ll wait until php 6 is released - maybe PDO will be improved by then and catch up with missing features.

This is concerning to me because while I haven’t moved into the buffered query arena yet I will be doing so for the project I’m on once I reach the optimization stage. I’ve already written extending classes for PDO to add some extra methods so if necessary I’ll extend it to cover this corner case I guess. I’m just quite ready to look into it now.