[RESOLVED] Query on Multiple Select
Results 1 to 5 of 5

Thread: [RESOLVED] Query on Multiple Select

  1. #1
    Junior Member
    Join Date
    Feb 2013
    Posts
    7

    resolved [RESOLVED] Query on Multiple Select

    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...

    Code:
    $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.

  2. #2
    Junior Member
    Join Date
    Feb 2013
    Posts
    7
    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";

  3. #3
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,845
    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 mysqli_escape_string on any user input you expect to stick in a query.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  4. #4
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,853
    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?
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  5. #5
    Junior Member
    Join Date
    Feb 2013
    Posts
    7
    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";

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
  •