Need to load posts with last 3 comments for each post without using subqueries
Results 1 to 3 of 3

Thread: Need to load posts with last 3 comments for each post without using subqueries

  1. #1
    Senior Member
    Join Date
    Apr 2009
    Location
    Gran Canaria
    Posts
    103

    Need to load posts with last 3 comments for each post without using subqueries

    Hello,

    Looking for a CLEAN way to load posts with last 3 comments for each post without using subqueries. Is it possible to load posts and comments with the same query? Can I first load the posts with 1 query, the comments with second query and then combine both results? If yes, can you show an example?
    Any help will be appreciated. Thanks.

    Below are my script which uses subqueries. I need to get rid of subqueries because there are 20 posts per page. It means 20 queries to load comments.


    PHP Code:

    $result 
    mysql_query("SELECT posts.id, posts.text, posts.type, posts.poster, posts.comments, users.profile_picture 
    FROM posts left join users on posts.poster=users.id order by posts.id DESC limit 
    $page$step") or die('Error1'); 

    while(list(
    $pid$text$type$poster$comments$profile_picture) = mysql_fetch_row($result)) {

    //print post

    if ($comments != '0' {

    $result1 mysql_query("SELECT comments.id,comments.poster, comments.text, users.profile_picture FROM comments LEFT JOIN users ON comments.poster=users.id where comments.parent='$pid' order by comments.id DESC limit 0,3") or die('Error2'); 

    while(list(
    $cid,$cposter$ctext$cprofile_picture) = mysql_fetch_row($result1)) {

    //print comments

    }


  2. #2
    Senile Member
    Join Date
    Oct 2002
    Location
    Cheshire, UK
    Posts
    1,661
    The way to do it is to use SQL subqueries. But you have ruled those out (probably without knowing what they are)
    Barand

  3. #3
    Senile Member
    Join Date
    Oct 2002
    Location
    Cheshire, UK
    Posts
    1,661
    Here is an example which selects the top three pupil scores in each subject from a table of exam results

    The select query inside the (...) is the subquery

    Code:
    mysql> SELECT subjectid, pupilid, pcent, rowno
        -> FROM
        ->     (
        ->     SELECT pupilid, pcent,
        ->     @row := IF(@prev=subjectid, @row+1,1) as rowno,
        ->     @prev:=subjectid as subjectid
        ->     FROM result
        ->         JOIN (SELECT @row:=0, @prev:='') as init
        ->     WHERE schoolyear=2009
        ->     ORDER BY subjectid, pcent DESC
        ->     ) as countquery
        -> WHERE rowno < 4
        -> LIMIT 9;
    +-----------+---------+-------+-------+
    | subjectid | pupilid | pcent | rowno |
    +-----------+---------+-------+-------+
    |         1 |       3 |    95 |     1 |
    |         1 |      15 |    94 |     2 |
    |         1 |       9 |    93 |     3 |
    |         2 |       4 |    85 |     1 |
    |         2 |      12 |    83 |     2 |
    |         2 |      21 |    82 |     3 |
    |         3 |       5 |    96 |     1 |
    |         3 |       6 |    96 |     2 |
    |         3 |       8 |    93 |     3 |
    +-----------+---------+-------+-------+
    Barand

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
  •