Treehouse: Grow your CSS skills. Land your dream job.

PDO – returning absolute index with MySQL

  • # October 29, 2012 at 11:52 am

    I simply want to grab an absolute-index of my result set of a PDO object.

    I’ve read from user comments in the PHP documentation of PDO that cursors are not supported by MySQL drivers.
    [Your text to link here…]( “Here’s the page with that info”)

    Apparently the function will silently fail, and just return the result set in ‘normal’ order.
    I have no idea how to work-around this, or if I even need to do a work-around. Perhaps I’m just missing the point.

    # October 29, 2012 at 8:36 pm

    What do you mean by “absolute index of your result set”?

    # October 30, 2012 at 11:08 am

    Let’s say I have a result set of 1000 items/rows. I want to grab #45.
    According to the documentation, I should be able to pass that index number (45) into fetch().

    I did just that, and I continued to be returned the first result time and time again when iterating through the PDO object, incrementing the index with each pass.
    The following should start at row 45 and display all id’s (or whatever) after right?

    $query = $connection->conn->prepare($some_query);
    $index = 45;
    while($data = $query->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT, $index))
    echo $data;

    I get all results. It does not start at 45.

    As stated in the documentation’s comments:
    **Someone’s already pointed out that PDO::CURSOR_SCROLL isn’t supported by the SQLite driver. It’s also worth noting that it’s not supported by the MySQL driver either.

    In fact, if you try to use scrollable cursors with a MySQL statement, the PDO::FETCH_ORI_ABS parameter and the offset given to fetch() will be silently ignored. fetch() will behave as normal, returning rows in the order in which they came out of the database.**

    # October 30, 2012 at 11:09 am

    I apologize for the code formatting… I selected all the code and pressed the button above and that’s what I got.

    I also tried to bold the quote from the documentation :(

    # October 30, 2012 at 4:31 pm

    Found this:

    > …this is not
    a bug. […] MySQL does not support cursors and the driver cannot emulate them for you.

    Also, I might suggest that, if you don’t want those first forty-four rows, you could simply not select them in the first place – only select the one you want:

    $PDO->query( “SELECT cols FROM table WHERE row_num=45” );

    If you *do* need all of the rows, but want to select an arbitrary one, you could use `$PDO->fetchAll( PDO::FETCH_NUM )` to get a 0-indexed array, but that would be a fairly inefficient approach…

    # October 31, 2012 at 9:38 am

    Yes I’ve actually done fetchAll() and stored it in an array so I can grab the indexes I want. That was my work-around.

    I figured it’s more efficient to just store the group of records than doing 10+ single queries since I am needing a bunch.

    If my thought process is way off, by all means tell me I’m wrong!
    I truly appreciate your help :)

    # October 31, 2012 at 9:49 am

    If you’re querying literally a “thousand” rows, you *probably* need to find a way to refine your query.

    Likewise, if you’re using hundreds (even dozens) of rows to do some kind of calculation, you could *probably* have MySQL do the calculation and return only the result you need.

    Otherwise, by all means, get all your data at once, in as few queries as possible.

    If you have any specific questions, let me know!

    # November 1, 2012 at 11:05 am

    I am doing a pagination script.

    Using your kick in the arse, I’ve gone over my code. I did not know that MySQL had an OFFSET parameter, so that’s cool.

    When selecting the records needed the end of my query is:
    ORDER BY news.date_posted DESC
    LIMIT $max_items
    OFFSET $data_point;

    Then when doing my pagination I do a simple SELECT COUNT(id).
    I may have read somewhere that COUNT() is a bad idea… but it’s surely better than returning 1009384 rows..?

    This has helped me out in my pagination system’s efficiency quite a bit. I have to do two small queries that return only what’s needed…. rather than doing one giant query that could return all results.

    Thanks for your patience!

    # November 1, 2012 at 11:06 am

    Once again excuse my lack of code formatting, I think the forum doesn’t work so nicely with FF16 :(

    # November 2, 2012 at 12:19 am

    `COUNT(*)` isn’t that bad; as you suspect, it’s better than actually returning all those rows.

    I prefer using mysqli because (among other reasons) it has good support for executing multiple statements, via [mysqli::multi_query()]( This allows you to get the count along with your first set of records, saving you a round-trip to the database.

    This is also possible (AFAICT) using PDO, [but it’s tricky](

    # November 2, 2012 at 9:25 am

    The reason I do two queries is because I have to grab the actual page content, and then my Pagination class needs to query for the total number of records. I could include the COUNT() in my initial query, but I’m trying to keep content retrieval and pagination separate.

    Maybe I’m thinking too much haha

    # November 2, 2012 at 2:32 pm

    No, separation of concerns is always A Good Thing.

    As I said, `COUNT(*)` isn’t that bad. If you’re dealing with lots of visitors (i.e., thousands of hits an hour), you would need to start worrying about optimizing it away. Even then, however, I’d probably look at caching the result (which would also eliminate the extra round-trip) before I started changing how all the queries were organized.


    > I did not know that MySQL had an OFFSET parameter, so that’s cool.

    Just noticed that. If you’re talking about `WHERE row_num = 45` in my post above, that’s not an “offset,” it’s just a search condition like any other.

    MySQL *does* have an “offset,” however: `LIMIT {offset},{length}`. For example, if you wanted to get 30 rows, but skip the first 44, you would use:

    `SELECT mycol FROM mytable LIMIT 44,30`

    (You use 44 -**not 45**- because the row count is 0-based.)

    *Only* the first 45 rows would be like so (you’re probably familiar with this one):

    `SELECT mycol FROM mytable LIMIT 44`

    *All* of the rows, excluding the first 45, is a little more difficult – basically, MySQL doesn’t support this, other than by using “[some large number](” for the `length` parameter:

    `SELECT mycol FROM mytable LIMIT 44,18446744073709551615`

    # November 2, 2012 at 4:16 pm

    Yes I read that about mysql and it’s weird limit issue.

    There’s actually a standard number most people use and, without looking into it, I’m guessing it’s the max number of rows a MySQL table can have.

    I do use OFFSET like you described :)
    I was ‘manually’ doing this with mysql_data_seek();

    However, now that I know about OFFSET I can do just what is needed and grabbing 10-20 recordings instead of all and then seek-ing to that record.

    The reason I use OFFSET is because I do not search by id, rather row number… which is what OFFSET does. And I LIMIT my results, at least in this circumstance, because it’s a pagination thing and I only need a certain number of records anyways.

    Learn something new everyday!

    # November 2, 2012 at 4:31 pm

    No prob, glad I could help : )

    The “large number” I used above is the max size for an unsigned integer on a 64-bit system. You can query MySQL to find the largest integer your install supports:

    `SELECT ~0`

    Note that this isn’t the number of rows a table can *have* (there isn’t any specific limit), rather, how many rows you can have before your AUTO_INCREMENT field runs out of unique values (assuming its datatype is BIGINT UNSIGNED).

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.