Storing Checkbox Data in a Database Page 5

Searching

When someone looking to hire web developers comes along and goes to your search page, you can show the same form and allow that person to check the qualifications he is
looking for. You’ll get the array of his choices, then you can just loop through that array, building a query to select web developers with those qualifications. You can then
display a list or results and allow the searcher to click on an entry in the list to see a more detailed profile. Here is a function demonstrating how to build the query:

<?php
/* builds a query to search for the skills
   checked off in the $skills array */
function skill_search($skills) {
   if (!empty($skills)) {
     $query = "SELECT DISTINCT user.username
                 FROM user, const_skills, lookup_skills
                WHERE lookup_skills.uid = user.id  
                  AND lookup_skills.skill_id = const_skills.id ";
     $query .= " AND (";
     foreach ($skills as $check) {
       $query .= " const_skills.id = $check OR";
     }
     /* remove the final OR */
     $query = substr($query, 0, -2);
     $query .= ")";
     $count = count($skills);
     $query .= " GROUP BY user.username HAVING count(user.username) >= $count";
     $query .= ";";
     return $query;
   }
}
?>

If the person performing the search checked “PHP” and “Javascript”, this function would return the query:

SELECT DISTINCT user.username FROM user, const_skills, lookup_skills 
 WHERE lookup_skills.uid = user.id AND lookup_skills.skill_id = const_skills.id 
   AND ( const_skills.id = 3 OR const_skills.id = 5 ) 
 GROUP BY user.username HAVING count(user.username) >= 2; 

Note that this function returns the logical “AND” of the boxes you check. That is, if both PHP and Javascript are checked as in the above example, we will only get
usernames of those web developers who know *BOTH* PHP and Javascript. If you wanted the usernames of those who know either PHP *OR* Javascript (or both), everything would
be the same except that you would remove the line that appends the SQL code “GROUP BY…” to the query.

Conclusion

Well, that’s it for this article. Checkboxes are excellent for tasks like the one mentioned here and I hope this helps people wondering how to work with them on their
data driven web sites.

— Dan