CODEWITHSUNDEEP
phpmysqlpdo
Voitcus
Voitcus

Reputation: 4446

Lack of some useful functions of mysql_* in PDO

I'd like to have a MySQL query or table object, say one that takes PDO object as input and doing all the necessary stuff with the table.

I was thinking that this object could implement some array interfaces, like Countable, ArrayAccess, Iterator so I could easily foreach the rows like it was a regular array.

In deprecated mysql_* functions there was (still is, yet) very useful function mysql_data_seek(), which helped to move row-by-row, so I could implement all Iterator methods. I could run one query at the beginning and keep the rows in some resource private field and serve if I needed to.

The other functions (mostly informational, like mysql_list_tables() and similar) I can cover by sending proper query to the db and fetch the result, but how could I move to specific row?

I thought that the last parameter of the fetch() method is the cursor position, but when I call

$p = $database->query('SELECT * FROM tobjects');
$row = $p->fetch(PDO::FETCH_ASSOC, PDO::ATTR_CURSOR,$i);

it's always resulting the same row for any value of $i.

EDIT

Actually, it's not the same row, but the first row at the first call to fetch(), second row for second etc., like there was no cursor information. As suggested in answers I did this

$p = $database->prepare('SELECT * FROM tobjects',
                    array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$p->execute();
$row = $p->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $i);

but this works the same.

Upvotes: 0

Views: 108

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157941

I don't find any of these functions useful for whatever real life scenario.

mysql_list_tables() indeed can be substituted with WAY more informative and flexible query to INFORMATION SCHEMA

Why bother with implementing ArrayAccess if you can have a regular array already? There are only 2 scenarios:

  • either you need relatively small amount of data, fits for the web page. Just select it all at once.

    $data = $stmt->fetchAll();

    And you can traverse the resulting array whatever way you like.

  • or you need to traverse long table in some sort of console service script. You don't need traverse it back and forth then. Just select and loop over.

Upvotes: 0

Jon
Jon

Reputation: 437684

You are using PDO::ATTR_CURSOR wrong. What you need to do is request a scrollable cursor first by using PDO::prepare, and then you can move to any record you want in the result set:

$s = $database->prepare('SELECT * FROM tobjects',
                        [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$s->execute();
$row = $s->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $i);

Upvotes: 1

Related Questions