Query with an array

[DS928]

I have a query that is almost working. What is giving me troulble is the third condition $Filter. This is based on a multi-select listbox. Basically the query should return the results where "$Food", "$Place" and "$Filter" are met. "$Food" and "$Place" are single select listboxes, and "$Filter" is a multi-select. I can't use the "IN" clause, that makes an "OR" statement. I need it to be an"AND" statement.

$Food  = ($_POST['Food']);
$Place = ($_POST['Place']);
$Filter = ($_POST['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, 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";

[simplypixie]

You can use "IN", it doesn't make it an OR statement and I am not sure why you think it does.

AND tblLocDet.DetailID IN ('$string')

[DS928]

Thank you for the answer. The query is still a litle off. According to what is in the database. If you select. Afghan, NYC, Buffet and Fireplace The correct result should be Afghan Kebab House. If you select Afghan, NYC ,Fireplace the correct result should be Afghan Kebeb House / Afghan Kebeb House II If you select Afghan, NYC, Fireplace and Private Rooms, the correct result should be Afghan Kebeb House II. This is the one that is wrong, I am getting both Kebab houses.

[simplypixie]

Well (if I am understanding what you have posted correctly) it will return both Kebab Houses as one of the search elements is still Fireplace and both have a Fireplace according to what you have written.

However, if you are saying that both Fireplace and Private Rooms should match then you need to change your query to allow AND for each different search term to match all of them and therefore using IN as I suggest won't work. So you need to get your array of Filters split into multiple AND statements.

The code you will need to change (not tested):

$string = implode(' AND tblLocDet.DetailID=',$Filter);

AND tblLocPlace.PlaceID = '$Place'
{$string}
GROUP BY tblLocations.LocationID 

[DS928]

Thank you. You are absolutely right in your logic. I will try the code and get back to you.