CrazeD Posted September 3, 2008 Share Posted September 3, 2008 I'm having trouble with a MySQL JOIN query. I'm still dabbing with making forums, and someone suggested in my other thread to use a JOIN query to only use one query, instead of two (for categories and subcategories). I can't quite get this to work as it's supposed to. Here's my table structure: categories cat_id | cat_title 0 | category 1 1 | category 2 forums (sub categories) forum_id | cat_id | forum_title 0 | 0 | forum 1 1 | 0 | forum 2 3 | 1 | forum 3 So, forum 1 & 2 should be displayed under category 1, and forum 3 should be displayed under category 2. However, this is not the case. It is displaying like this: category 1 forum 1 category 1 forum 2 category 2 forum 3 It's displaying category 1 twice, I guess because there is more forums than categories. Here is my code; which I found on a tutorial: SELECT c.*, f.* FROM categories=c, forums=f WHERE c.cat_id = f.cat_id I've tried LEFT JOIN, RIGHT JOIN, etc and can't make this work. Thanks. Quote Link to comment Share on other sites More sharing options...
Fadion Posted September 3, 2008 Share Posted September 3, 2008 Give this a go: SELECT cat.cat_id, cat.cat_title, for.forum_id, for.cat_id, for.forum_title FROM categories AS cat INNER JOIN forums AS for ON cat.id=for.cat_id ORDER BY cat.cat_title Quote Link to comment Share on other sites More sharing options...
gaza165 Posted September 3, 2008 Share Posted September 3, 2008 Here is a simpler version of GuiltyGears code.. SELECT * FROM CATEGORIES INNER JOIN FORUMS ON CATEGORIES.cat_id=FORUMS.cat_id Quote Link to comment Share on other sites More sharing options...
CrazeD Posted September 3, 2008 Author Share Posted September 3, 2008 Tried both of your suggestions, gets me the same result. Quote Link to comment Share on other sites More sharing options...
Fadion Posted September 3, 2008 Share Posted September 3, 2008 What is the PHP code you're using? You can post here the part where you make the query and fetch the data. Quote Link to comment Share on other sites More sharing options...
CrazeD Posted September 3, 2008 Author Share Posted September 3, 2008 $sql = mysql_query ("SELECT cat.cat_id, cat.cat_title, for.forum_id, for.cat_id, for.forum_title FROM categories AS cat INNER JOIN forums AS for ON cat.cat_id = for.cat_id") while ($row = mysql_fetch_array ($sql)) { echo $row['cat_title'].'<br />'; echo $row['forum_title'].'<br />'; } Quote Link to comment Share on other sites More sharing options...
DarkWater Posted September 3, 2008 Share Posted September 3, 2008 Check out the GROUP BY clause. Quote Link to comment Share on other sites More sharing options...
CrazeD Posted September 4, 2008 Author Share Posted September 4, 2008 Can you be more specific? Quote Link to comment Share on other sites More sharing options...
aschk Posted September 4, 2008 Share Posted September 4, 2008 What you're asking for can't be achieved by SQL you need to perform this using PHP. The SQL result you are getting back is perfectly fine in each case, what you're attempting to do is show the results in a categorised fashion. i.e. the resultset you are getting is as follows: forum_id | cat_title | forum_title 0 | category1 | forum 1 1 | category2 | forum 2 3 | category2 | forum 3 This is PERFECT, as there is no such thing as nested presentation in SQL. Results are 2D datasets. Anyway to resolve your problem you need to do the following in PHP: // Dataset container $data = array(); // SQL query string $query_string = " SELECT f.forum_id ,c.cat_title ,f.forum_title FROM categories c JOIN forums f ON c.cat_id = f.cat_id ORDER BY cat_title "; // This gives an alphabetical result by category. // Get SQL result. $result = mysql_query($query_string); // Build dataset from result. while($row = mysql_fetch_assoc($result)){ $data[] = $row; } // Perform presentation $current_cat = ""; foreach($data as $d){ if($d['cat_title'] != $current_cat){ $current_cat = $d['cat_title']; echo $current_cat . "<br/>"; } echo $d['forum_title'] . "<br/>"; } [code] The above will need some tweaking for the particular of the presentation (i.e. tabulated, or line breaks or whatever html you have), but you get the gist of it hopefully. [/code] Quote Link to comment Share on other sites More sharing options...
CrazeD Posted September 4, 2008 Author Share Posted September 4, 2008 Thanks, that works. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.