table joins

[doddsey_65]

im trying to improve my code by using table joins but when i use the following code it just returns everything in the database rather than the results that equal $forum_id which is the get value 2. There should only be 2 results.

 

 $query = $db->query("SELECT 
                    ".DB_PREFIX."topics.topic_id,
                    ".DB_PREFIX."topics.topic_name,
                    ".DB_PREFIX."topics.topic_poster,
                    ".DB_PREFIX."topics.topic_time_posted,
                    ".DB_PREFIX."topics.topic_views,
                    ".DB_PREFIX."topics.topic_replies,
                    ".DB_PREFIX."topics.topic_last_poster,
                    ".DB_PREFIX."topics.topic_last_post_time,
                    ".DB_PREFIX."topics.topic_locked,
                    ".DB_PREFIX."topics.topic_sticky,
                    
                    ".DB_PREFIX."parents.parent_id,
                    ".DB_PREFIX."parents.parent_name,
                    
                    ".DB_PREFIX."forums.forum_name,
                    
                    ".DB_PREFIX."members.user_username,
                    ".DB_PREFIX."members.user_group
                    
                    FROM ".DB_PREFIX."topics
                    
                     JOIN ".DB_PREFIX."members
                     JOIN ".DB_PREFIX."parents
                     JOIN ".DB_PREFIX."forums
                    
                    ON ".DB_PREFIX."topics.topic_poster 
                    = ".DB_PREFIX."members.user_username
                    
                    WHERE ".DB_PREFIX."forums.forum_id
                    = ".$forum_id."
                    
                    ORDER BY ".DB_PREFIX."topics.topic_time_posted $max")
                    or trigger_error("SQL", E_USER_ERROR);

[mikosiko]

because your select is doing a couple of JOINS without conditions ... this part has an incorrect format that cause the behavior that you are seeing

 

 

                    FROM ".DB_PREFIX."topics
                     JOIN ".DB_PREFIX."members
                     JOIN ".DB_PREFIX."parents
                     JOIN ".DB_PREFIX."forums
                    ON ".DB_PREFIX."topics.topic_poster 
                    = ".DB_PREFIX."members.user_username

 

it should be something like this (adjust to your table descriptions)

                    FROM ".DB_PREFIX."topics
                     JOIN ".DB_PREFIX."members 
                               ON ".DB_PREFIX."topics.topic_poster  = ".DB_PREFIX."members.user_username
                     JOIN ".DB_PREFIX."parents  ON ".DB_PREFIX."<complete here> = ".DB_PREFIX."<complete here>
                     JOIN ".DB_PREFIX."forums   ON ".DB_PREFIX."<complete here> = ".DB_PREFIX."<complete here>