advanced search form

[thenewb]

I have looked all over google I can not find a way to dynamically build a mysql search query using only certain variables that change based on what check boxes are check on my form. So if I have 28 check boxes and a text search field. The check boxes are for diffrent systems and equipment. I use a 1 or 0 for each check box 1 if that part applies to the record 0 if it does not. I want to be able to enter text into the search field but limit the search results based on what if any check boxes had been checked. If I use

$data = mysql_query (" 
  SELECT *, 
  MATCH(content, summary) AGAINST('$keyword') AS score 
  FROM KB 
  WHERE mws = '$mws' AND power= '$power' AND smp = '$smp' AND xpient = '$xpient' AND r5000 = '$r5000' AND r5500 = '$r5500' AND r6500 = '$r6500' AND r7000 = '$r7000' AND r7500 = '$r7500' AND r7700 = '$r7700' AND r7750 = '$r7750' AND r7800 = '$r7800' AND r9000 = '$r9000' AND r160 = '$r160' AND r170 = '$r170' AND r925 = '$r925' AND r930 = '$r930' AND r950 = '$r950' AND rNCR = '$rNCR' AND pepson = '$pepson' AND pithaca = '$pithaca' AND ppanasonic = '$ppanasonic' AND k750 = '$k750' AND k800 = '$k800' AND k900 = '$k900' AND k910 = '$k910' AND klogic = '$klogic' AND kqsr = '$kqsr' AND MATCH(content, summary) AGAINST('$keyword') 
  ORDER BY score DESC 
 "); 

This will search the keyword text ok however the check boxes which apply to everything else have to all be checked exactly like they are in the record or it does not find that record. I want the user to be able to check what they want and find records with 1 or more items checked. I don't want them to have to check the exact ones that are in the database to get a return from that record. I think I need php to create dynamic query that only searches for the fields the are checked but I can't find anyway to make that query dynamic based on $_POST data from by search form.


I know that is probably extremely confusing I can't think of a way to explain it that is not confusing.

[rocknbil]

Welcome aboard thenewb, there are a few hundred examples of what I'm about to describe on this site, a search in G for rocknbil [term] site:webmasterworld.com should turn a few of mine.

First, about checkboxes: Checkboxes will **only** appear in $_POST or $_GET if they are checked. So you can start with something like this. First dispense will all your $scalar values ($smp, $xpient etc.), I'm sure it's a nightmare to maintain. You probably won't even need them with this approach. In the below example, form field names are on the left, database field names on the right.

$where = null; 
// 
$checkboxes = array( 
 'checkbox1' => 'field1', 
 'checkbox2' => 'field2', 
 'checkbox3' => 'field3' 
); // etc 
// 
foreach $checkboxes as $chk => $fieldname) { 
 if (isset($_POST[$chk]) {  
 // Don't need "and" first time through 
 if ($where) { $where .= ' and'; } 
 $where .= " $fieldname='$somevalue'"; 
 } 
} 

That gives you your where clause, if it exists. Note the use of null - it's just like the checkboxes, $where is present or it's not, so we don't have to check for empty(), just "if $where."

After you get past all the building of the where clause,

$query = "select * from table";
if ($where) { $query .= " where $where"; }

So if $where is still null at this point, it selects all records, if not, it appends $where to the query. I know this is not the same statement you're using but you can apply these concepts.

"What if I want to do something with that field even if the checkbox is not checked?"

foreach $checkboxes as $chk => $fieldname) { 
 if (isset($_POST[$chk]) {  
 if ($where) { $where .= ' and'; } 
 $where .= " $fieldname='$somevalue'"; 
 } 
 else { $where .= " $fieldname='$some_other_value'"; } 
} 

[thenewb]

Thank you for your quick reply. I was kept the "$scalar values" because they made it easier for me to keep track of the variables in my head. Using what you posted here is what I came up with. As far as I can tell it is working as I hoped it would. I am not sure if there was a better way or something I am doing that could potentially cause a problem but here is my new code:

$where = null; 
// 
$checkboxes = array( 
 'mws' => "$_POST[mws]", 
 'power' => "$_POST[power]", 
 'smp' => "$_POST[smp]",
 'xpient' => "$_POST[xpient]", 
 'r5000' => "$_POST[r5000]",
 'r5500' => "$_POST[r5500]",
 'r6500' => "$_POST[r6500]",
 'r7000' => "$_POST[r7000]",
 'r7500' => "$_POST[r7500]",
 'r7700' => "$_POST[r7700]",
 'r7750' => "$_POST[r7750]",
 'r7800' => "$_POST[r7800]",
 'r9000' => "$_POST[r9000]",
 'r160' => "$_POST[r160]",
 'r170' => "$_POST[r170]",
 'r925' => "$_POST[r925]",
 'r930' => "$_POST[r930]",
 'r950' => "$_POST[r950]",
 'rNCR' => "$_POST[rNCR]",
 'pepson' => "$_POST[pepson]",
 'pithaca' => "$_POST[pithaca]",
 'ppanasonic' => "$_POST[ppanasonic]",
 'k750' => "$_POST[k750]",
 'k800' => "$_POST[k800]",
 'k900' => "$_POST[k900]",
 'k910' => "$_POST[k910]",
 'klogic' => "$_POST[klogic]",
 'kqsr' => "$_POST[kqsr]"
 ); // etc 
// 
foreach ($checkboxes as $chk => $fieldname) { 
 if (isset($_POST[$chk])) { 
 // Don't need "and" first time through 
 if ($where) { $where .= ' AND'; } 
 $where .= " $chk='1'"; 
 } 
} 


 $query = " 
      SELECT *, 
        MATCH(content, summary, fdata) AGAINST('$keyword' IN BOOLEAN MODE) AS score 
        FROM KB WHERE ";
     $query2= " AND MATCH(content, summary, fdata) AGAINST('$keyword' IN BOOLEAN MODE) 
      ORDER BY score DESC 
 "; 

  
$data = mysql_query("$query $where $query2") or die(mysql_error()); 

Thanks again for the help.

[thenewb]

I did find a problem that was created because of the AND before my MATCH statement. To get around a invalid MYSQL statment when no check boxes had been selected I changed the last bit of code
from:

$query = " 
SELECT *, 
MATCH(content, summary, fdata) AGAINST('$keyword' IN BOOLEAN MODE) AS score 
FROM KB WHERE ";
$query2= " AND MATCH(content, summary, fdata) AGAINST('$keyword' IN BOOLEAN MODE) 
ORDER BY score DESC 
"; 


$data = mysql_query("$query $where $query2") or die(mysql_error()); 

TO:

 $query = " 
 SELECT *, 
 MATCH(content, summary, fdata) AGAINST('$keyword' IN BOOLEAN MODE) AS score 
 FROM KB WHERE ";

 $query2= " MATCH(content, summary, fdata) AGAINST('$keyword' IN BOOLEAN MODE) 
 ORDER BY score DESC 
 ";
 
if (isset($where))
{
$result ="$query $where AND $query2 ";

}
else
{
$result="$query $where $query2";

}

$data = mysql_query("$result") or die(mysql_error()); 

[rocknbil]

I did have an error in the ISSET statement as you probably noticed, missing a ).

If you're going to use same-named form fields and database fields (bad idea, reveals stuff to potential hackers about your DB) you don't need an associative array. In your foreach, you're doing that anyway - it's only using the first value ($chk), never touching the associated value ($fieldname).

$checkboxes = array( 
'mws', 'power', 'smp', 'xpient', 'r5000', 'r5500', 'r6500', 'r7000', 'r7500', 'r7700', 'r7750', 'r7800', 
'r9000', 'r160', 'r170', 'r925', 'r930', 'r950', 'rNCR', 'pepson', 'pithaca', 'ppanasonic', 'k750', 'k800', 
'k900','k910','klogic','kqsr' 
); // etc 
// 
foreach ($checkboxes as $chk) { 
 if (isset($_POST[$chk])) { 
 if ($where) { $where .= ' AND'; } 
 $where .= " $chk='1'"; 
 } 
} 

if you set $where=null you don't need to check with isset, same result though. Here's ternary replacing your if/else if you want it . . .


$result = ($where)?"$query $where AND $query2 ":"$query $where $query2";