Page 1 of 1

search form question

Posted: Sat Mar 19, 2005 5:59 pm
by ecaandrew
I've alaways been confused on this, how do you allow users to search with multiple choices, lets say

gender, between the ages of XX and XX, country, and zip code

how would you do the SELECT sql statement??? thanks :!!

Posted: Sat Mar 19, 2005 6:03 pm
by hawleyjr

Code: Select all

&quote;SELECT * FROM my_table where age > 15 and age < 21 and zipCode = 99223&quote;

Posted: Sat Mar 19, 2005 6:07 pm
by ecaandrew
ohhh i see, so just keep using AND statements?

Posted: Sat Mar 19, 2005 6:08 pm
by John Cartwright
you can also use OR if you want.. depends how strict you want your query to be


note: && and || are invalid in SQL.

Posted: Sat Mar 19, 2005 6:09 pm
by ecaandrew
basically not strict, like i want it to be optional

Posted: Sat Mar 19, 2005 6:11 pm
by hawleyjr
You have other options check out the mysql.com its a good reference. you can also use ()

Code: Select all

&quote;SELECT * FROM my_table where (age > 15 and age < 21)  or zipCode = 99223&quote;

Posted: Sat Mar 19, 2005 6:29 pm
by ecaandrew
sounds good, now what if you have 4 checkboxes, and have multiple choices, how would you tie that in? thanks !!

Posted: Sat Mar 19, 2005 6:34 pm
by hawleyjr
Untested:

Code: Select all

<?php
$where = '';
if(isset($_POST['checkbox'])){

if($where=='')
$where = ' WHERE ';

if($where !=' WHERE ')
$where .= ' AND ';

$where .= "myField = 1"

}

$qry = "SELECT * FROM my_table $where";
?>

Posted: Sat Mar 19, 2005 6:36 pm
by ecaandrew
awesome ill try it out :D

Posted: Sat Mar 19, 2005 6:36 pm
by John Cartwright

Code: Select all

$count = count($_POST['checkbox']);

for ($x=0;$x >= $count; $x++)
{
     $sql = "SELECT * FROM `table` WHERE ";

     if ($_POST['checkbox'][$x] == 'checked')     
     {
          $sql .= "`value` = '".$_POST['checkbox'][$x]."'";     
     
          if ($x != $count)
          {
               $sql .= ' AND ';
          }
     }
}

echo $sql;
Hope that gives you an idea..