• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 628
  • Last Modified:

MySQL Query on multi-checkboxes

I am trying to do a search based on multiple check boxes.  At this point I am not getting any results.  I have this on the form.

<td><strong>
            <input name="criteria[Buffet]" type="checkbox" id="Buffet" value="1"/>
            <label for="Buffet">Buffet</label>
            </strong></td>
          <td><strong>
            <input name="criteria[Breakfast]" type="checkbox" id="Breakfast" value="1"/>
            <label for="Breakfast">Breakfast</label>
            </strong></td> etc...........

Open in new window


and this on the results page.....
<?php
			require "congig.php";
			if(isset($_POST['criteria']) && !empty($_POST['criteria'])){ 
    			foreach($_POST['criteria'] as $key=>$value){ 
        		if($value==1) $criteria[] = "'DetailName'='".mysql_escape_string($key)."'";
     			} 
    			$criteria = implode(' OR ', $criteria); 
				} 
				$query = "SELECT tblLocations.CityID, tblRestaurants.RestName, tblLocations.Street, 			
				tblLocations.Phone, tblLocations.Price, tblLocations.Rating, tblDetails.DetailName
				FROM (tblRestaurants INNER JOIN tblLocations ON tblRestaurants.RestID = tblLocations.RestID) 			
				INNER JOIN (tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID)
				ON tblLocations.LocationID = tblLocDet.LocID
				WHERE tblLocations.CityID='16'
				AND $criteria"; 

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";
	?>

Open in new window


Any help is appreciated.  Thank you.
0
DS928
Asked:
DS928
  • 2
  • 2
1 Solution
 
arnoldCommented:
Without knowing your data set,
Check what the value of criteria is
One thing is to make sure $critera is surrounded by () to avoid the first entry in criteria being ANDed while the rest OR
Output the string.
I do not beleive your notation is correct I.e. you criteria[ref] is incorrect it might have to be $criteria['breakfast']

As a means of debug output the sql string as a comment in the response.  This way you see what the query is and then work to solve it.
0
 
DS928Author Commented:
Thank you.  I am trying your suggestions. The thing is, the result page opens but it never populates or gives a message.  It just hangs.  I did these changes.

<?php
	require "congig.php";
	if(isset($_POST['criteria']) && !empty($_POST['criteria'])){ 
    	foreach($_POST['criteria'] as $key=>$value){ 
       		if($value==1) $criteria[] = "'DetailName'='".mysql_escape_string($key)."'";
    		} 
    		$criteria = implode(' OR ', $criteria); 
			} 
			if(!$rs=mysql_query("SELECT tblLocations.CityID, tblRestaurants.RestName, tblLocations.Street, 			
			tblLocations.Phone, tblLocations.Price, tblLocations.Rating, tblDetails.DetailName
			FROM (tblRestaurants INNER JOIN tblLocations ON tblRestaurants.RestID = tblLocations.RestID) 			
			INNER JOIN (tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID)
			ON tblLocations.LocationID = tblLocDet.LocID
			WHERE tblLocations.CityID='16'
			AND $criteria
			ORDER BY tblRestaurants.RestName ASC"))
			{
	echo "Cannot parse query";
	}
	elseif(mysql_num_rows($rs) == 0) {
	echo "No records found";
	}
	else {
	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";
	}
	?>

Open in new window

0
 
arnoldCommented:
Try skipping the execution and simply output the sql query as the response.
See whether the where clause you have matches the checkboxes and what you expect.

The main part I see deals with the criteria whether the database.table .
0
 
DS928Author Commented:
Solved.  I did this on the search page for the checkboxes.

<input name="criteria[]" type="checkbox" id="Buffet" value="Buffet" />

Open in new window


And this on the results page.

<?php
	include "config.php";
	if(isset($_POST['criteria']) && !empty($_POST['criteria'])){ 
    foreach($_POST['criteria'] as $key=>$value){ 
      	// Runs mysql_real_escape_string() on every value encountered.
        $clean_criteria = array_map('mysql_real_escape_string', $_REQUEST['criteria']);
        // Convert the array into a string.
        $criteria = implode("','", $clean_criteria);
		if(!$rs=mysql_query("SELECT tblLocations.CityID, tblRestaurants.RestName, tblLocations.Street, 
				tblLocations.Phone, tblLocations.Price, tblLocations.Rating, tblDetails.DetailName
            	FROM (tblRestaurants INNER JOIN tblLocations ON tblRestaurants.RestID = tblLocations.RestID)            	INNER JOIN (tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID)
				ON tblLocations.LocationID = tblLocDet.LocationID
            	WHERE tblLocations.CityID='16' AND tblDetails.DetailName IN ('$criteria')
            	ORDER BY tblRestaurants.RestName ASC"))
        {
		echo "Cannot parse query";
	}
	elseif(mysql_num_rows($rs) == 0) {
	echo "No records found";
	}
	else {
	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";
	}}
	?>

Open in new window

Thank you for your help.
0
 
aboutmuscleCommented:
Thanks for posting the solved solution this has helped me out. However does anyone know how I would go about using a pagination with this I have tried but can seem to get the pagination to work, for some reason the pagination button get removed at the bottom.

any examples of using the checkboxes code above with pagination would be a great help.

Thanks in advance
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now