crafting mysql select string by checkboxes

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
iG9
Forum Commoner
Posts: 38
Joined: Fri Jul 18, 2008 2:11 pm

crafting mysql select string by checkboxes

Post by iG9 »

Hi. I'm trying to let visitors to my site search the db for a column matching one of many checkbox selections, and return all results that match any of them. The checkbox selections are named pClass1, pClass2, pClass3... to pClass6. I'm using an if tree to append search options to the query string, but the problem is that say I write it...

Code: Select all

 
$q = "select * from properties where propID > 1 && ";
if ($pClass1) {
    $q .= "class = '$pClass1' ";
}
if ($pClass2) {
    $q .= "|| class = '$pClass2'";
}
if ($pClass3) {
    $q .= "|| class = '$pClass3'";
}
...
 
and so on, this will only work if they chose both $pClass1 and one of the higher numbered ones. If they skip $pClass1 and just choose the higher numbered ones the || will be misinterpreted. I hope I've explained myself clearly. Any ideas greatly appreciated.
Cirdan
Forum Contributor
Posts: 144
Joined: Sat Nov 01, 2008 3:20 pm

Re: crafting mysql select string by checkboxes

Post by Cirdan »

You could do something like this, put each option into an array (untested example)

Code: Select all

 
$options[] = $pClass1;
$options[] = $pClass2;
$options[] = $pClass3;
 
while($i = 0; $i < count($options); $i++)
{
    if($i != 0)
        $q .= " || ";
 
    $q .= "class = '$options[$i]'";
}
 
iG9
Forum Commoner
Posts: 38
Joined: Fri Jul 18, 2008 2:11 pm

Re: crafting mysql select string by checkboxes

Post by iG9 »

Thanks for the insight. I though something with count() would work but I couldn't get it down. Using your code as the starting point, here's what I got working...

Code: Select all

 
            $q = "select * from properties where propID > 0 ";
            
            if ($pClass1 || $pClass2 || $pClass3 || $pClass4 || $pClass5 || $pClass6) {
                $class = array();
                if ($pClass1) {
                    $class[] = $pClass1;
                }
                if ($pClass2) {
                    $class[] = $pClass2;
                }
                if ($pClass3) {
                    $class[] = $pClass3;
                }
                if ($pClass4) {
                    $class[] = $pClass4;
                }
                if ($pClass5) {
                    $class[] = $pClass5;
                }
                if ($pClass6) {
                    $class[] = $pClass6;
                }
                for ($i = 0; $i < count($class); $i++) {
                    if ($i) {
                        $q .= "|| ";
                    }
                    if (!$i) {
                        $q .= "&& ";
                    }
                    $q .= "class = '$class[$i]' ";
                }
            }
 
Cirdan
Forum Contributor
Posts: 144
Joined: Sat Nov 01, 2008 3:20 pm

Re: crafting mysql select string by checkboxes

Post by Cirdan »

Bah...it's for...not while.
:banghead:
frao_0
Forum Commoner
Posts: 27
Joined: Sat Aug 08, 2009 3:52 am
Location: Toulouse, France

Re: crafting mysql select string by checkboxes

Post by frao_0 »

Maybe use an array

Code: Select all

$q=array();
 
$possibilities=100;
 
for($i=0; $i<$possibilities; $i++)
{
   if(${"pClass$i"})
   $q[]=" `class`= '".${"pClass$i"}."'";
}
 
$q=implode('OR', $q);
 
Post Reply