Selecting with multiple clauses

darylr

Hi all,

Im having trouble selecting records from my database.. Basically, i want to select one row but it has to have a restriction. When a user logs in, their rank is gotten from one table in my database. Ive then put if clauses on each rank which then selects the type of aircraft they are allowed.

Then I want to be able to select a single route bearing the aircraft restriction in mind. It doesnt seem to be working using 'OR'. Here's the code: I think its the SQL syntax Ive done wrong so can anyone help?

if($row_Recordset1['rank'] == "Training Officer") { $aircraftres = "'ATR42'"; }

else if ($row_Recordset1['rank'] == "First Officer") { $aircraftres = "'ATR42' or 'ATR72'"; }
else if ($row_Recordset1['rank'] == "Senior First Officer") { $aircraftres = "'ATR42' or 'ATR72' or 'B727-200F'"; }
else if ($row_Recordset1['rank'] == "Captain") { $aircraftres = "'ATR42' or 'ATR72' or 'B727-200F' or 'A300'"; }
else if ($row_Recordset1['rank'] == "Senior Captain") { $aircraftres = "'ATR42' or 'ATR72' or 'B727-200F' or 'A300'"; }

mysql_select_db($database_conn_abrv, $conn_abrv);
$query_routes = "SELECT * FROM routes where aircraft = '$aircraftres' ORDER BY RAND() LIMIT 1";
$routes = mysql_query($query_routes, $conn_abrv) or die(mysql_error());
$row_routes = mysql_fetch_assoc($routes);
$totalRows_routes = mysql_num_rows($routes);

Thanks,

devinemke

$ranks = array(
'Training Officer' => array('ATR42'),
'First Officer' => array('ATR42', 'ATR72'),
'Senior First Officer' => array('ATR42', 'ATR72', 'B727-200F'),
'Captain' => array('ATR42', 'ATR72', 'B727-200F', 'A300'),
'Senior Captain' => array('ATR42', 'ATR72', 'B727-200F', 'A300')
);

foreach ($ranks[$row_Recordset1['rank']] as $value) {$aircraft[] = "'" . $value . "'";}
$sql = 'SELECT * FROM routes WHERE aircraft IN(' . implode(',', $aircraft) . ') ORDER BY RAND() LIMIT 1';

BobLennon

devinemke wrote:

$ranks = array(
'Training Officer' => array('ATR42'),
'First Officer' => array('ATR42', 'ATR72'),
'Senior First Officer' => array('ATR42', 'ATR72', 'B727-200F'),
'Captain' => array('ATR42', 'ATR72', 'B727-200F', 'A300'),
'Senior Captain' => array('ATR42', 'ATR72', 'B727-200F', 'A300')
);

foreach ($ranks[$row_Recordset1['rank']] as $value) {$aircraft[] = "'" . $value . "'";}
$sql = 'SELECT * FROM routes WHERE aircraft IN(' . implode(',', $aircraft) . ') ORDER BY RAND() LIMIT 1';

Nicely done!