Adding an extra field to mysql form

[daverichris]

Hello,

 

I'm not sure if this is javascript query or mysql really.

 

I have created a basic profile form: name, dob, address etc. I have also included languages section at the bottom, I don't want lots of empty boxes so would like users to click an add button which will create a new box for users to add the language they speak and then if they want they can click the button again to create another box and so on.

 

The database needs to be easily searchable by recruiters, so i presumably would need a separate column for each language instead just lumping it all in one text box. Any ideas how to do this? or what this would be called so I could look up some tutorials?

 

thanks mike

[Adam]

The database needs to be easily searchable by recruiters, so i presumably would need a separate column for each language

 

No, though this is a common misconception when learning about database design. Consider how many columns you would actually need to cater for every possible user. Some may speak 1, whilst others may speak 10+. You can't efficiently account for every user with fixed columns. Also, imagine what the query would look like...

 

where language1 = '...'
or language2 = '...'
or language3 = '...'
or language4 = '...'
or language5 = '...'
-- etc

 

Instead you should take advantage of rows, and more specifically a relational table -- called say `user_languages`. A column in that table would be the foreign key referring to the other table you mentioned -- we'll call it `users` for now. The other column would be the language, though I don't know how you plan to handle languages internally...

 

For example:

 

create table user_languages (
    user_id mediumint unsigned not null,
    language varchar(2) not null,
    primary key (user_id, language),
    foreign key (parent_user) references users(id)
) engine=InnoDB;

 

(... This assumes you already have a table called `users` which contains an `id` column.)

 

Now instead of inserting what languages the user speaks into the `users` table, you would insert a row for each language they speak into the `user_languages` table:

 

insert into user_languages (user_id, language)
values
(1, 'EN'), -- user #1 speaks English
(1, 'DE'), -- user #1 also speaks Dutch
(1, 'FR'), -- user #1 also speaks French
(2, 'EN'); -- user #2 just speaks English

 

Now coming back to how you would query for users matching a language, you just need to join the `user_languages` table, based on the `user_id` where it matches the language:

 

select users.* from users
join user_languages on (users.id = user_languages.user_id)
where user_languages.language = 'EN';

 

I don't want lots of empty boxes so would like users to click an add button which will create a new box for users to add the language they speak and then if they want they can click the button again to create another box and so on.

 

The database structure above should now allow you to create an interface similar to what you describe, with each text box being a new row in the `user_languages` table.