Page 1 of 1
crafting mysql select string by checkboxes
Posted: Sun Aug 09, 2009 10:09 pm
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.
Re: crafting mysql select string by checkboxes
Posted: Sun Aug 09, 2009 11:38 pm
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]'";
}
Re: crafting mysql select string by checkboxes
Posted: Mon Aug 10, 2009 12:23 am
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]' ";
}
}
Re: crafting mysql select string by checkboxes
Posted: Mon Aug 10, 2009 12:45 am
by Cirdan
Bah...it's
for...not while.

Re: crafting mysql select string by checkboxes
Posted: Mon Aug 10, 2009 3:03 am
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);