[RESOLVED] Query on Multiple Select

WestWing

I have a query that is run off of three listboxes. One called "Food", a second called "Place" and the third a multi-select called "Filter".
Right now its returning an "OR" result. I need it to be an "AND" result. Here is my query...


$Food  = ($_POST['Food']);
$Place = ($_POST['Place']);
$Filter = ($_POST['Filter']);

// CONSTRUCT THE QUERY
$sql="SELECT tblRestaurants.RestName, tblRestaurants.RestPage,
CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) AS Address,
tblLocations.Phone, tblLocations.Price, tblLocations.Rating,
tblLocations.LocationPix, tblLocations.LocationID, tblDetails.DetailName,
tblLocDet.DetailID
FROM
tblLocations
INNER JOIN tblRestaurants ON tblRestaurants.RestID = tblLocations.RestID
INNER JOIN tblLocFood ON tblLocations.LocationID = tblLocFood.LocationID
INNER JOIN tblLocPlace ON tblLocPlace.LocationID = tblLocations.LocationID
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
WHERE tblLocFood.FoodID = '$Food'
AND tblLocPlace.PlaceID = '$Place'
AND tblLocDet.DetailID IN( '" . implode("','", $Filter) . "' ) 
GROUP BY tblLocations.LocationID
ORDER By tblRestaurants.RestName ASC
limit 100";

Here is the link if you want to see it.....it's still in production so the narrow path is "Afghan" "New York City" and for the multi-select....
Select "Buffet", "Fireplace" the result shpuld be Afghan Kebab House. Then select "Afghan" "New York City" "Fireplace", "Raw Bar" the result should be Afghan Kebab House II but it's not because its returning any restaurant that has a "Fireplace" or a "Raw Bar" or a "Buffet"

http://www.menuhead.net/Pages/placeHead.php

Help is appreciated, thank you.

WestWing

This seems closer, but it is only right 66% of the time?

$string = implode(',',$Filter);
// CONSTRUCT THE QUERY
$sql="SELECT tblRestaurants.RestName, tblRestaurants.RestPage,
CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) AS Address,
tblLocations.Phone, tblLocations.Price, tblLocations.Rating,
tblLocations.LocationPix, tblLocations.LocationID, tblDetails.DetailName,
tblLocDet.DetailID
FROM
tblLocations
INNER JOIN tblRestaurants ON tblRestaurants.RestID = tblLocations.RestID
INNER JOIN tblLocFood ON tblLocations.LocationID = tblLocFood.LocationID
INNER JOIN tblLocPlace ON tblLocPlace.LocationID = tblLocations.LocationID
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
WHERE tblLocFood.FoodID = '$Food'
AND tblLocPlace.PlaceID = '$Place'
AND tblLocDet.DetailID ='$string'
GROUP BY tblLocations.LocationID
ORDER By tblRestaurants.RestName ASC
limit 100";

sneakyimp

The WHERE clause in your SQL clearly has big fat ANDs in it so it's not at all clear what you are talking about. It's also not clear what you mean by "right 66% of the time." Try being more concise in your post?

Also, it's a bad idea to take user input (i.e., anything that is POSTed to your script) and stick it directly into a query. When you do this, your script becomes vulnerable to SQL Injection. You should use a function like [man]mysqli_escape_string[/man] on any user input you expect to stick in a query.

Weedpacket

So if the posted filters are "Buffet", "Fireplace", and "Al fresco" you only want results where "Buffet" is in the details for the given location AND "Fireplace" is in the details for the given location AND "Al fresco" is in the details for the given location?

I'm a bit confused by your database schema. What does the data being stored in tblLocDet.DetailID look like?

WestWing

This is correct. Each LocationID has many details. So the tblLocDet is connected to the table tblLocations and tblDetails is connected to the table tblLocDet. The problem is now solved though thank you. Ended up using two queries.

$Food = ($POST['Food']);
$Place = ($POST['Place']);
$Filter = ($_POST['Filter']);
$total_filter = count($Filter);
$string = implode(',',$Filter);

// CONSTRUCT THE QUERY
$sql="SELECT tblRestaurants.RestName, tblRestaurants.RestPage,
CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) AS Address,
tblLocations.Phone, tblLocations.Price, tblLocations.Rating,
tblLocations.LocationPix, tblLocations.LocationID, tblDetails.DetailName,
tblLocDet.DetailID
FROM
tblLocations
INNER JOIN tblRestaurants ON tblRestaurants.RestID = tblLocations.RestID
INNER JOIN tblLocFood ON tblLocations.LocationID = tblLocFood.LocationID
INNER JOIN tblLocPlace ON tblLocPlace.LocationID = tblLocations.LocationID
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
WHERE tblLocFood.FoodID = '$Food'
AND tblLocPlace.PlaceID = '$Place'
GROUP BY tblLocDet.LocationID
ORDER By tblRestaurants.RestName ASC
limit 100";

// RUN THE QUERY OR FAIL AND VISUALIZE WHY THERE WAS A FAILURE
$res = mysql_query($sql) or die("FAIL: $sql BECAUSE: " . mysql_error());
$LocationID = array();
while($row = mysql_fetch_object($res))
{
$LocationID[] = $row->LocationID;
//$LocationID .= $row->LocationID;
}

$LocationID = implode(',',$LocationID);

$sql="SELECT tblRestaurants.RestName, tblRestaurants.RestPage,
CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) AS Address,
tblLocations.Phone, tblLocations.Price, tblLocations.Rating,
tblLocations.LocationPix, tblLocations.LocationID, tblDetails.DetailName,
tblLocDet.DetailID
FROM
tblLocations
INNER JOIN tblRestaurants ON tblRestaurants.RestID = tblLocations.RestID
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
WHERE tblLocDet.LocationID in($LocationID)
AND tblLocDet.DetailID in ($string)
GROUP BY tblLocDet.LocationID
HAVING COUNT(tblLocDet.DetailID) = $total_filter
ORDER By tblRestaurants.RestName ASC
limit 100";