Solved

Multi Checkboxes returning seperate results

Posted on 2013-02-05
9
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

0
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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 20

Expert Comment

by:Mark Brady
ID: 38857903
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.
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 38857906
You can always var_dump your posted values and see what you have.

var_dump($_POST['criteria']);

or dump them all

var_dump($_POST);
0
 

Author Comment

by:DS928
ID: 38857931
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?
0
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 
LVL 82

Expert Comment

by:leakim971
ID: 38857947
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

0
 

Author Comment

by:DS928
ID: 38857991
Hmmmmmm, this is what I got.

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

Check Fireplace and Great Views,   SEARCH.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 38858043
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)
0
 
LVL 16

Accepted Solution

by:
Rose Babu earned 500 total points
ID: 38858316
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.
0
 

Author Closing Comment

by:DS928
ID: 38859385
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!
0
 
LVL 16

Expert Comment

by:Rose Babu
ID: 38859489
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.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
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.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question