Page 1 of 1

problems with LIMIT and specific row return

Posted: Thu Jan 15, 2009 4:08 pm
by chidge
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

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

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";
 
I want to understand what is happening and why I cant quite grasp this. Any help much appreciated

Thanks

Re: problems with LIMIT and specific row return

Posted: Sat Jan 17, 2009 11:40 am
by scaraffe
You can use limit for Displayin the articles above 10 too. As u probably know limit takes two arguments
limit offset,number of records
So wat u can do it pass 0 as the first argument, which would point to the first record. For the second argument pass (number of rows) - 10
So this code might do it:

Code: Select all

$count = mysql_query("select count(*) from table");
$count = $count - 10;
$sql = "SELECT arti,
          DATE_FORMAT(create, '%M %Y') AS monyr,
          MONTH(create) AS mon,
          YEAR(create) AS yr,
          COUNT(*) AS cnt
          FROM blog
          LIMIT 0, $count";