SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Complex join

  1. #1
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex join

    I have 2 tables, members (id,name) and posts (id,title,text,posterid). I want to list the members by total posts.
    1 problem, I don't have a number of total posts in the members table. I suppose I have to work with a join query.

    So, I want something like this:

    $total_posts = mysql_num_rows(mysql_query("SELECT * FROM posts WHERE posterid='$id'"));
    mysql_query("SELECT * FROM members, posts WHERE members.id='$id' ORDER BY $total_posts");

    I know this queries are not correct, but It's my way to express what I mean

    Hope somebody can help me...

  2. #2
    SitePoint Zealot alexk's Avatar
    Join Date
    Nov 2000
    Location
    Sydney, Australia
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT members.id, COUNT(posts.id) AS posts_num FROM members, posts WHERE posts.posterid=members.id GROUP BY members.id ORDER BY posts_num DESC;

    something like that

  3. #3
    SitePoint Zealot alexk's Avatar
    Join Date
    Nov 2000
    Location
    Sydney, Australia
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you also want to see the ones who didn't post then:

    SELECT members.id, COUNT(posts.id) AS posts_num FROM members LEFT JOIN posts ON posts.posterid=members.id GROUP BY members.id ORDER BY posts_num DESC;

    tho I'm not sure if that's optimal queries, i've just started with SQL...

  4. #4
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems

    This for() has an unlimited number of counts...

    PHP Code:
    $query mysql_query("SELECT $table[members].ID, COUNT($table[posts].posterid) AS posts_num FROM $table[members]$table[posts] WHERE $table[posts].posterid=$table[members].ID GROUP BY $table[members].ID ORDER BY posts_num DESC");

    $h mysql_fetch_array($query);

    $count mysql_num_rows($query);

    for(
    $i 0$count 0$i++){
        echo 
    $h[posts_num];
        echo 
    " - ";
        echo 
    $h[username];
        echo 
    "<br>";


  5. #5
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query mysql_query("SELECT $table[members].ID, COUNT($table[posts].posterid) AS posts_num FROM $table[members]$table[posts] WHERE $table[posts].posterid=$table[members].ID GROUP BY $table[members].ID ORDER BY posts_num DESC");

    $count mysql_num_rows($query);
    $h mysql_fetch_array($query);

    for(
    $i 0$i <= $count$i++){
        echo 
    $h[posts_num];
        echo 
    " - ";
        echo 
    $h[username];
        echo 
    "<br>";
        
    $h mysql_fetch_array($query);


  6. #6
    SitePoint Zealot alexk's Avatar
    Join Date
    Nov 2000
    Location
    Sydney, Australia
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    one more fixie...

    PHP Code:
    $query mysql_query("SELECT $table[members].ID, COUNT($table[posts].posterid) AS posts_num FROM $table[members]$table[posts] WHERE $table[posts].posterid=$table[members].ID GROUP BY $table[members].ID ORDER BY posts_num DESC");

    while(
    $fres mysql_fetch_row($query))
    {
        echo 
    $fres[1], " - "$fres[0], "<br>";



Bookmarks

Posting Permissions

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