Joins

[doddsey_65]

i have a recent topics box on my homepage which shows the five most recent topics. this is the query to display them:

 

$query = $link->query("SELECT t.t_name, t.t_poster, t.t_time_posted, t.t_views, t.t_replies, t.t_last_poster, t.t_last_post_time, 
                                        u.u_avatar,
                                        f.f_name
        FROM ".TBL_PREFIX."topics t
        JOIN ".TBL_PREFIX."users u
        ON (u.u_username = t.t_poster)
        JOIN ".TBL_PREFIX."forums as f
        ON (t.t_fid = f.f_fid)
        ORDER BY t_time_posted DESC
        LIMIT 5")or die(print_link_error());
        
        $result = $query->fetchAll();

 

the problem is the url needs to be:

./category/the_category/forum/the_forum/topic/the_topic

 

but with this code i can only pull the topic name and the forum name due to the database.

i dont have a seperate table for categories. instead i have forums with a pid of 0 to denote a category and all

forums within the category have a pid which is equal to the fid of the forum category:

 

name       fid     pid
category   1       0  
forum       2       1
another    3       1

and so on.

 

so how would i be able to get the category name aswell without running another query within my foreach loop?

i tried joining the forums table again as c but that wouldnt help because the result would be looking for the name and it would appear twice within the query.

 

Thanks

[colap]

Does this help?

http://colalinux.blogspot.com/2010/11/sql-tutorial.html