Search/checkboxes

[thoand]

Hi I a database table with the following columns

title
abstract
body
author

and want to search in theese columns

From my search page I want to let the enduser to check which columns they want to search in eg.

[ ] Title, [ ] Abstarct, [ ] Body, [ ] Author

How can I script a query that sees which coumns that the user have checked,
because as you see here I realy dont know how many/which boxes that is checked, and I dont want a lot of if/else structures

hope you can help me

best regards Thomas A.

[daphreeek]

Use this as your form (make it look better tho):

<input type=checkbox name='title' value='1'>Title<br>
<input type=checkbox name='abstract' value='1'>Abstract<br>
<input type=checkbox name='body' value='1'>Body<br>
<input type=checkbox name='author' value='1'>Author<br>
<input type=checkbox name='search_string'>

This will create your sql query:

$sql = "SELECT * FROM table WHERE";

if ($checkbox_title == 1)
   $sql .= " title LIKE '%$search_string%'";

if ($checkbox_abstract == 1)
   $sql .= " adstract LIKE '%$search_string%'";

if ($checkbox_body == 1)
   $sql .= " body LIKE '%$search_string%'";

if ($checkbox_author == 1)
   $sql .= " author LIKE '%$search_string%'";

// do mysql query using $sql

now thats not tested so no guarantees - i often make typos 🙂 but its a good start point.

• matt

[thoand]

It should get me started

[Threehearts]

Hi!
You wont get around if/else stuff unless you want to search through the variable lists.
Checkboxes dont transfer any data unless theyre checked, so $title and so on should only exist if checked.

I'd do something like:
$str_searchstring = "";
if($title == "1"){$str_searchstring .= "title";}
if($abstract == "1"){
if($str_searchstring != ""){$str_searchstring .= ", ";}
$str_searchstring .= "abstract";}
}
if($author == "1"){
if($str_searchstring != ""){$str_searchstring .= ", ";}
$str_searchstring .= "author";}
}
if($body == "1"){
if($str_searchstring != ""){$str_searchstring .= ", ";}
$str_searchstring .= "body";}
}

$sql_query = "SELECT $str_searchstring FROM ....";

clumsy but working.
im sure this is not the best solution, but here you go.

greetings, j.

[Threehearts]

above:
the query is crap of course, but the structur should work.
sorry, i really should get some sleep.
greetings, j.

[daphreeek]

hey again - i have just looked over my above post and it aint right 🙂 hehe - i said i make mistakes

try this one maybe:

$fields = ""; 
$valid_search = 0;

if ($checkbox_title == 1) {
   $fields .= " title LIKE '%$search_string%' "; 
   $valid_search = 1;
}

if ($checkbox_abstract == 1) {
   $fields .= " abstract LIKE '%$search_string%' "; 
   $valid_search = 1;
}

if ($checkbox_body == 1) {
   $fields .= " body LIKE '%$search_string%' "; 
   $valid_search = 1;
}

if ($checkbox_author == 1) {
   $fields .= " author LIKE '%$search_string%' "; 
   $valid_search = 1;
}

if ($valid_search == 0)
   echo "Select at least 1 place to search";
else{
   $fields = substr(eregi_replace("  ","" OR ", $fields), 1, -1);
   $sql = "SELECT * FROM table WHERE $fields)";

   // do mysql query using $sql
}

I think that looks a bit better 🙂 Still - no guarantees!

You also need to check the search_string so it has no double spaces in it and also no bad SQL in it.

• Matt

[thoand]

Any good sites about "bad queries"?