HEAD EXPLODING!!!! mysql query help

[severndigital]

arggghh

i want to select

the field location_name from the table location

based on

what location_id is set in the table empty_locations

i know this isn't right because it kicks out with an error, but this is what i've got.

$get_locations = mysql_query("SELECT locationid, date FROM empty_locations
          INNER JOIN location ON empty_locations.name=location.location_name");

please help out .. my eyes are bleeding ..

thanks in advance
chris

[TobyRT]

Assuming that location.id and empty_location.id are foreign keys, try:

"SELECT
l.id
l.location_name
e.locationid
e.date
FROM
locations l, empty_locations e
WHERE
l.id = e.locationid"

[tomhath]

What error are you getting? Run the query like this:

$get_locations = mysql_query("SELECT locationid, date FROM empty_locations
          INNER JOIN location ON empty_locations.name=location.location_name")
   or die ("select failed " . mysql_error()) ;

If that doesn't tell you, run the query from a command line in mysql and see what it tells you, but usually the mysql_error function is pretty clear.

[severndigital]

i get the error

Unknown column 'empty_locations.name' in 'on clause'

i tried to add the column to the table, but then i just get the white screen of death.

$get_locations = mysql_query("SELECT locationid, date FROM empty_locations
          INNER JOIN location ON empty_locations.name=location.location_name")
   or die ("select failed " . mysql_error()) 
   ;
	echo '<strong>Empty Locations</strong><BR>';
	while ($row = mysql_fetch_array($get_locations)){
		$locationid = $row['locationid'];
		$date_open = $row['date'];
		$name = $row['location_name'];

echo '<font size=2><i>Date Open:</i></font> ' . $date_open . '';

echo' <font size=2><i>Field:</i></font> ' . $name . '';
}

i guess my problem now is that i am unfamilar with how to display data from a query like that.

i've tried several variations in the while loop, but the script still only gets as far as

echo '<strong>Empty Locations</strong><BR>';

any ideas??
thanks

chris

[tomhath]

Add location_name to the columns you select.

$get_locations = mysql_query("SELECT locationid, location_name, date FROM empty_locations
          INNER JOIN location ON empty_locations.name=location.location_name")
   or die ("select failed " . mysql_error()) 
   ;

Also, I would add another line at the end (outside the while loop), to see if gets that far when there were no rows returned by the query.