rcouser Posted May 1, 2008 Share Posted May 1, 2008 Hello I currently have two tables as below and I want to pull the information and display like (Location) Listing_title eg (London) Welcome to London how do I get the Location_id from the Listing table to compare with the Location table and then select the Location Location Location_id Location Listing Listing_id Location_id Listing_title Listing_description Thanks Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 1, 2008 Share Posted May 1, 2008 Try: SELECT Listing.*,Location.Location FROM Listing,Location WHERE Listing.Listing_id=Location.Location_id Quote Link to comment Share on other sites More sharing options...
psychowolvesbane Posted May 1, 2008 Share Posted May 1, 2008 SQL for first part: $sql = "SELECT Location_id FROM Location WHERE Location='London'"; $rs = mysql_query($sql); //(If you have a connection you need to have ($sql,$conn) After using the mysql_query function you set the mysql_fetch_array function in a While loop. While($row = mysql_fetch_array($rs)) { $LocationID = $row['Location_id']; $sql2 = "SELECT * FROM Listing WHERE Location_id ='$LocationID'"; $rs2 = mysql_query($sql2); $row2 = mysql_fetch_array($rs2); $ListingID = $row2['Listing_id']; $ListingTitle = $row2['Listing_title']; $ListingDesc = $row2['Listing_Description']; } Quote Link to comment Share on other sites More sharing options...
craygo Posted May 1, 2008 Share Posted May 1, 2008 $sql = "SELECT loc.Location, lis.Listing_id, lis.Listing_title, lis.Listing_description FROM Location loc JOIN Listing lis ON loc.Location_id = lis.Location_id"; no need to do 2 queries If you want to make sure you get all the location even though there is no matching listing use LEFT JOIN rather than JOIN Ray Quote Link to comment Share on other sites More sharing options...
rcouser Posted May 1, 2008 Author Share Posted May 1, 2008 Thank for all the help guys, I got it working by modifying GingerRobot's post SELECT listing_id, listing_title, listing_description, location FROM listing, location WHERE listing.location_id = location.location_id Much appreciated. 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.