JOIN SQL Statement Kind of Working

[suttercain]

Hi everyone,

 

I am trying to use the following SQL statement with a triple select list:

 

$year = $_POST['year'];
$disp = $_POST['displacement'];
$mfr = $_POST['engine_mfr'];
$matching = $year . " " . $disp . " " . $mfr;
$statement = "SELECT * FROM device, device_eo, eo, eo_engine_family, engine_family 
            WHERE device.device_id = device_eo.device_id
	    AND device_eo.executive_order = eo.executive_order
	    AND eo.executive_order = eo_engine_family.executive_order
	    AND eo_engine_family.engine_family_name = engine_family.engine_family_name
	    AND engine_family.engine_year ='".mysql_real_escape_string($year)."'
	    AND engine_family.displacement ='".mysql_real_escape_string($disp)."'
	    AND engine_family.engine_mfr ='".mysql_real_escape_string($mfr)."' ".$order."";

 

The above statement does not work, yet if I move the any two of the last three AND lines, it will work... example of when it works...

 

$year = $_POST['year'];
$disp = $_POST['displacement'];
$mfr = $_POST['engine_mfr'];
$matching = $year . " " . $disp . " " . $mfr;
$statement = "SELECT * FROM device, device_eo, eo, eo_engine_family, engine_family 
            WHERE device.device_id = device_eo.device_id
	    AND device_eo.executive_order = eo.executive_order
	    AND eo.executive_order = eo_engine_family.executive_order
	    AND eo_engine_family.engine_family_name = engine_family.engine_family_name
	    AND engine_family.displacement ='".mysql_real_escape_string($disp)."' ".$order."";

 

 

What's the dilly yo?

 

Thanks.

[fenway]

First, define "not work" -- error, or no results?

 

Second, you'd benefit from proper JOIN syntax using ON clauses; easier on the eye & the parser, since it keeps the join conditions separate from the where clause:

$statement = "SELECT * FROM device
INNER JOIN device_eo ON (  device.device_id = device_eo.device_id )
INNER JOIN eo ON ( device_eo.executive_order = eo.executive_order )
INNER JOIN eo_engine_family ON ( eo.executive_order = eo_engine_family.executive_order )
INNER JOIN engine_family ON ( eo_engine_family.engine_family_name = engine_family.engine_family_name )
WHERE engine_family.engine_year ='".mysql_real_escape_string($year)."'
AND engine_family.displacement ='".mysql_real_escape_string($disp)."'
AND engine_family.engine_mfr ='".mysql_real_escape_string($mfr)."' ".$order."";

 

But can I see the valued of $statement in both cases?