www.webdeveloper.com
Results 1 to 5 of 5

Thread: Query with an array

Hybrid View

  1. #1
    Join Date
    Mar 2013
    Posts
    3

    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.

    Code:
    $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";

  2. #2
    Join Date
    Aug 2012
    Posts
    155
    You can use "IN", it doesn't make it an OR statement and I am not sure why you think it does.
    Code:
    AND tblLocDet.DetailID IN ('$string')

  3. #3
    Join Date
    Mar 2013
    Posts
    3
    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
    Posts
    155
    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);

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

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



Recent Articles