?
Solved

MySQL Query on multi-checkboxes

Posted on 2013-02-05
5
Medium Priority
?
626 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 79

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 79

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
In this article, we’ll look at how to deploy ProxySQL.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

765 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