I have a tour search application developed in PHP and MySQL. The search function is working properly. But I need assistance for a query that I have. The search function works with three parameters: Region, Country and Duration. Based on what a user selects it shows correct result.
The PHP code and MySQL Structure which I am using allows me to assign only one country, region or duration to a particular tour. If I am adding more than one country, region or duration it shows No Tours Found for both the values. For example if a Tour Named: Indochina Tour is listed in two countries: Cambodia and Thailand, and someone searches for tours in Cambodia, Indochina tour doesnt show up. Same when someone searches with Thailand. But if the tour would have been listed only under one country say Cambodia, it will show up when somebody searches for Cambodia tour.
I want to have multiple countries, region or duration for any tour. If a Tour say Tour Africa is listed under two countries with id 6 and 9. I would put both the countries in the country column as 6,9 and can get correct result while searching. The tour should appear when I search for country id 6 or for 9
This is my database table
This is my PHP Code:
<?php
mysql_connect("localhost", "root", "");
mysql_select_db("byp");
if(isset($_POST['submit'])){
$region=$_POST['region'];
$country=$_POST['country'];
$duration=$_POST['duration'];
//define the index for the All option
$optionAllValue = 0; //add here the option index value used for the 'All' option
//define the where clause for the query
//in order to avoid many conditions verifications, we start it as 1=1
$whereClause = "1=1";
//now we check if the option selected for each field is not the value defined for the option 'All'
//this is just an example, and the best would be to create a function to avoid the replication of code
if($region != $optionAllValue)
{
$whereClause = $whereClause." and region='$region'";
}
if($country != $optionAllValue)
{
$whereClause = $whereClause." and country='$country'";
}
if($duration != $optionAllValue)
{
$whereClause = $whereClause." and duration='$duration'";
}
$query = "select * from byp_tour where ".$whereClause;
//original query select * from byp_tour where region='$region' and country='$country' and duration='$duration'"
$tour = mysql_query($query);
$tourNum = mysql_num_rows($tour);
if($tourNum >0){
while($result=mysql_fetch_array($tour)){
$tour_name = $result['tour_name'];
$tour_detail = $result['tour_detail'];
echo "Tour Name: $tour_name";
echo "<br />";
echo "Tour Detail: $tour_detail";
echo "<br />";
echo "<br />";
echo "<br />";
}
}
else{
echo "No Tour Found";
echo "<br />";
echo "<br />";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>BYP Test</title>
</head>
<body>
<form action="searchtest1.php" method="post" target="_blank">
<div>
<label>Region</label>
<select id="region" name="region">
<option value="0">All</option>
<option value="1">South East Asia</option>
<option value="2">Africa</option>
<option value="3">Europe</option>
<option value="4">America</option>
<option value="5">Australia</option>
</select>
</div>
<div>
<label>Country</label>
<select id="country" name="country">
<option value="0">All</option>
<option value="1">Cambodia</option>
<option value="2">Thailand</option>
<option value="3">Vietnam</option>
<option value="4">Myanmar</option>
<option value="5">Laos</option>
<option value="6">Ethiopia</option>
<option value="7">France</option>
<option value="8">New York City</option>
<option value="9">Melbourne</option>
</select>
</div>
<div>
<label>Duration</label>
<select id="duration" name="duration">
<option value="0">All</option>
<option value="1">5 Days</option>
<option value="2">10 Days</option>
</select>
</div>
<input type="submit" name="submit" value="submit" />
</form>
</body>
