Save checkbox data in the database in PHP

Data | database

Introduced A checkbox is a very useful page table item that allows a user to select all items or not one, even if they have multiple selections. However, although this is a very good form element, there are always some confusing situations in our work about how to properly save the selection. This article describes how to correctly store a checkbox selection in a database, in accordance with a good database design principle. Requirements This article explains how to correctly store the selections in the user database. Although this includes useful PHP code, I will express them from the viewpoint of database design, so you can easily use any database and server-side scripting language to implement them. I just want to provide a way to do it so that you can apply it to your own site. If you want to run the source code here, you need to install PHP, MySQL, and Web servers. Example 1: recruitment site If you are asked to do a recruiting site, allow job-seekers to fill in their skills, allow employers to visit the site, and find the right staff based on the skills of the job seeker. As you know, a developer has more than one skill, so you decide to design your site this way. Every job seeker will be allowed to visit the site, register a user, and enter his skills, the checkbox will come in handy, you may want to make a page like this: __ PHP __ MySQL __ Zope __ Perl __ Javascript __ JSP Submitted Every job seeker can choose the skills he has. Obviously this choice is different for different people. One person may be PHP and MySQL, others may be just JSP. How will you save these choices? A natural idea is to build a field for each option so that it starts working correctly. But then you may find that when you want to expand or adjust, the trouble comes and you may have to revise your table structure. The good way to do this is to: You should have a user table that contains the user's registration information, such as user name, password, and any other content you need. If you directly use the source code given later in this article, you will build a simple table as follows: ID username 1 User1 2 User2 3 User3 Let's build a table "Const_skills" with the following SQL statement: Sql> CREATE TABLE Const_skills ( ID int NOT NULL PRIMARY key, Value varchar (20)); Now we add the skills: Sql> INSERT into Const_skills (ID, value) VALUES (1, "PHP"); Sql> INSERT into Const_skills (ID, value) VALUES (2, "MySQL"); Sql> INSERT into Const_skills (ID, value) VALUES (3, "Zope"); Sql> INSERT into Const_skills (ID, value) VALUES (4, "Perl"); Sql> INSERT into Const_skills (ID, value) VALUES (5, "Javascript"); Sql> INSERT into Const_skills (ID, value) VALUES (6, "JSP"); Your const_skills should now be like this: ID value 1 PHP 2 MySQL 3 Zope 4 Perl 5 Javascript 6 JSP This table just allows the user to select the appropriate skill, and now, build a table lookup_skills with SQL as follows: Sql> CREATE TABLE Lookup_skills ( ID int NOT NULL auto_increment primary key, UID int, skill_id int); The purpose of this table lookup_skills is to provide a mapping relationship between the user table and the Development skill table. In other words, it allows us to save the developer and the skills they have, such as when a job seeker completes the selection by clicking Submit, we will fill out this table with the values selected in the checkbox. For each of the selected skills, we add a record to the table, noting the user ID and the ID of the selected item. (presumably everyone knows it.) I translate this, hehe ... ） Before we look at this insert record code, we first design this page, the content should have a form, we can query the database and take the checkbox tag from the Const_skills table, build this checkbox table item. The code is as follows: < PHP /* Insert code to connect to your database here/* /* Get the checkbox labels * * $skills = Get_checkbox_labels ("Const_skills"); /* Create the HTML code for a formatted set of Checkboxes * $html_skills = make_checkbox_html ($skills, 3, "skills["); ? > < HTML > < BODY > < br > < form name= "skills" method= "POST" action= "insertskills.php" > Check off your web development skills: <? echo "$html_skills";? > < br > < input type= "submit" value= "Submit" > </form > </body > < PHP function Get_checkbox_labels ($table_name) { /* Make an array * * $arr = Array (); * Construct the query * * * $query = "SELECT * from $table_name"; /* Execute the query * * $qid = mysql_query ($query); /* Each row in the result set would be packaged as An object with put on an array */ while ($row= mysql_fetch_object ($qid)) { Array_push ($arr, $row); } return $arr; } /* Prints a nicely formatted table of checkbox choices. $arr is a array of objects that contain the choices $num is the number of elements wide we display in the table $width is the value of the width parameter to the table tag $name is the name of the checkbox array $checked is a array of element names that should be checked */ function make_checkbox_html ($arr, $num, $width, $name, $checked) { /* Create string to hold out HTML */ $str = ""; /* Make it * * $str. = "< table width=" $width "border=" 0 ">n"; $str. = "< tr >n"; /* Determine if we'll have to close add A closing TR tag at the "End of" our table * * if (count ($arr)% $num!= 0) { $CLOSINGTR = true; } $i = 1; if (Isset ($checked)) { /* If we passed in an array of the checkboxes we want To be displayed as checked * * foreach ($arr as $ele) { $str. = "< td >< Input type=" checkbox "Name=" $name "value=" $ele->id ""; foreach ($checked as $entry) { if ($entry = = $ele->value) { $str. = "Checked"; Continue } } $str. = ">"; $str. = "$ele->value"; if ($i% $num = = 0) { $str. = "</tr >n< tr >"; } else { $str. = "</td >n"; } $i++; } } else { /* We just want to print the checkboxes. None would have checks * * foreach ($arr as $ele) { $str. = "< td >< Input type=" checkbox "Name=" $name "value=" $ele-">"; $str. = "$ele->value"; if ($i% $num = = 0) { $str. = "</tr >n< tr >"; } else { $str. = "</td >n"; } $i++; } } /* Tack on a closing TR tag if necessary * * if ($closingtr = = True) { $str. = "</tr ></table >n"; } else { $str. = "</table >n"; } return $str; } ? >

The code is very simple, you quickly finished it. 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. Notice that I did not transfer 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 have 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.