Save checkbox data in the Database in PHP (2)

Data | Database The code is very simple, and you'll soon be finished. The main work has two functions to complete: "Get_checkbox_labels" and "make_checkbox_html". Where the "get_checkbox_labels" query table Const_skills and returns an array of objects, each with an ID value and a corresponding skill name. We send this array and some other parameters to "make_checkbox_html", which returns a string that generates the HTML code for the checkbox. Now we're going to insert this string into the HTML file to generate the form we need to include a variety of skills choices. Note that I do not transmit the variable $checked to "make_checkbox_html", which is an array of objects we want to display checked. If a user learns a new skill, we can provide an "edit skills" page that shows the user's skill item saved in the checkbox box should be checked in advance.

What are the benefits of creating a form dynamically in this way relative to a fixed HTML code to generate a skill checkbox? Well, maybe we allow job seekers to choose a project that doesn't have a const_skills in our table, such as DHTML, so that we can insert it into the table const_skills, and then, when the job seeker visits our site, one more DHTML option is found. All this doesn't need to be adjusted for HTML files.

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 invoke each element of the selection, which means that we can access each selection as an array element. So we can insert this option into the table Lookup_skill. If the user selects 5 options, we insert 5 records in the Lookup_skill. Remember that there are only two field user IDs and skill IDs for each record in the table Lookup_skills. In my example site, users can register and then create/edit their profiles. You may want to use session to save UserID when they log in. But how to manage UserID is beyond the scope of this article.

The following code, we assume that we might access the UserID with this variable name $uid, the following is the function code to insert the record:


/* 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) {

/* A, we'll delete any entries this user already has
In the table * *
Purge_lookup ("Lookup_skills", $uid);

/* Now create 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 very simple. Now you know how to create a form dynamically from a table Const_skill read a record, and also know how to save user-selected skills to table Lookup_skills. What are we going to do next? Let's take a look at the search.
Search

When an employer comes to a web developer, he comes to your search page and you can display the same form and allow him to choose the skills he wants the employee to have. You pick up an array of his selected skills, and then you can iterate through the array and use an SQL statement to find the job seeker with this skill, you can display the list or result, and allow the searcher to point to an item to display its details. The following function describes how to create this 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 has count (user.username) >= $count";

$query. = ";";
return $query;
}
}

?>

If you perform a 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.S kill_id = Const_skills.id and (const_skills.id = 3 OR const_skills.id = 5) GROUP by User.username having count (user.user Name) >= 2;

This function will return the logic of the item you selected, which means that if we choose PHP and JavaScript two entries, we will only return the username of the candidate who has PHP and JavaScript two skills. If you want to find a job seeker with any of these skills, you can use PHP *or* Javascript, and if you want to show the same record, you can remove the last "GROUP by ..." Clause.


Summarize

Well, that's it. Checkboxes is an excellent form element, as discussed in this article. I hope this helps you to work with them to create a data-driven Web site.