Further filter SQL database results (PHP) using check box

Question

I was given a coding assignment to do using MySQL codes. I have been able to do the search button and it works perfectly. But I was also asked to filter the search results using check boxes but I cannot do it. Here are the PHP codes:

The results searched are displayed in a table and using check boxes, I have to filter the results according to the RATING of the results. The check boxes are "filterstar5" and so on. How can i achieve this? I googled and Ajax seemed to be the solution but I have not learnt Ajax yet.

Thanks a lot. (I know these are old SQL codes and this is part of my assignment to learn them). This is an example of what i want: http://hibbard.eu/blog/pages/ajax-filter/

<?php
error_reporting(0);
include("config.php");
?>

<section id="reservation-form">

<form id="form1" name="form1" method="post" action="">

<label>Search City</label>
<input name="citysearch" type="text"/>

<label for="from">Check in</label>
<input name="from" type="text" id="from" size="10"/>

<label for="to">Check out</label>
<input name="to" type="text" id="to" size="10"/>

<label >Price($)</label>
<select name="pricefrom">
<option value="">--</option>
<option value="100">100</option>
<option value="150">150</option>
<option value="200">200</option>
<option value="500">500</option>
</select>

<label >To($)</label>
<select name="priceto">
<option value="">--</option>
<option value="150">150</option>
<option value="200">200</option>
<option value="250">250</option>
<option value="900">900</option>
</select>

<button type="submit">Search</button>
</form>
</section>




<h4>STARS</a></h4>
<input type="checkbox" name="filterstar5" value="5">5
<input type="checkbox" name="filterstar4" value="4">4
<input type="checkbox" name="filterstar3" value="3">3
<input type="checkbox" name="filterstar2" value="2">2
<input type="checkbox" name="filterstar1" value="1">1



<?php
if ($_REQUEST["citysearch"]<>'') {
$search_citysearch = " AND city='".mysql_real_escape_string($_REQUEST["citysearch"])."' OR country='".mysql_real_escape_string($_REQUEST["citysearch"])."'";    
}
if($_REQUEST["pricefrom"]<>'' and $_REQUEST["priceto"]<>'') {
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE price >= '".mysql_real_escape_string($_REQUEST["pricefrom"])."' AND price <= '".mysql_real_escape_string($_REQUEST["priceto"])."'".$search_citysearch;
} else if ($_REQUEST["pricefrom"]<>'') {
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE price >= '".mysql_real_escape_string($_REQUEST["pricefrom"])."'".$search_citysearch;
} else if ($_REQUEST["priceto"]<>'') {
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE price <= '".mysql_real_escape_string($_REQUEST["priceto"])."'".$search_citysearch;
}
else if($_REQUEST["from"]<>'' and $_REQUEST["to"]<>'') {
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE from_date >= '".mysql_real_escape_string($_REQUEST["from"])."' AND to_date <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_citysearch;
} else if ($_REQUEST["from"]<>'') {
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE from_date >= '".mysql_real_escape_string($_REQUEST["from"])."'".$search_citysearch;
} else if ($_REQUEST["to"]<>'') {
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE to_date <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_citysearch;
} else {
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE id>0".$search_citysearch;
}

$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) {
while ($row = mysql_fetch_assoc($sql_result)) {

?>

<table width="750" border="0" style=";border:1px solid lightblue;">
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>

<tr>
<td colspan=6 height="10"></td>
</tr>

<tr>
<td rowspan=3 align="center"><?php echo $row["images"]; ?></td>
<td colspan=1 class="mycss">
<div id="containerhotel">
<div class="tabletext"><?php echo $row["hotel"]; ?></div>
</div>
</td>
<td colspan=1></td>
<td colspan=1></td>
<td colspan=1></td>
<td colspan=1 align="center" class="tabletextstar"><?php echo $row["starimages"]; ?></td>
</tr>

<tr>
<td colspan=1 class="myButton"><?php echo $row["links"]; ?></td>
<td colspan=1></td>
<td colspan=1></td>
<td colspan=1></td>
<td colspan=1 align="center"><?php echo $row["priceimages"]; ?></td>
</tr>

<tr>
<td colspan=3 class="tabletextcity" ><?php echo $row["city"]; ?></td>
<td colspan=1></td>
<td colspan=1></td>
</tr>

<tr>
<td colspan=6 height="10"></td>
</tr>

</table>
<?php
}
} else {
?>
<p>No Hotels found, Please try another search.</p>
<?php   
}

?>

Matt · Accepted Answer · 2016-02-20 14:37:10Z

1

Generally, you can continue doing what you're doing. I'm not 100% sure exactly how you want it to work but I'll assume that for each button checked you want to find all items with a rating between up to the next number and down to the previous number.

(This is bad code, but to show you the general logic.) You could add this after your block above.

$sql .= " AND (rating = 0 ";

if ($_REQUEST["filterstar5"] == 5) {
    $sql .= " or (rating <= 5 and rating >= 4)";
}
if ($_REQUEST["filterstar4"] -= 4) {
    $sql .= " or (rating <= 4 and rating >= 3)";
}
if ($_REQUEST["filterstar3"] == 3) {
    $sql .= " or (rating <= 3 and rating >= 2)";
}
if ($_REQUEST["filterstar2"] == 2) {
    $sql .= " or (rating <= 2 and rating >= 2)";
}
if ($_REQUEST["filterstar1"] == 1) {
    $sql .= " or (rating <= 1 and rating >= 0)";
}
$sql .= ")";

answered Feb 20, 2016 at 14:37

Matt

5,4381 gold badge34 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Matter Over a year ago

No, a search is done then I will use check boxes to filter the results. Yes, i am using like a sort of rating to sort the results. The code is not working.

Matt Over a year ago

Do you want to do filtering the moment the checkbox is clicked? If so, you'll probably need to use ajax unless you are loading all the data at once without pagination, then you could do pure client side filtering.

John Matter Over a year ago

yes once it is clicked or i can make use of button "GO" also but i do not know how to do it.

Collectives™ on Stack Overflow

Further filter SQL database results (PHP) using check box

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related