problems with LIMIT and specific row return
Posted: Thu Jan 15, 2009 4:08 pm
hi all a bit of a mysql noobie here and I am having problems with the following code.
It is to return the last 10 entries from the db and then any entries above 10 to display in a different way
I have the following
the first query returns the last(most recent) 10 entries put in the database but i am having difficulty showing the entries after the last 10 rows in the second query (this shows years and then months and then a count of the articles that month in brackets) I know this must be simple - i could and have gone on arti > 10 but when articles are deleted it obviously doesnt work I need some form of row count.....
I can do this with date (below works as intended) and have the first lot from the last week and the rest displayed over a week old but i would like to do it by amount rather than dates
I want to understand what is happening and why I cant quite grasp this. Any help much appreciated
Thanks
It is to return the last 10 entries from the db and then any entries above 10 to display in a different way
I have the following
Code: Select all
// Select the first 10 posts
$lastweek = "SELECT art, titles,
DATE_FORMAT(create, '%a, %b %D, %Y') AS detim
FROM blog
ORDER BY create DESC LIMIT 10";
//select the rows after 10 (and display in a different way)
$sql = "SELECT arti,
DATE_FORMAT(create, '%M %Y') AS monyr,
MONTH(create) AS mon,
YEAR(create) AS yr,
COUNT(*) AS cnt
FROM blog
WHERE arti > 10 //this isnt working here
GROUP BY monyr
ORDER BY yr DESC, mon";
I can do this with date (below works as intended) and have the first lot from the last week and the rest displayed over a week old but i would like to do it by amount rather than dates
Code: Select all
// Select posts for this previous week
$lastweek = "SELECT article, titles,
DATE_FORMAT(create, '%a, %b %D, %Y') AS date
FROM blog
WHERE create > DATE_SUB(NOW(), INTERVAL 1 WEEK)
ORDER BY create DESC";
// Select posts grouped by month and year
$sql = "SELECT DATE_FORMAT(create, '%M %Y') AS monyr,
MONTH(create) AS mon,
YEAR(create) AS yr,
count(*) AS cnt
FROM blog
WHERE create < DATE_SUB(NOW(), INTERVAL 1 WEEK)
GROUP BY monyr
ORDER BY yr DESC, mon";
Thanks