/* the function we call to insert. the $skills argument is the skills array that is sent to the script when the user hits the submit button */ function insert_skills($uid, $skills) {
/* first, we'll delete any entries this user already has in the table */ purge_lookup("lookup_skills", $uid);
/* now create the sql insert query */ $query = create_checkbox_query($skills, "lookup_skills", $uid);
/* execute the query */ mysql_query($query); }
/* helper function for insert_skills(). removes all rows in $table with $uid */ function purge_lookup($table, $uid) { $q = "DELETE FROM $table, WHERE uid = '$uid'"; mysql_query($q); }
/* helper function for insert_skills(). generates the sctual SQL query */ function create_checkbox_query($arr, $table, $uid) { $q = "INSERT INTO $table (uid, skill_id) VALUES";
/* 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; } }
?>
如果执行了搜索 PHP 和 Javascript ,这个函数返回这个语句:
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;