|Weird SELECT LIMIT problem|
| 11:28 pm on Feb 1, 2010 (gmt 0)|
Hey guys, I have a very strange problem. I try to query my dbase and want to display 10 records at once and then with a 'next' link the next 10 records. The problem is that when I add a limit the dabase returns none at all!
This snip returns OK
FROM $DbTable WHERE Keywords LIKE '%plop%' LIMIT 10");
This snip returns nothing at all?
FROM $DbTable WHERE Keywords LIKE '%plop%' LIMIT 1, 10");
| 11:56 pm on Feb 1, 2010 (gmt 0)|
The only reason I can think of this would happen is if you only have one record in your table.
create table blah (int(11) primary key auto_increment, keywords varchar(255));
insert into blah (keywords) values ('kaplop');
One record. Right?
select * from blah where keywords like '%plop%' limit 1,10;
-> EMPTY SET
select * from blah where keywords like '%plop%' limit 0,10;
-> | 1 | kaplop
So now you're seeing a particular weirdness that will keep you busy for a while creating your pagination - record 1 starts at index zero. But you need it to display 1 on the page.
Take this one step further, if you wind up with 100 K records in your database, you're going to need to do something to limit the # of actual pagination links on the page, otherwise it will choke the browser (been there . . . )
The best recommendation is to search cpan for a pagination library and using it to save you the pain. But if you want to tough it out, a clue is that the total number of records, order clause, limit clause, and your pagination links all work together to create an expandable pagination scheme.
I could just post my code for this but it's fairly primitive. :-)
| 12:06 am on Feb 2, 2010 (gmt 0)|
Hiya Rock, well the dbase holds 20 test records at the moment and when I remove the limit part the record with the 'plop' word shows up just fine. I have build a very complex system before and that works just the same way, it WORKS. I just dont see what I am doing wrong... tired maybe;-)
| 3:55 am on Feb 2, 2010 (gmt 0)|
I have no other explanation. Printed select to screen, does it not work on the command line? Sure it's pointed to the right table? If you have 10 records in the table and at least 1 with something containing plop, it should fly with limit 0, 10.