Solved

Multi Checkboxes returning seperate results

Posted on 2013-02-05

1 solution

370 Views

Last Modified: 2013-02-06

I have a form with 43 checkboxes on it. I want to be able to select as many checkboxes as I wish and return a result with whatever meets that criteria. Right now its returning a result for each checkbox. This is what I have so far. The checkbox code.

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

Open in new window

The PHP Code.

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

The link:
www.menuhead.net/Steelers/deep_search.php

In case you want to see first hand.

Open in new window

Comment

Question by:DS928

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

9 Comments

LVL 20

Expert Comment

by:Mark Brady

ID: 388579032013-02-05

When you send an array to php (in this case your checkbox values) you are only sending an array not an associative array (ie: $key => $value).

So I believe this line

foreach($_POST['criteria'] as $key=>$value){

should be this

foreach($_POST['criteria'] as $value){

as there are only single values in that array.

give that a try.

LVL 20

Expert Comment

by:Mark Brady

ID: 388579062013-02-05

You can always var_dump your posted values and see what you have.

var_dump($_POST['criteria']);

or dump them all

var_dump($_POST);

Author Comment

by:DS928

ID: 388579312013-02-05

I tried this and it didn't change a thing.

foreach($_POST['criteria'] as $value){

Open in new window

Perhaps this needs some kind of loop?

LVL 82

Expert Comment

by:leakim971

ID: 388579472013-02-05

try this :

<?php
	include "config.php";
	if(!empty($_POST["criteria"])){ 
		$arr = array();
		foreach($_POST['criteria'] as $key=>$value) $arr[] = "'" . mysql_real_escape_string($value) . "'";
		$criteria .= implode(",", $arr);
		if(!$rs=mysql_query("SELECT tblLocations.CityID, tblRestaurants.RestName, tblLocations.Street, 
				tblLocations.Phone, tblLocations.Price, tblLocations.Rating
            	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

Author Comment

by:DS928

ID: 388579912013-02-05

Hmmmmmm, this is what I got.

http://www.menuhead.net/Steelers/deep_search.php

Check Fireplace and Great Views, SEARCH.

LVL 82

Expert Comment

by:leakim971

ID: 388580432013-02-05

you've :

 <div id="result">
  	Cannot parse query</table><br />

Open in new window

so put the sql query in a string first and replace :
echo "Cannot parse query";
by :
echo "Cannot parse query : " . $sql; // $sql is the string where you put the query so you can locate the error and do a try in a sql editor (phpmyadmin for example)

LVL 16

Accepted Solution

by:Rose Babu

Rose Babu earned 500 total points

ID: 388583162013-02-05

Can you try the below code ?

update the DB details and check the result.

<!DOCTYPE unspecified PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>

<style type="text/css">
.tblHead {
	background-color: #002DD1;
	color: white;
	text-align: left;
}
</style>

</head>
<body>

<?php

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;
	}
	echo "<br><br>";
	
	echo $criteria . "<br><br>";
	
	// Test DB Connection	---	Modify it and test once
	$con = mysql_connect ( "localhost", "username", "password" );
	if (! $con) {
		die ( 'Could not connect: ' . mysql_error () );
	}
	mysql_select_db ( "your_db_name", $con );
	
	$mysqlQuery = "SELECT tblLocations.CityID, tblRestaurants.RestName, tblLocations.Street, ";
	$mysqlQuery .= "tblLocations.Phone, tblLocations.Price, tblLocations.Rating ";
	$mysqlQuery .= "FROM (tblRestaurants INNER JOIN tblLocations ON tblRestaurants.RestID = tblLocations.RestID) ";
	$mysqlQuery .= "INNER JOIN (tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID) ";
	$mysqlQuery .= "ON tblLocations.LocationID = tblLocDet.LocationID ";
	$mysqlQuery .= "WHERE tblLocations.CityID='16' AND tblDetails.DetailName IN (" . $criteria . ") ";
	$mysqlQuery .= "ORDER BY tblRestaurants.RestName ASC ";

	// Make sure you have formatted the MySQL query correctly especially in the inner join
	//	SELECT 
	//	tblLocations.CityID, 
	//	tblRestaurants.RestName, 
	//	tblLocations.Street, 
	//	tblLocations.Phone, 
	//	tblLocations.Price, 
	//	tblLocations.Rating 
	//	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 ('Buffet', 'Brunch', 'Cateringt', 'Great Views', 'Cheap Eats', 'Happy Hour', 'Delivery', 'Private Rooms(s)', 'Gastro-Pub', 
	//								  'Lunch', 'On-Line Ordering', 'Waterfront', 'Hotel Dining', 'Pre / Post Theatre', 'Reservations', 'Wi-Fi', 'Live Entertainment', 
	//								  'Text Ordering', 'Romantic', 'Sports Bar', 'Theme') 
	//	ORDER BY tblRestaurants.RestName ASC 
	
	
	
	if (! $rs = mysql_query ( $mysqlQuery )) {
		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>";
			echo "<td>" . $row ['Address'] . "</td>";
			echo "<td>" . $row ['Phone'] . "</td>";
			echo "<td>" . $row ['Price'] . "</td>";
			echo "<td>" . $row ['Rating'] . "</td>";
			echo "</tr>";
		}
	}
	echo "</table><br />\n";
	
	mysql_close ( $con );
}

?>

<form method="post" name="Critters" id="Critters">
<div align="left">
<table width="950" border="0" cellspacing="0" cellpadding="0">
	<tbody>
		<tr>
			<th class="tblHead" width="11" scope="col">&nbsp;</th>
			<th class="tblHead" width="19" scope="col">&nbsp;</th>
			<th class="tblHead" width="195" scope="col">FEATURES</th>
			<th class="tblHead" width="206" scope="col">MEAL PERIODS</th>
			<th class="tblHead" width="215" scope="col">SERVICES</th>
			<th class="tblHead" width="268" scope="col">TYPE OF PLACE</th>
			<th class="tblHead" width="51" scope="col">&nbsp;</th>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Buffet"
				value="Buffet"> <label for="Buffet"> Buffet</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Breakfast"
				value="Breakfast"> <label for="Breakfast"> Breakfast</label> </strong>
			</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="BYOB"
				value="BYOB"> <label for="BYOB"> BYOB</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Bar Scene"
				value="Bar Scene"> <label for="Bar Scene"> Bar Scene</label> </strong>
			</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Fireplace"
				value="Fireplace"> <label for="Fireplace"> Fireplace</label> </strong>
			</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Brunch"
				value="Brunch"> <label for="Brunch"> Brunch</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Catering"
				value="Cateringt"> <label for="Catering"> Catering</label> </strong>
			</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Business Dining" value="Business Dining"> <label
				for="Business Dining"> Business Dining</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Great Views" value="Great Views"> <label for="Great Views">
			Great Views</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Dinner"
				value="Dinner"> <label for="Dinner"> Dinner</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Credit Cards" value="Credit Cards"> <label for="Credit Cards">
			Credit Cards</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Cheap Eats"
				value="Cheap Eats"> <label for="Cheap Eats"> Cheap Eats</label> </strong>
			</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Outdoor Dining" value="Outdoor Dining"> <label
				for="Outdoor Dining"> Outdoor Dining</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Happy Hour"
				value="Happy Hour"> <label for="Happy Hour"> Happy Hour</label> </strong>
			</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Delivery"
				value="Delivery"> <label for="Delivery"> Delivery</label> </strong>
			</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Diner"
				value="Diner"> <label for="Diner"> Diner</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Private Rooms(s)" value="Private Rooms(s)"> <label
				for="Private Room(s)"> Private Rooms(s)</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Late Night"
				value="Late Night"> <label for="Late Night"> Late Night</label> </strong>
			</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Food Truck / Cart" value="Food Truck / Cart"> <label
				for="Food Truck / Cart"> Food Truck / Cart</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Gastro-Pub"
				value="Gastro-Pub"> <label for="Gastro-Pub"> Gastro-Pub</label> </strong>
			</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Raw Bar"
				value="Raw Bar"> <label for="Raw Bar"> Raw Bar</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Lunch"
				value="Lunch"> <label for="Lunch"> Lunch</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="On-Line Ordering" value="On-Line Ordering"> <label
				for="On-line Ordering"> On-Line Ordering</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Group Dining" value="Group Dining"> <label for="Group Dining">
			Group Dining</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Waterfront"
				value="Waterfront"> <label for="Waterfront"> Waterfront</label> </strong>
			</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Open 24 Hours" value="Open 24 Hours"> <label for="Open 24 Hours">
			Open 24 Hours</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="On-Line Reservations" value="On-Line Reservations"> <label
				for="On-line Reservations"> On-line Reservations</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Hotel Dining" value="Hotel Dining"> <label for="Hotel Dining">
			Hotel Dining</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Wheelchair Access" value="Wheelchair Access"> <label
				for="Wheelchair Access"> Wheelchair Access</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Pre / Post Theatre" value="Pre / Post Theatre"> <label
				for="Pre / Post Theatre"> Pre / Post Theatre</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Reservations" value="Reservations"> <label for="Reservations">
			Reservations</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Kid Friendly" value="Kid Friendly"> <label for="Kid Friendly">
			Kid Friendly</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Wi-Fi"
				value="Wi-Fi"> <label for="Wi-Fi"> Wi-Fi</label> </strong></td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Take-Out"
				value="Take-Out"> <label for="Take-Out"> Take-Out</label> </strong>
			</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Live Entertainment" value="Live Entertainment"> <label
				for="Live Entertainment"> Live Entertainment</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Text Ordering" value="Text Ordering"> <label for="Text Ordering">
			Text Ordering</label> </strong></td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="People Watching" value="People Watching"> <label
				for="People Watching"> People Watching</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Romantic"
				value="Romantic"> <label for="Romantic"> Romantic</label> </strong>
			</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Specials"
				value="Specials"> <label for="Specials"> Specials</label> </strong>
			</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Sports Bar"
				value="Sports Bar"> <label for="Sports Bar"> Sports Bar</label> </strong>
			</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox"
				id="Tasting Menu" value="Tasting Menu"> <label for="Tasting Menu">
			Tasting Menu</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Theme"
				value="Theme"> <label for="Theme"> Theme</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td><strong> <input name="criteria[]" type="checkbox" id="Trendy"
				value="Trendy"> <label for="Trendy"> Trendy</label> </strong></td>
			<td>&nbsp;</td>
		</tr>
	</tbody>
</table>
</div>
<p><input type="submit" name="btnSearch" id="btnSearch"
	value="          SEARCH          "></p>
</form>

</body>
</html>

Open in new window

Post me the result or error you got. Hope this will give you the right result.

Try it once.

Author Closing Comment

by:DS928

ID: 388593852013-02-06

I would give you a ten thousand points if I could! This is perfect! Exactly what I was looking for! Oops! Upon further testing, it returns a result of any restaurant that has any of the criteria as opposed to only that criteria. If i select credit cards, fireplace and great views, it should return the restaurants with those three criteria, but I am getting any restaurant that has any one of the three. Meaning if a restaurant has a fireplace but no credit cards, it still shows up in the results. This is so close! It has everything I want!

LVL 16

Expert Comment

by:Rose Babu

ID: 388594892013-02-06

glad to know that my solution helped you :-)

The way i coded is to get the selected checkboxes values and formatted those data to be searched using in sql operator (e.g., select * from tblName where column1 in ('chkBoxVal1','chkBoxVal2','chkBoxVal3','chkBoxValn' )

And i mentioned that "// Make sure you have formatted the MySQL query correctly especially in the inner join". to get the required result, the sql query may need to be updated i think so.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating linked dropdowns using jQuery and PHP

Article by: Julian

This article discusses how to create an extensible mechanism for linked drop downs.

Update and Secure Legacy PHP Scripts wih filter_input_array and PDO Prepared Statements

Article by: John

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.

Look For Files Using PHP

Video by: Marco

The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

Introduction to PHP: Building the Form

Video by: Jason

The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …