tomhoad Posted August 8, 2010 Share Posted August 8, 2010 I am not too experienced with MySQL and I've read a few tutorials on JOIN and UNION but can't get my head around it to be honest - can someone help me out please? I'm fairly sure I need to combine these queries to get my desired result... I have 2 tables, one called promo and another called items. What I want is... This is a Promotional [title] I am the promotional description for this promo! [description] Items in this promotional are: - Item 1 - Item 2 - Item 3 Code so far: $result = mysql_query("SELECT * FROM promo WHERE id = '$promo_id'") or die('Error : ' . mysql_error()); while($row = mysql_fetch_array($result)) { echo "<p>".$row['title']."</p>"; echo "<p>".$row['description']."</p>"; } $result = mysql_query("SELECT * FROM item WHERE promo_id = '$promo_id'") or die('Error : ' . mysql_error()); while($row = mysql_fetch_array($result)) { echo "<p>".$row['filename']."</p>"; } Using MySQL 5. Thanks for your help Link to comment https://forums.phpfreaks.com/topic/210158-multiple-queries/ Share on other sites More sharing options...
kgenly Posted August 8, 2010 Share Posted August 8, 2010 I think the mySQL you are looking for is something like... SELECT * FROM promo JOIN item ON promo.id = item.promo_id WHERE promo.id='$promo_id' The important part is ON promo.id = item.promo_id. This tells mysql to take your item table and merge the items together with their promotions, but only where the promo_id field matches an id of a promotion on the promo table. Link to comment https://forums.phpfreaks.com/topic/210158-multiple-queries/#findComment-1096752 Share on other sites More sharing options...
tomhoad Posted August 9, 2010 Author Share Posted August 9, 2010 Thanks for that. I have made some progress but I am still struggling slightly. Believe me I've worked on this for a few hours now and can't crack it!!! Maybe I'm just too simple! My php now looks like so: $result = mysql_query("SELECT * FROM promo JOIN items ON promo.id = items.promo_id WHERE promo.id='$promo_id'") or die('Error : ' . mysql_error()); while($row = mysql_fetch_array($result)) { echo "<p>".$row['promo.title']."</p>"; echo "<p>".$row['description']."</p>"; echo "<p>".$row['filename']."</p>"; } The outcome is: Description test 1 filename.jpg Description test 1 filename_2.jpg I don't seem to be able to access the Promo Title at all ? Also it's repeating and I can sort of see why from the PHP - what do you recommend to just get my desired outcome (OP). Link to comment https://forums.phpfreaks.com/topic/210158-multiple-queries/#findComment-1097191 Share on other sites More sharing options...
sasa Posted August 10, 2010 Share Posted August 10, 2010 try to change $row['promo.title'] to $row['title'] Link to comment https://forums.phpfreaks.com/topic/210158-multiple-queries/#findComment-1097265 Share on other sites More sharing options...
tomhoad Posted August 10, 2010 Author Share Posted August 10, 2010 Still the same - I have a 'title' column in both tables, perhaps that's where the problem lies? Link to comment https://forums.phpfreaks.com/topic/210158-multiple-queries/#findComment-1097493 Share on other sites More sharing options...
fenway Posted August 10, 2010 Share Posted August 10, 2010 Still the same - I have a 'title' column in both tables, perhaps that's where the problem lies? Probably -- use a column alias. Link to comment https://forums.phpfreaks.com/topic/210158-multiple-queries/#findComment-1097555 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.