Jump to content


Photo

Is LIMIT the right choice?


  • Please log in to reply
4 replies to this topic

#1 sgiandhu

sgiandhu
  • Validating
  • PipPip
  • Member
  • 13 posts
  • LocationVancouver, B.C. Canada

Posted 14 November 2005 - 08:24 PM

Hi everyone.

I am using a query to place news items on a link page. Items are in the DB and I can get at them using:

SELECT * FROM tblName WHERE YEAR(articleDate) = YEAR(CURDATE()) AND MONTH(articleDate) = MONTH(CURDATE())
What I would like to also do, is limit this query to not include the 4 most recent (they are being displayed on another page).

I can of course, sort by other criteria in the DB, such as title, source ID etc, but thought this way would be the best for what I want

With my experimenting, I've found that LIMIT isn't quite what I want. Is there something else that would do what I need?

Many thanks

J

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 14 November 2005 - 08:38 PM

how many records are you going to fetch? you could specify the offset of LIMIT to 4 so it ignore the first 4 elements.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 sgiandhu

sgiandhu
  • Validating
  • PipPip
  • Member
  • 13 posts
  • LocationVancouver, B.C. Canada

Posted 14 November 2005 - 10:23 PM

[!--quoteo(post=318465:date=Nov 14 2005, 12:38 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 14 2005, 12:38 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
how many records are you going to fetch? you could specify the offset of LIMIT to 4 so it ignore the first 4 elements.
[/quote]


I'm using 5 on the main page, with a "more" link to the second page. On the second page I want to display the rest of the links, but the number becomes variable, depending on how many new items get added to the first page.

So if I do this:
SELECT * FROM tblName WHERE YEAR(itemDate) = YEAR(CURDATE()) AND MONTH(itemDate) = MONTH(CURDATE()) ORDER BY itemDate DESC LIMIT 5

Then I only get the most recent 5 ..which is great for the front page... but not what I need for the 2nd page!

Thanks.

J

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 14 November 2005 - 10:43 PM

the mysql manual suggested the following:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM tblName WHERE YEAR(itemDate) = YEAR(CURDATE()) AND MONTH(itemDate) = MONTH(CURDATE()) ORDER BY itemDate DESC LIMIT 5, 9999999999999999999 [!--sql2--][/div][!--sql3--]
this would fetch all the rows after the 5th to the 9999999999999999999999th row (which i think is more than enough)
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 sgiandhu

sgiandhu
  • Validating
  • PipPip
  • Member
  • 13 posts
  • LocationVancouver, B.C. Canada

Posted 15 November 2005 - 04:24 AM

[!--quoteo(post=318532:date=Nov 14 2005, 02:43 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 14 2005, 02:43 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
the mysql manual suggested the following:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM tblName WHERE YEAR(itemDate) = YEAR(CURDATE()) AND MONTH(itemDate) = MONTH(CURDATE()) ORDER BY itemDate DESC LIMIT 5, 9999999999999999999 [!--sql2--][/div][!--sql3--]
this would fetch all the rows after the 5th to the 9999999999999999999999th row (which i think is more than enough)
[/quote]
Perfect! Thank you. I was trying to navigate my way around the MySQL manual, but wasn't sure what to look for exactly!

As far as the script goes, it works just fine at DESC LIMIT 5, 100!

Now onto the next one... how to do the same thing for some other month!

Appreciate the help. ! Now where is that button - this one is solved.

Many thanks

J




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users