Results 1 to 5 of 5

Thread: Query with an array

  1. #1
    Join Date
    Mar 2013

    Query with an array

    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);
    $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
    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";

  2. #2
    Join Date
    Aug 2012
    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')

  3. #3
    Join Date
    Mar 2013
    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.

  4. #4
    Join Date
    Aug 2012
    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):
    PHP Code:
    $string implode(' AND tblLocDet.DetailID=',$Filter);

    tblLocPlace.PlaceID '$Place'
    GROUP BY tblLocations.LocationID 

  5. #5
    Join Date
    Mar 2013
    Thank you. You are absolutely right in your logic. I will try the code and get back to you.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center