fenway Posted February 12, 2013 Share Posted February 12, 2013 If you post your entire code one more time, I'm going to lock this thread for good -- just post the relevant code snippet, nothing more. If you feel the urge to include the entire script, use an attachment. Link to comment Share on other sites More sharing options...
DSTR3 Posted February 12, 2013 Author Share Posted February 12, 2013 fenway. Here is the solution I came up with. First I take all of te records that meet the criteria and insert them into them into a temp table. Then I do a count of how many times the LcationID appears and compare that against the number options that were selected. Then I run the query with those that equal the number of selected options. See what you think. <form method="post" name="Critters" id="Critters"> <p align="center"> <select name> </select> <select name> </select> <select> </select> </p> <p align="center"> <select name="criteria[]" multiple="multiple"> <?php include("config.php"); ///////With Numbers///// //$sql = "SELECT DISTINCT DetailType AS type, DetailID, DetailName FROM tblDetails //ORDER BY DetailType, DetailName"; // $result = mysql_query($sql) or die(mysql_error()); // $prev=''; // while ($row = mysql_fetch_assoc($result)) { // if ($prev != $row['type']) { // if ($prev) echo "</optgroup>"; // echo "<optgroup label='{$row['type']}'>"; // $prev = $row['type']; // } // echo "<option value='".$row['DetailID']."'>".$row['DetailName']."</option>"; // } // echo "</optgroup>"; //Without Numbers///// //This builds the dropdown based on City, Area, and Cuisiner for the results_city.php file $sql = "SELECT DetailType AS type, DetailID, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|') AS DetailName FROM tblDetails GROUP BY DetailType"; $result = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_assoc($result)){ { var_dump($row); } echo "<optgroup label='{$row['type']}'>"; $DetailNames = explode('|', $row['DetailName']); foreach($DetailNames as $DetailName) { echo "<option value='".$DetailName."'>".$DetailName."</option>"; } echo "</optgroup>"; } ?> </select> </p> <p align="center"><input type="submit" name="btnSearch" id="btnSearch" value=" SEARCH "></p> </form> <p> <?php //This is for the button/////////////////////////////////// if (isset ( $_POST ["btnSearch"] )) { echo "<br>Selected Options are :<br>"; $checked = $_POST ["criteria"]; $criteria = ""; $separator = ", "; for($i = 0; $i < count ( $checked ); $i ++) { echo " " . $checked [$i] . "<br/>"; if ($i == count ( $checked ) - 1) { $separator = ""; } $criteria = $criteria . "'" . $checked [$i] . "'" . $separator; } //This gives a count of the number of options that have been selected//////////////////////// $nchecked = count($checked); echo("<p>$nchecked filter(s) HEROselected:<br>"); for($i=0; $i < $nchecked; $i++) { echo($checked[$i] . "<br/>"); } echo("</p>"); //END COUNT//////////////////////////////////// echo "<br><br>"; echo $criteria . "<br><br>"; include "config.php"; $mysqlQuery = "INSERT into tblTemp (LocationID,DetailID,DetailName) SELECT tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName FROM tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID WHERE tblDetails.DetailName IN (" . $criteria . ");"; mysql_query($mysqlQuery); $mysqlQuery2 = "SELECT tblTemp.LocationID, tblRestaurants.RestName, CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) AS Address, tblLocations.Phone, tblLocations.Price, tblLocations.Rating FROM (tblRestaurants INNER JOIN tblLocations ON tblRestaurants.RestID = tblLocations.RestID) INNER JOIN tblTemp ON tblLocations.LocationID = tblTemp.LocationID GROUP BY tblTemp.LocationID HAVING Count(tblTemp.LocationID) = '$nchecked' ORDER BY tblRestaurants.RestName"; //$mysqlQuery2 = ("SELECT tblTemp.LocationID, Count(tblTemp.LocationID) AS CountOfLocationID // FROM tblTemp // GROUP BY tblTemp.LocationID // HAVING Count(tblTemp.LocationID) = '$nchecked'"); mysql_query($mysqlQuery2); if (! $rs = mysql_query ( $mysqlQuery2 )) { echo "Cannot parse query"; } elseif (mysql_num_rows ( $rs ) == 0) { echo "No records found"; } else { echo (mysql_error()); echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\" cellspacing=\"0\">\n"; echo "<thead>\n<tr>"; echo "<th>PLACE</th>"; echo "<th>ADDRESS</th>"; echo "<th>PHONE</th>"; echo "<th>PRICE</th>"; echo "<th>RATING</th>"; echo "</tr>\n</thead>\n"; while ( $row = mysql_fetch_array ( $rs ) ) { echo"<tr> <td><strong><a href='$row[RestPage]'>$row[RestName]</a></strong></td> <td>$row[Address]</td> <td>$row[Phone]</td> <td>$row[Price]</td> <td>$row[Rating]</td> </tr>\n"; } echo "</table><br />\n"; } $mysqlQuery3 = "DELETE FROM tblTemp"; mysql_query($mysqlQuery3); mysql_close (); } ?> </p> </body> Link to comment Share on other sites More sharing options...
Barand Posted February 12, 2013 Share Posted February 12, 2013 //This builds the dropdown based on City, Area, and Cuisiner for the results_city.php file $sql = "SELECT DetailType AS type, DetailID, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|') AS DetailName FROM tblDetails GROUP BY DetailType"; $result = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_assoc($result)){ { var_dump($row); } echo "<optgroup label='{$row['type']}'>"; $DetailNames = explode('|', $row['DetailName']); foreach($DetailNames as $DetailName) { echo "<option value='".$DetailName."'>".$DetailName."</option>"; } echo "</optgroup>"; } ?> </select> You posted that bit of code in the forum as it did not work correctly (no detail id in the options). I gave you an improved version. Now that one is back in there. Why do I get the distinct feeling that our time is being wasted? Link to comment Share on other sites More sharing options...
Recommended Posts