Save checkbox data in the Database in PHP (1) _php base

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 to 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->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;
}


? >