V Posted July 28, 2010 Share Posted July 28, 2010 I'm having some trouble echoing a join query. I'm querying two tables: categories and subcats. First I used 2 loops to query all the categories and their respective subcategories (subcats). It works fine, look.. $sql = "SELECT * FROM categories ORDER BY cat_name"; //categories $cats_result = $connection->query($sql) or die(mysqli_error($connection)); while ($row = $cats_result->fetch_assoc()) { $cat_id = $row['cat_id']; $cat_name = $row['cat_name']; echo "<strong>".$cat_name."</strong><br />"; $sql = "SELECT * FROM subcats WHERE cat_id = '$cat_id'"; //subcategories $subcat_result = $connection->query($sql) or die(mysqli_error($connection)); while ($row = $subcat_result->fetch_assoc()) { $subcat_name = $row['subcat_name']; echo $subcat_name; echo "<br />"; }//end subcats while loop }//end category while loop The output is.. Technology Computers Gadgets Robots Health Fitness Diet Now I'm trying to make everything more compact by joining the two tables in one query. $sql = "SELECT categories.*, subcats.* FROM categories, subcats WHERE categories.cat_id = subcats.cat_id ORDER BY cat_name"; $cats_result = $connection->query($sql) or die(mysqli_error($connection)); while ($row = $cats_result->fetch_assoc()) { $cat_id = $row['cat_id']; $cat_name = $row['cat_name']; $subcat_name = $row['subcat_name']; //should output all categories once and nest their subcategories echo "<strong>".$cat_name."</strong>"; echo "<br />"; echo $subcat_name; echo "<br />"; } The output I get is.. Technology Computers Technology Gadgets Technology Robots Health Fitness Health Diet Will I always get this with joins or am I doing something terribly wrong? Quote Link to comment Share on other sites More sharing options...
Alex Posted July 28, 2010 Share Posted July 28, 2010 That's not a JOIN at all.. Using a JOIN your query might look something like this: SELECT categories.*, subcats.* FROM categories JOIN subcats on (categories.cat_id = subcats.cat_id) ORDER BY cat_name Quote Link to comment Share on other sites More sharing options...
V Posted July 28, 2010 Author Share Posted July 28, 2010 Thanks Alex! I was under teh impression that join means when you query 2 tables at once. I used the query you provided but I get the exact output like my query without join. Quote Link to comment Share on other sites More sharing options...
Alex Posted July 28, 2010 Share Posted July 28, 2010 The data returned is correct. You just need to loop over it with PHP in the correct manner, but that's more of a question suited for the PHP coding help forum. Quote Link to comment Share on other sites More sharing options...
V Posted July 28, 2010 Author Share Posted July 28, 2010 Ok, that makes sense! I'll re-post in the correct section. Thanks! 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.