Jump to content

Is LIMIT the right choice?


sgiandhu

Recommended Posts

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

Link to comment
Share on other sites

[!--quoteo(post=318465:date=Nov 14 2005, 12:38 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 14 2005, 12:38 PM) 318465[/snapback][/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.

 

 

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

[!--quoteo(post=318532:date=Nov 14 2005, 02:43 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 14 2005, 02:43 PM) 318532[/snapback][/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)

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.