krv Posted January 23, 2009 Share Posted January 23, 2009 Hello. I am having issues displaying mysql results. I have a 2 tables, 1 is categories 2 is sub categories. Categories +----+--------------+ | id | title | +----+--------------+ Sub Categories +----+-----+----------------------+ | id | cat | title | +----+-----+----------------------+ I am trying to show them like: Categories.title - sub_categories.title where sub_cat.cat = categories.id Ive tried a cross join in mysql command line but cant figure it out in php $sql_map = "SELECT ht_cat.id as hid, ht_cat.title as htitle, ht_sub_cat.id as sid, ht_sub_cat.cat as scat, ht_sub_cat.title as stitle FROM ht_cat,ht_sub_cat ORDER BY ht_cat.title ASC "; $map_result = mysql_query($sql_map) or die(mysql_error()); while ($row = mysql_fetch_array($map_result)) { foreach ($row[hid] as $row[htitle] ->$row[scat]) { echo "$row[htitle] ><br /> - $row[stitle]<br />"; } } what am I doing wrong? Quote Link to comment Share on other sites More sharing options...
revraz Posted January 23, 2009 Share Posted January 23, 2009 SELECT Sub_Categories.cat, Sub_Categories.title FROM `table` JOIN Categories on (Sub_Categories.cat = Categories.id); Like that? Quote Link to comment Share on other sites More sharing options...
krv Posted January 23, 2009 Author Share Posted January 23, 2009 Yes thank you that works. but how do i display in a while loop from mysql so it shows categories.title then sub_categories.title where sub_categories.category=categories.title or id I would assume Quote Link to comment Share on other sites More sharing options...
revraz Posted January 23, 2009 Share Posted January 23, 2009 If you post your actual table field name, I can change the code to work for you if you don't see what we're doing. $query = "SELECT Sub_Categories.cat, Sub_Categories.title, Categories.title FROM Sub_Categories JOIN Categories on (Sub_Categories.cat = Categories.id)"; $result = mysql_query ($query) or DIE (mysql_error()); while ($row = mysql_fetch_array($result)) { echo "$row['htitle'] - $row['stitle']<br />"; } Quote Link to comment Share on other sites More sharing options...
krv Posted January 23, 2009 Author Share Posted January 23, 2009 awesome last thing though how do i pull categories.title so its $row[ctitle] - $row[stitle] ? Thanks Quote Link to comment Share on other sites More sharing options...
revraz Posted January 23, 2009 Share Posted January 23, 2009 categories.title is already being selected, so just add the appropriate echo: while ($row = mysql_fetch_array($result)) { echo "$row['ctitle'] - $row['stitle']<br />"; } Quote Link to comment Share on other sites More sharing options...
krv Posted January 23, 2009 Author Share Posted January 23, 2009 what if they are the both named title in both tables? Quote Link to comment Share on other sites More sharing options...
revraz Posted January 23, 2009 Share Posted January 23, 2009 I would think you would have a few options: Use AS as you did in your first post. Use the ID of the field result instead of the name ($row[0], $row[1], $row[2]...etc) Echo the $row array to see how it's being returned. Quote Link to comment Share on other sites More sharing options...
krv Posted January 23, 2009 Author Share Posted January 23, 2009 Thanks a lot man. Quote Link to comment Share on other sites More sharing options...
krv Posted January 23, 2009 Author Share Posted January 23, 2009 Hello again. Its not selecting title from categories. +-----+----------------------+ | cat | title | +-----+----------------------+ | 4 | Competition | | 4 | Metro Italia | | 4 | Geared/ Multipurpose | | 4 | Carbon | | 4 | Neo-Classical | | 4 | Performance | | 4 | Fixed/ SS | | 4 | Steel | | 3 | Cruiser | | 3 | Comfort | | 3 | Commuter | | 1 | Freeride | | 1 | All-Mountain | | 1 | Dirt Jump | | 1 | XC Sport | +-----+----------------------+ cat should be the categorires.title for instance 1 would be MTB any suggestions. Quote Link to comment Share on other sites More sharing options...
revraz Posted January 23, 2009 Share Posted January 23, 2009 Figure it out? Quote Link to comment Share on other sites More sharing options...
krv Posted January 25, 2009 Author Share Posted January 25, 2009 yes thank you 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.