PHPBuilder - Storing Checkbox Data in a Database Page 5



RSS Twitter
Articles Databases

Storing Checkbox Data in a Database - Page 5

by: Dan LaFlamme
|
October 15, 2000

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($query0, -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

« Previous Page
1
|
2
|
3
|
4
|
5

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Dan LaFlamme

Comment:



Comment:

(Maximum characters: 1200). You have characters left.