homepage Welcome to WebmasterWorld Guest from 54.211.7.174
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Weird SELECT LIMIT problem
parrot




msg:4072347
 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");


Any suggestions?

Cheers,

Ton

 

rocknbil




msg:4072358
 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. :-)

parrot




msg:4072361
 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;-)

Thanx,

Ton

rocknbil




msg:4072465
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved