Solved

MySQL Query on multi-checkboxes

Posted on 2013-02-05
5
621 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
  • 2
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
arnold earned 500 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 77

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can i make performance tuning to my sql query? 6 47
PHP Sessions in WordPress 8 31
echo paypal data on screen 5 52
UPDATE query not working in mysqli php 8 51
Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

777 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