Selecting data from two tables

[rcouser]

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

[GingerRobot]

Try:

 

SELECT Listing.*,Location.Location FROM Listing,Location WHERE Listing.Listing_id=Location.Location_id

 

 

[psychowolvesbane]

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'];

}

[craygo]

$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

[rcouser]

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.