problems with LIMIT and specific row return

[bennystylee]

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



// 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";


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



// 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";


I want to understand what is happening and why I cant quite grasp this. Any help much appreciated

Thanks

[skywalker2208]

If you do a count of how many records their are then you could do LIMIT 10, variable_with_total_number_of_records

[bennystylee]

do you have a code example? or a hint as to the syntax for this?

[bennystylee]

how do i count my records? (sorry real noobie here) well actually if you dont know the syntax you dont know it......

[bennystylee]

the ideal would be as you said to count the amount of records then do a limit but how can i do this in the second statement whilst also using count to get the amount of posts per month?

[skywalker2208]

SELECT COUNT(arti) FROM blog

I assume that arti is the blog id.

[bennystylee]

this wont work with the above query?



//select the rows after 10 (and display in a different way)

$sql = "SELECT COUNT(arti) AS arti_count,
DATE_FORMAT(create, '%M %Y') AS monyr,
MONTH(create) AS mon,
YEAR(create) AS yr,
COUNT(*) AS cnt
FROM blog
WHERE arti_count > 10
GROUP BY monyr
ORDER BY yr DESC, mon";



and gives me the error "Warning: mysqli_error() expects exactly 1 parameter, 0 given in"

[skywalker2208]

You will have 3 queries. The second one is going to be the count of how many rows of data there are in the blog table. Before you run the third query you will need to get the results of the second query and store it in a variable. It would be something like what I have posted below. I have not tested so there could be some errors, but it should give you the basics of what I was explaining.


// 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";


$result = mysql_query("SELECT COUNT(arti) FROM blog;");
$row = mysql_fetch_row($result);


// 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
GROUP BY monyr
ORDER BY yr DESC, mon"
LIMIT 10, $row[0];

[bennystylee]

i think due to the group by this actually limits the monyr amount rather than the count of the articles.

So i get less monyr displayed (or monyr from row 10) display which isnt right. What i need is to be able to limit the articles (arti) but also keep the grouping of years and months for me displaying as such.

You may find this useful i have posted my full code here

http://www.codingforums.com/showthread.php?p=770783#post770783

[bennystylee]

solved see

http://www.codingforums.com/showthread.php?p=770892#post770892

thanks for your help