We'll have a user table that contains user login information such as username, password, and some other fields that we might use. A simple user table is shown below.
I will assume you can create a table like this if you plan to use some of the example code found later in the article.
We first create a table called "const_skills" with the following SQL code:
SQL> CREATE TABLE const_skills (
id int not null primary key,
value varchar(20) );
And now we will populate it with 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 (6, "JSP");
Your const_skills table now looks like this:
This table's only purpose is to hold the names of the skills that web developers can select. Now, create a table called "lookup_skills" with the following SQL:
SQL> CREATE TABLE lookup_skills (
id int not null auto_increment primary key,
skill_id int );
The purpose of lookup_skills is to provide a mapping from userids to web devloper skills. In other words, it allows us to store the developers and what skills they have. So, when the
unemployed web developer hits the submit button after filling out our form, we will populate this table with the checkbox values that were checked off. For each skill
the developer checks off, a row will be added with the user's uid from the user table and the number corresponding to the skill he selected in the const_skills table. Before we
look at the code to do the insert, lets first use our design to make creating our form easier. Each time that the user requests the page that has our form on it, we can query
the database and get the checkbox labels from the const_skills table and create the checkbox form with the appropriate checkbox choices. Here is the code to do that.