problems with LIMIT and specific row return

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
chidge
Forum Commoner
Posts: 29
Joined: Sat May 10, 2008 4:03 pm

problems with LIMIT and specific row return

Post 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
scaraffe
Forum Newbie
Posts: 3
Joined: Fri Jan 16, 2009 1:06 pm

Re: problems with LIMIT and specific row return

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