I have created a mysql database which is searchable through a webpage.
I can search by keyword and area and want to be able to search by category but am struggling to do this.
I have 11 categories which are fields cat1-cat11 in my mysql database. In my HTML search form there is a category drop-down box so you can select 1 category which would relate to e.g. cat1. This would return all entries where the cat1 field has a value of >1
Can anyone suggest how to do this?
I have included the code I have so far to make my question clearer:
HTML SEARCH FORM:
<form method="post" action="Search.php">
<select name="cat" class="search_input">
<option value="0" selected>ANY TYPE</option>
<option value="1">Training, Education and Employment</option>
<option value="2">Education, Advice and Information</option>
<option value="3">Emergency Accommodation</option>
<option value="4">Supported Accommodation</option>
<option value="5">Housing Support</option>
<option value="6">Long Term Housing and Accommodation</option>
<option value="7">Counselling</option>
<option value="8">Capacity Building</option>
<option value="9">Mental Health</option>
<option value="10">Drug/Alcohol Support</option>
<option value="11">Other</option>
</select>
<select name="la" class="search_input">
<option value="0" selected>ANY REGION</option>
<option value="1">National</option>
<option value="2">Outwith Scotland</option>
<option value="4">Aberdeen City</option>
<option value="5">Aberdeenshire</option>
<option value="6">Angus</option>
<option value="7">Argyll & Bute</option>
<option value="8">Clackmannanshire</option>
<option value="9">Dumfries & Galloway</option>
<option value="10">Dundee City</option>
<option value="11">East Ayrshire</option>
<option value="12">East Dunbartonshire</option>
<option value="13">East Lothian</option>
<option value="14">East Renfrewshire</option>
<option value="15">Edinburgh City</option>
<option value="16">Eilean Siar</option>
<option value="17">Falkirk</option>
<option value="18">Fife</option>
<option value="19">Glasgow City</option>
<option value="20">Highland</option>
<option value="21">Inverclyde</option>
<option value="22">Midlothian</option>
<option value="23">Moray</option>
<option value="24">North Ayrshire</option>
<option value="25">North Lanarkshire</option>
<option value="26">Orkney Islands</option>
<option value="27">Perth & Kinross</option>
<option value="28">Renfrewshire</option>
<option value="29">Scottish Borders</option>
<option value="30">Shetland Islands</option>
<option value="31">South Ayrshire</option>
<option value="32">South Lanarkshire</option>
<option value="33">Stirling</option>
<option value="34">West Dunbartonshire</option>
<option value="35">West Lothian</option>
</select><br>
<font face="Arial, Helvetica, sans-serif" size="2" color="#000000"> Keywords:<input type="text" name="search" size=25 maxlength=25>
<input type="Submit" name="Submit" value="Submit"><input type="reset" name="Clear" value="Clear">
</form>
SEARCH PHP:
<?php
mysql_connect ("localhost", "root", "purple1");
mysql_select_db ('youth_services');
$cat=$POST["cat"];
$la=$POST["la"];
$search=$_POST["search"];
$result = mysql_query("SELECT * FROM details WHERE org OR gptext LIKE '%$search%' AND $la='0'
OR org OR gptext LIKE '%$search%' AND la LIKE '$la'");
if(mysql_num_rows($result)>0)
while($r=mysql_fetch_array($result))
{
$org=$r["org"];
$address1=$r["address1"];
$address2=$r["address2"];
$address3=$r["address3"];
$city=$r["city"];
$pcode=$r["pcode"];
$tel=$r["tel"];
$fax=$r["fax"];
$email=$r["email"];
$web=$r["web"];
$name=$r["name"];
$surname=$r["surname"];
$gptext=$r["gptext"];
$image=$r["image"];
$date=$r["date"];
$cat1=$r["cat1"];
$cat2=$r["cat2"];
$cat3=$r["cat3"];
$cat4=$r["cat4"];
$cat5=$r["cat5"];
$cat6=$r["cat6"];
$cat7=$r["cat7"];
$cat8=$r["cat8"];
$cat9=$r["cat9"];
$cat10=$r["cat10"];
$cat11=$r["cat11"];
$other=$r["other"];
echo "<tr><td width=25><td><font face=Arial, Helvetica, sans-serif size=3><b>$org</b> <br> <font face=Arial, Helvetica, sans-serif size=2>$address1 $address2 $address3 <br> $city <br> $pcode <br> $tel
<br> $fax <br> $email <br> $web <br> Contact Name: $name $surname <br> Date of entry: $date <br><br> <b> Areas Relevant to Service:<br></b>";
if($cat1>1)print "Training, Education and Employment<br>";
if($cat2>1)print "Education, Advice and Information<br>";
if($cat3>1)print "Emergency Accommodation<br>";
if($cat4>1)print "Supported Accommodation<br>";
if($cat5>1)print "Housing Support<br>";
if($cat6>1)print "Long Term Housing and Accommodation<br>";
if($cat7>1)print "Counselling<br>";
if($cat8>1)print "Capacity Building <br>";
if($cat9>1)print "Mental Health<br>";
if($cat10>1)print "Drug/Alcohol Support <br>";
if($cat11>1)print "Other:<br>";
if($cat11>1)print "$other";
echo"</td><td width='200'><font face=Arial, Helvetica, sans-serif size=2><img src='$image' width='100'><p><b>Good Practice Example:</b><br>$gptext</td></tr><tr><td><td><hr><td></tr>";}
else {echo "<tr><td height=25><td><font face=Arial, Helvetica, sans-serif size=2>Sorry, no results found ";
}
?>
THE CATEGORIES IN THE HTML INPUT FORM:
<td width="150"><b> Type of service:</b><tr><td width="25">
<td width="150">
Training, Education and Employment <td><select name="cat1">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Education, Advice and Information <td><select name="cat2">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Emergency Accommodation <td><select name="cat3">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Supported Accommodation <td><select name="cat4">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150">Housing Support <td><select name="cat5">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Long Term Housing and Accommodation <td><select name="cat6">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Counselling <td><select name="cat7">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Capacity Building <td><select name="cat8">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Mental Health <td><select name="cat9">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Drug/Alcohol Support <td><select name="cat10">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
<tr><td width="25">
<td width="150"> Other<td><select name="cat11">
<option value="1">Not relevant</option>
<option value="2">Relevant to some extent</option>
<option value="3">Very relevant</option>
</select>
