I have a form for users to select what they wish to view from a database (Diablo2 game items). The checkboxes are used to create "filters" for which records they view. Each record has a main category and a sub-category. For example, a sword in the database looks like this:

Category: Rare
Sub-category: Sword
Name: Name of Sword
Picture: image.jpg
Submitted by: name of person that possesses the item

The form that I am using to query the database looks like this:

<form action="query.php" method="post">
<table border="1" width="90%" align="center">
<th colspan=2>Please select your filter for viewing the database</th>
<tr>
<td width=20%>Item Type:</td>
<td>
<input type=checkbox name=category[] value="rare">Rare<br>
<input type=checkbox name=category[] value="unique">Unique<br>
<input type=checkbox name=category[] value="set">Set<br>
<input type=checkbox name=category[] value="magic">Magic<br>
</td>
</tr>
<tr>
<td>Item Class</td>
<td>
<?PHP

$connect = mysql_connect ($host, $user, $pass) or die("Unable to connect to database");

mysql_select_db("items",$connect) or die ("Unable to select data table");

$result2 = mysql_query("SELECT name FROM item_name", $connect);

$result1 = mysql_query("SELECT * FROM item_sub", $connect);

while ($row1 = mysql_fetch_row ($result1)
{
	foreach ($row1 as $rowa)
	{
		print "<input type=checkbox name=subcat[] value=$rowa>$rowa<br>\n";
	}
}

mysql_close($connect);
?>
</td>
<td></td>
</tr>
<tr>
<td colspan=3 align=center><input type=submit name=submit1 value=view><input type=reset></td>
</tr>
</form>
<tr>
<form method=post action=viewmule.php>
<td colspan=3 align=center><b>OR</b></td>
</tr>
<tr>
<td>You can just select the name of the item you wish to view</td>
<td>
<?PHP

print "<select name=name>\n";

while ($row2 = mysql_fetch_row ($result2)
{
	foreach ($row2 as $rowb)
	{
		print "<option value=$rowb>$rowb</option>\n";
	}
}

?>
<input type=submit name=submit2 value=Go></form>
</td>
</tr>
</table>

Clicking the submit button on the first form will return an array for the category[] and subcat[]checkboxes that are checked. The second form will just query based on the option that is selected in the dropdown (that query is a snap).

How do I use the array produced from the checkboxes to format my mySQL query? I am guessing that it looks something like this:

SELECT * FROM item_name WHERE $category[1] = item_cat

The hard part is how to make the query statement change based on the number of checkboxes that are actually checked? So, if someone was to check 3 boxes, the query would be different than if they checked 2 boxes or whatever. I don't want to hard-code all of the options, because there are 30+ sub categories and I really don't think it should be necessary to write 30 queries? Can someone help?

Thanks,

Jon

    How about taking the $category array and clearing out all the elements that are not chosen (some kind of for-loop and adding the used elements to a new array?) and then using implode to create a useful SQL query, eg;

    $category[0] = "cat1";
    $category[1] = "cat2";
    $category[2] = "cat3";

    if(sizeof($category)) {
    $cat_clause = " AND [cat_field]=".implode(" AND [cat_field]=", $category);
    }

    $SQL = "SELECT [field] FROM [table] WHERE [clause]".$cat_clause;

    Not sure if that's totally clear, but i'm sure you get the gist 🙂

    HTH

    Matt

      I will try it out and see what happens and will post the results in the next day or so.

      Thanks!

      Jon

        I keep getting an error. For some reason, I am getting a parse error when it runs the query. The query looks like this when the form is processed:

        SELECT * FROM [table] WHERE cat=dog AND cat=blue AND cat=green AND cat=etc

        You get the idea. For some reason, mySQL doesn't like that type of query? Is there a better way to make the query based on the inputs from the checkboxes?

        Thanks

        Jon

          Write a Reply...