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...
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.
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?
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.**
...this is not
a bug. [...] MySQL does not support cursors and the driver cannot emulate them for you.
- bugs.php.net/bug.php?id=34625
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...
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.
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.
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.
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.
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.
Edit
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
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.
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).
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...
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.
What do you mean by "absolute index of your result set"?
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->execute(); $index = 45; while($data = $query->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT, $index)) { echo $data['id']; $index++; }
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.**
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 :(
Found this:
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:
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...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 :)
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!
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!
Once again excuse my lack of code formatting, I think the forum doesn't work so nicely with FF16 :(
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.
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
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.Edit
Just noticed that. If you're talking about
WHERE row_num = 45in 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 44All 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
lengthparameter:SELECT mycol FROM mytable LIMIT 44,18446744073709551615Yes 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!
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 ~0Note 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).