[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MySQL Query on multi-checkboxes

Posted on 2013-02-05
5
Medium Priority
?
627 Views
Last Modified: 2013-04-13
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
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
  • 2
  • 2
5 Comments
 
LVL 80

Accepted Solution

by:
arnold earned 1500 total points
ID: 38856606
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
 

Author Comment

by:DS928
ID: 38857221
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
 
LVL 80

Expert Comment

by:arnold
ID: 38857472
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
 

Author Comment

by:DS928
ID: 38857661
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
 

Expert Comment

by:aboutmuscle
ID: 39076316
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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