Is LIMIT the right choice?

[sgiandhu]

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

[ryanlwh]

how many records are you going to fetch? you could specify the offset of LIMIT to 4 so it ignore the first 4 elements.

[sgiandhu]

[!--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

[ryanlwh]

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)

[sgiandhu]

[!--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