Solved

MySQL Query on multi-checkboxes

Posted on 2013-02-05
5
624 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 78

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 78

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

Technology Partners: 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!

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
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 …

739 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