Saving checkbox data in the database in PHP (1)

Introduction

Checkbox is a very useful page form item. When multiple choices are made to users, it can even allow users to select all projects or none of them. However, although this is a very good form element, there is always some obfuscation in our work about how to properly Save the selection items. This article will describe how to properly store the checkbox selection items in the database under the good database design principles.

Requirements

This article describes how to properly store the selected items in the user database. Although this includes useful PHPCodeBut I will express them from the perspective of database design, so you can easily use any database or server-side scripting language. I just want to provide a method for you to apply it to your site. If you want to run the source code, you need to install PHP, MySQL, and the network server.

Example 1: recruitment site

If you are asked to create a recruitment website that allows software developers who are looking for a job to enter their skills so that employers can access the site and find the right employee based on the job seeker's skills. You also know that a developer has more than one skill, so you decide to design your site in this way.

Every job seeker will be allowed to access this site, register a user, and enter his skills. checkbox will come in handy. You may want to make a page like this:

_ PHP _ MySQL _ Zope
_ Perl _ JavaScript _ JSP

[Submit]

You can select the skills you have for each job. Obviously, this option is different for different people. One person may be PHP and MySQL, and others may be JSP. How will you save these options? A natural idea is to create a field for each option so that it can work properly. However, you may find that when you want to expand or adjust the table structure, you may have to modify it.
A good method should be like this:

You should have a user table containing the user registration information, such as the user name, password, and other information you need. If you directly use the source code provided later in this article, you need to create a simple table as follows:

Id Username
1 user1
2 user2
3 user3

Create a table "const_skills" and use the following SQL statement:

SQL> Create Table const_skills (
Id int not null primary key,
Value varchar (20 ));

Now we add the following 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 be like this:

ID value
1 PHP
2 MySQL
3 Zope
4 Perl
5 Javascript
6 JSP

This table only allows you to select the corresponding skills. Now, create another table lookup_skills with the following SQL:

SQL> Create Table lookup_skills (
Id int not null auto_increment primary key,
UID int,
Skill_id INT );

This lookup_skills table aims to provide a ing between the user table and the development skill table. In other words, it allows us to save developers and their skills. For example, when a job seeker completes the selection and click Submit, we will fill in the table with the values selected in the checkbox. For each selected skill, we add a record in this table to write down the user ID and the selected ID. (Everyone knows it. I translated it here, hey ...)

Before we look at the code for inserting a record, we should first design this page, and there should be a form in the content, we can query the database and take the checkbox label from the const_skills table, create this checkbox form 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
Checkboxes */
$ Html_skills = make_checkbox_html ($ skills, 3,400, "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>
</Html>

<? 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 will be packaged
An object and put in an array */
While ($ ROW = mysql_fetch_object ($ qid )){
Array_push ($ arr, $ row );
}

Return $ arr;
}

/* Prints a nicely formatted table of checkbox choices.

$ Arr is an 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 an array of element names that shoshould 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 will 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 will have checks */
Foreach ($ arr as $ Ele ){
$ Str. = "<TD> <input type =" checkbox "name =" $ name "value =" $ ele-> ID "> ";
$ 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;
}

? >