www.webdeveloper.com
Results 1 to 10 of 10

Thread: problems with LIMIT and specific row return

  1. #1
    Join Date
    Jul 2008
    Posts
    69

    problems with LIMIT and specific row return

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

  2. #2
    Join Date
    Mar 2007
    Posts
    946
    If you do a count of how many records their are then you could do LIMIT 10, variable_with_total_number_of_records

  3. #3
    Join Date
    Jul 2008
    Posts
    69
    do you have a code example? or a hint as to the syntax for this?

  4. #4
    Join Date
    Jul 2008
    Posts
    69
    how do i count my records? (sorry real noobie here) well actually if you dont know the syntax you dont know it......

  5. #5
    Join Date
    Jul 2008
    Posts
    69
    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?

  6. #6
    Join Date
    Mar 2007
    Posts
    946
    PHP Code:
    SELECT COUNT(artiFROM blog 
    I assume that arti is the blog id.

  7. #7
    Join Date
    Jul 2008
    Posts
    69
    this wont work with the above query?

    Code:
    //select the rows after 10 (and display in a different way)
    
    $sql = "SELECT COUNT(arti) AS arti_count,
    	  DATE_FORMAT(create, '&#37;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"
    Last edited by bennystylee; 01-16-2009 at 03:39 AM.

  8. #8
    Join Date
    Mar 2007
    Posts
    946
    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.

    PHP Code:
    // 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]; 

  9. #9
    Join Date
    Jul 2008
    Posts
    69
    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/showthre...783#post770783

  10. #10
    Join Date
    Jul 2008
    Posts
    69

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles