I have 11 tables in my database and I have created a form with 5
drop down boxes, a user should be able to perform the search by selecting one or more of the 5 drop down boxes
<form name="Simple_lists_form" METHOD="post" ACTION="simplequeryprocessing.php">
<table width="640" border="0">
<tr>
<td width="140" height="62"> Type of Agency<br>
<select name="Agency_types">
<option value=" " selected></option>
<option value="AOG">AOG</option>
<option value="Authorizing Agency">Auth agency</option>
<option value="Cal-Tribe">Cal-Tribe</option>
</select> </td>
<td width="140">Type of Funding<br>
<select name="Funding_types">
<option value=" " selected> </option>
<option value="CMAQ">CMAQ</option>
<option value="TCRP">TCRP</option>
<option value="TDA-STA">TDA-STA</option>
</select> </td>
<td width="140">Type of Service<br>
<select name="Service_types">
<option value=" " selected> </option>
<option value="Commuter Rail">Commuter Rail</option>
<option value="Demand Based">Demand Based</option>
<option value="Ferry Boat">Ferry Boat</option>
</select> </td>
</tr>
<tr>
<td height="51">Located in District:<br>
<select name="Districts">
<option value=" " selected></option>
<option value="District 1">District 1</option>
<option value="District 2">District 2</option>
<option value="District 3">District 3</option>
</select> </td>
<td>Loctated in County:<br>
<select name="Counties">
<option value=" " selected> </option>
<option value="Alameda">Alameda </option>
<option value="Alpine">Alpine </option>
<option value="Amador">Amador </option>
</select> </td>
<td><input type=submit name="query" value="get results"></td>
</tr>
</table>
</form>
Here is the code to process this page
<?php
$db = mysql_connect('localhost','loginname','password') or die('Failed to connect to db server.<br>Error: ' . mysql_error());
mysql_select_db('transdata') or die('Failed to select db.<br>Error: ' . mysql_error());
?>
<?php
$sql = 'SELECT distinct agencies.agency_name
FROM districts,counties,counties_agencies, agencies,service_types,service_types_agencies,
funding_types, funding_types_agencies,agency_types,agency_types_agencies
WHERE not(agencies.agency_id ='0') ';
if ($HTTP_GET_VARS['Agency_types'])
$sql .= ' and agency_types.agency_type_id = agency_types_agencies.agency_type_id and agencies.agency_id
= agency_types_agencies.agency_id AND agency_types.agency_type="' . $HTTP_GET_VARS['Agency_types'] . '"';
if ($HTTP_GET_VARS['Funding_types'])
$sql .= ' and funding_types.funding_type_id = funding_types_agencies.funding_type_id
and agencies.agency_id = funding_types_agencies.funding_type_id AND funding_types.funding_type="' . $HTTP_GET_VARS['Funding_types'] . '"';
if ($HTTP_GET_VARS['Service_types'])
$sql .= ' and service_types.service_type_id
= service_types_agencies.service_type_id and agencies.agency_id = service_types_agencies.agency_id AND service_types.service_type="' . $HTTP_GET_VARS['Service_types'] . '"';
if ($HTTP_GET_VARS['Districts'])
$sql .= ' AND districts.district_name="' . $HTTP_GET_VARS['Districts'] . '"';
if ($HTTP_GET_VARS['Counties'])
$sql .= ' and counties.county_id = counties_agencies.county_id
and agencies.agency_id = counties_agencies.agency_id AND counties.county_name="' . $HTTP_GET_VARS['Counties'] . '"';
$res = mysql_query($sql,$db) or die('failed to execute query.<br>error:' .
mysql_error() . '<br>query: ' . $sql);
echo '<table border=1>';
while($row=mysql_fetch_row($res)){
echo"<tr>";
$field_count = count($row);
for($j=0;$j<$field_count ; $j++){
echo"<td>$row[$j]</td>";
}
echo '</tr>';
}
echo '</table>';
?>
Here the processing code does not generate any output,I understand this is because
the FROM has so many tables to be joined but there WHERE clause does not give all
the joining conditions, Can anyone please suggest a way to implement this.
Any help is greatly appreciated
Thanks
Sunder
