Saving Checkbox data in the database in PHP (2)

Saving Checkbox data in the database in PHP (2) This code is very simple and you will soon finish reading it. Two major functions are completed: "get_checkbox_labels" and "make_checkbox_html ". "Get_checkbox_labels" queries the const_skills table and returns an array of objects. each object has an id value and a corresponding skill name. We send this array and other parameters to "make_checkbox_html". This function returns a string to generate the html code of the checkbox. Now we insert this string into an html file to generate a form that contains various skill options. Note that I didn't send the variable $ checked to "make_checkbox_html". This parameter is an array of the checked objects to be displayed. If a user has learned a new skill, we can provide an "edit skill" page. the skill items saved in the displayed checkbox box should be pre-checked.

This method is used to dynamically create a form. What are the advantages of using a fixed html code to generate a skill checkbox? Well, maybe we allow job seekers to select a project that is not included in our table const_skills, such as DHTML. in this way, we can insert it into the table const_skills. then, when a job seeker visits our site, he will find that there is an additional DHTML option. You do not need to adjust the html file.

Insert lookup_skills

Now that we have created this form, we need to save the skills selected by this user. In the make_checkbox_html function, we use skill [] to call each selection item element, which means that we can access each selection item as an array element. In this way, we can insert this option into the lookup_skill table. If you select five options, we will insert five records in lookup_skill. Remember that in the lookup_skills table, each record has only two field IDs and skill IDs. In my example site, users can register and then create/edit their profiles. You may need to use session to save the userid when they log on. However, how to manage userids is beyond the scope of this article.

The following code assumes that we may access this userid and use this variable name $ uid. below is the function code for inserting records:


/* 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 ";

Foreach ($ arr as $ check ){
$ Q. = "($ uid, $ check )".",";
}

/* Remove the last comma and return */
Return substr ($ q, 0,-1 );
}

?>

It's easy. Now you know how to dynamically create a form by reading records from the table const_skill, and how to save the skills selected by the user to the table lookup_skills. What should we do next? Let's take a look at the search.
Search

When an employer comes to a web developer, he goes to your search page and you can display the same form and allow him to select the skills he wants the employee to possess. You get an array of the selected skills, and then you can traverse this array and use an SQL statement to find the job seeker with this skill. you can display this list or result, the searcher can click a project to display its details. The following function describes how to create a query statement:


/* 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 you search for PHP and Javascript, this function returns this statement:

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;

This function returns the logic of the selected project, that is, if we select PHP and Javascript, only the username of * job seekers with both PHP and Javascript skills will be returned. If you want to find a job seeker with one of the skills, you can use PHP * OR * Javascript. if you want to display the same record, you can remove the last "group... "clause.


Summary

Okay, that's it. Checkboxes is an excellent form element, as described in this article. I hope this will help you use them to create a data-driven website.