using checkbox to search for multiple criteria

[jdgieschen]

It's the age-old question of checkboxes. I've got this code:

<?php
require "connect_to_mysql.php";
// Start the SQL string
$sql = "SELECT * FROM post_sec_all_stats WHERE 1=1";

if (isset($_POST ['dura_sch'])) {
$sql .= " AND ";
$sql .= "DURA_SCH = '" .$_POST ['dura_sch']. "'";
}

if (isset($_POST['ctrl_sch'])) {
$sql .= " AND ";
$sql .= "CTRL_SCH = '" .$_POST['ctrl_sch']. "'";
}

if (isset($_POST['total_sts'][0])) {
$sql .= " AND ";
$sql .= "TOTAL_STS < 2000";
}

if (isset($_POST['total_sts'][1])) {
$sql .= " AND ";
$sql .= "TOTAL_STS BETWEEN 2000 AND 20000";
}

if (isset($_POST['total_sts'][2])) {
$sql .= " AND ";
$sql .= "TOTAL_STS > 20000";
}

$sql .= " ORDER BY UNITID ASC";
print_r($sql);


	$result = mysql_query($sql) or die("Error processing <strong>query</strong>. ".mysql_error());
	$row = mysql_fetch_assoc($result) or die("No rows returned by query");
	echo $result;
	while($booyah = mysql_fetch_object($result))
	{
		echo "<tr>";
			echo "<td><input type = 'radio' name = 'UNITID' value = '".$booyah->UNITID."'></td>";
			echo "<td>$booyah->NAME_SCH</td>";
			echo "<td>$booyah->ADD_SCH $booyah->CITY_SCH, $booyah->STATE_SCH</td>";
			echo "<td>$booyah->WEB_SCH</td>";
		echo "</tr>";
	}
?>

and the checkbox form:

<input type="checkbox" value="1" name="total_sts[]"> Small (fewer that 2000)<br />
<input type="checkbox" value="2" name="total_sts[]"> Medium (2,000 - 20,000)<br />
<input type="checkbox" value="3" name="total_sts[]"> Large (more than 20,000)<br />

It's nice and simple, because I'm an idiot, but I can't figure out how to code for searching the checkboxes. If I write it like I have it now, my search results in nothing, because obviously TOTAL_STS can't be both less than 2000, between 2000 and 20000, and over 20000 at the same time. I confirmed this by echoing out the results:

SELECT * FROM post_sec_all_stats WHERE 1=1 AND DURA_SCH = '1' AND CTRL_SCH = '1' AND TOTAL_STS < 2000 AND TOTAL_STS BETWEEN 2000 AND 20000 AND TOTAL_STS > 20000 ORDER BY UNITID ASC

SOOOoooo, if the user checks just one box, it's fine and dandy, but if they check more than one, it screws up the results. Can someone tell me how to fix this? As you can tell, I'm just starting with this whole coding thing.

[jdgieschen]

Actually, I adjusted the code a bit so it's more readable now (thanks to another great user). Goes like this:

switch ($_POST['total_sts']) {
	case '1':
		$sql .= " AND ";
		$sql .= "TOTAL_STS<2000";
		break;
	case '2':
		$sql .= " AND ";
		$sql .= "TOTAL_STS>=2000 AND TOTAL_STS<=20000";
		break;
	case '3':
		$sql .= " AND ";
		$sql .= "TOTAL_STS>20000";
		break;
}

and the form:

<input type="checkbox" value="1" name="total_sts"> Small (fewer that 2000 students)<br />
<input type="checkbox" value="2" name="total_sts"> Medium (2,000 - 20,000 students)<br />
<input type="checkbox" value="3" name="total_sts"> Large (more than 20,000 students)<br />

I, of course, still have the same issue as before. This hopefully just makes my code more readable. Anybody? Thanks.

[gamebits]

I think the issue is you are not using the right tool, checkboxes are use to give the option of multiple choices, like this and that and that while what you are trying to get is this or that or that which radio button are use for.

So my advice is use radio button instead, if you like the checkbox look than you will have to use javascript to give them the functionality of radio button.

[jdgieschen]

Thanks, but I am actually looking for the checkbox functionality. In this case, I want the user to be able to search a list of schools based on the enrollment at those schools. The options are:

1. fewer than 2,000 students
2. between 2,000 and 20,000 students
3. over 20,000 students

The user can choose none, one, two, or all three. The user might choose the first two options if he wants to search for small and medium-sized schools. Soooo, I think maybe my code is all off.

[pzuurveen]

You need a query like

SELECT * FROM post_sec_all_stats WHERE 1=1 AND DURA_SCH = '1' AND CTRL_SCH = '1' AND (TOTAL_STS < 2000 [B]OR[/B] TOTAL_STS BETWEEN 2000 AND 20000 [B]OR[/B] TOTAL_STS > 20000) ORDER BY UNITID ASC

change your input names so that it not a array

<input type="checkbox" value="1" name="Small"> Small (fewer that 2000 students)<br />
    <input type="checkbox" value="2" name="Medium"> Medium (2,000 - 20,000 students)<br />
    <input type="checkbox" value="3" name="Large"> Large (more than 20,000 students)<br />

liked your first code better, with some changes

if (isset($_POST['Small'])) {
$sql .= "AND ( ";
$sql .= "TOTAL_STS < 2000";
}
else $sql .= " AND ( FALSE ";
 
if (isset($_POST['Medium'])) {
$sql .= " OR ";
$sql .= "TOTAL_STS BETWEEN 2000 AND 20000";
}
else $sql .= " OR FALSE ";
 
if (isset($_POST['Large'])) {
$sql .= " OR";
$sql .= "TOTAL_STS > 20000 )";
}
else $sql .= " OR FALSE ) ";

[jdgieschen]

You need a query like

SELECT * FROM post_sec_all_stats WHERE 1=1 AND DURA_SCH = '1' AND CTRL_SCH = '1' AND (TOTAL_STS < 2000 [B]OR[/B] TOTAL_STS BETWEEN 2000 AND 20000 [B]OR[/B] TOTAL_STS > 20000) ORDER BY UNITID ASC

change your input names so that it not a array

<input type="checkbox" value="1" name="Small"> Small (fewer that 2000 students)<br />
    <input type="checkbox" value="2" name="Medium"> Medium (2,000 - 20,000 students)<br />
    <input type="checkbox" value="3" name="Large"> Large (more than 20,000 students)<br />

liked your first code better, with some changes

if (isset($_POST['Small'])) {
$sql .= "AND ( ";
$sql .= "TOTAL_STS < 2000";
}
else $sql .= " AND ( FALSE ";
 
if (isset($_POST['Medium'])) {
$sql .= " OR ";
$sql .= "TOTAL_STS BETWEEN 2000 AND 20000";
}
else $sql .= " OR FALSE ";
 
if (isset($_POST['Large'])) {
$sql .= " OR";
$sql .= "TOTAL_STS > 20000 )";
}
else $sql .= " OR FALSE ) ";

Thanks! You saved me twice now! I'm new to the forum, and any forum for that matter. Are you supposed to click the link for "solved" or am I? Anyway, you solved it. Thanks again! You can expect to see me on here again as I progress with this. I've learned so much already, and it makes all the difference being able to ask questions on here.

[pzuurveen]

glad to help
You started this thread, so you have to mark it solved