problem in fetching data from several tables in one query

Posted by Mac Taylor on Stack Overflow See other posts from Stack Overflow or by Mac Taylor
Published on 2010-05-29T10:49:02Z Indexed on 2010/05/29 10:52 UTC
Read the original article Hit count: 295

Filed under:
|
|
|

hey guys in an attempt to union my querries into one query to database , now im in need of geting username of first poster and last poster of a topic in my forums

here is my code to do as i told ::

$result = $db->sql_query("SELECT t.*,p.*,u.*

    SUM(t.topic_approved='1') AS Amount_Of_Topics,

   SUM(p.post_approved ='1') AS Amount_Of_Posts

   FROM  bb3topics t, bb3posts p, bb3users u

   GROUP BY t.topic_last_post_id

   ORDER BY t.topic_last_post_id DESC LIMIT 10
"
);


while( $row = $db->sql_fetchrow($result) )
{

    $Amount_Of_Topics = $row['Amount_Of_Topics'];
    $Amount_Of_Posts = $row['Amount_Of_Posts'];
    $Amount_Of_Topic_Replies = $Amount_Of_Topic_Replies + $row['topic_replies'];
    $Amount_Of_Topic_Views = $Amount_Of_Topic_Views + $row['topic_views'];



    $topic_id = $row['topic_id'];
    $forum_id = $row['forum_id'];
    $topic_last_post_id = $row['topic_last_post_id'];
    $topic_title = $row['topic_title'];
    $topic_poster = $row['topic_poster'];
    $topic_views = $row['topic_views'];
    $topic_replies = $row['topic_replies'];
    $topic_moved_id = $row['topic_moved_id'];
    $topic_time = $row['topic_time'];

    $result2 = $db->sql_query( "SELECT topic_id, poster_id, post_time FROM bb3posts where post_id = '$topic_last_post_id'" );
    list( $topic_id, $poster_id, $post_time ) = $db->sql_fetchrow( $result2 );


    $result3 = $db->sql_query( "SELECT username, user_id FROM bb3users where user_id='$poster_id'" );
    list( $uname, $uid ) = $db->sql_fetchrow( $result3 );
    $LastPoster = "$uname";

    $result4 = $db->sql_query( "SELECT username, user_id FROM bb3users where user_id='$topic_poster'" );
    list( $uname, $uid ) = $db->sql_fetchrow( $result4 );
    $OrigPoster = "$uname";

now i need to query all this together not in separated ones

i tried using left join but didn't worked

what mysql conjunction should i use ?!

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql