[SOLVED] Filtering MYSQL by multiple possibilities

[sloth456]

Ok I've tried searching the forum and couldn't find quite what I was looking for

 

I have a mysql database with about 15 fields, 4 of them are:

 

com

couk

org

net

 

On the user interface, (my form) I have a 4 checkboxes for each, i.e

 

com

couk

org

net

 

The user can check any, none or all of those checkboxes in order to filter the records they see for records where a .com domain is available or a .co.uk domain is available, or both, etc etc.

 

I'm having trouble with the backend coding though. I'll illustrate with some code.

 

If the user checks no boxes, the code is simple

 

$sql="SELECT * FROM tkeywords";

 

The trouble is when they start checking boxes.  If they check one box I need to use 1 WHERE clause, but if they do more than one I must use a WHERE clause first and then precede with AND statements.

 

This is what I want to do

 

$sql="SELECT * FROM tkeywords";

if(isset($_GET['com']=="on"))
{
$sql.=" WHERE com='1';
}

if(isset($_GET['couk']=="on"))
{
$sql.=" WHERE couk='1';
}

if(isset($_GET['org']=="on"))
{
$sql.=" WHERE org='1';
}

if(isset($_GET['net']=="on"))
{
$sql.=" WHERE net='1';
}

 

Of course that would only work if just one checkbox was selected, but if for example 2 where selected the SQL would end up as

 

SELECT * tkeywords WHERE com='1' WHERE couk='1'

 

which is incorrect and would not work, the second where clause and all after that need to be AND.

 

Is there anyone who has had to do something similar and come up with an easy logic, or am I really going to have to write out a seperate sql statement for every possible combination of checkboxes on?

[cags]

Something like this perhaps?

 

$domains = array();
$sql="SELECT * FROM tkeywords";

if(isset($_GET['com']=="on")){
   $domains[] = 'com';
}

if(isset($_GET['couk']=="on")){
   $domains[] = 'couk';
}

if(isset($_GET['org']=="on")){
   $domains[] = 'org';
}

if(isset($_GET['net']=="on")){
   $domains[] = 'net';
}

$clause = "";
if(!empty($domains) {
   $clause = " WHERE " . $domains[0] . "='1'";
   $domain_count = count($domains);
   for($i = 1; $i < $domain_count; ++$i) {
      $clause = " OR " . $domains[$i] . "='1'";
   }
}

$sql .= $clause;

 

For a better solution though you could have your HTML checkboxes named domains[], with the values of the actual domain extention, then you could jsut loop through the array and not need all the isset items.

[sloth456]

thanks I think that should do the trick