Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Nov 2010
    Posts
    94
    Thanks
    23
    Thanked 0 Times in 0 Posts

    Multiple table population (+ check if exists)

    I have two tables in my database, one called users and one called skills.

    'users' has the fields - id, name, email, skillone, skilltwo, skillthree, (+ some other registration info)

    'skills' has the fields - id, skill.

    I've populated the skills table with data, so it looks like this -

    id - 1, skill - dj
    id - 2, skill - web design
    id - 3, skill - blogs

    I currently have a registration page which gathers all the registration information from a user. I'm now trying to make a new page where users can add their skills.

    The page currently has an input box that has an autocomplete feature, based on the skills already in the skills table. So a user can select from a pre-populated list. There will be multiple inputs, incase the user has multiple skills.

    What I need to happen, is once the input box is populated with the autocomplete text (say web development), find the id of that skill in the 'skills' table, and put the id into either skillone, skilltwo or skillthree (depending on what input box is being used, either .input1, .input2, or .input3).

    Hope that makes sense. Any help appreciated!

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,535
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I'm confused... are you asking how you query this? How you do the logic for this? The query is straight forward, a select and insert works just fine.

    Also, its not good to have multiple columns for the same datatype. In the future, if you want to expand to 6 skills, you have to add 6 columns, edit a bunch of code, its messy.

    Instead, you can create a mapping table like so:

    Code:
    CREATE TABLE skillMap(
        user_id int not null,
        skill_id int not null,
        skillNum int not null,
        primary key (user_id, skill_id)
    );
    The skillNum is only there in case you care which skill is first, second, third. You can drop this if it doesn't matter.

    This way, if you want a user's skills, all you have to do is join skill on skillMap where user_id = something. And in the future, if you decide to drop to 2 skills or go up to 20, no (or nominal) changes are necessary.

  • #3
    New Coder
    Join Date
    Nov 2010
    Posts
    94
    Thanks
    23
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    I'm confused... are you asking how you query this? How you do the logic for this? The query is straight forward, a select and insert works just fine.

    Also, its not good to have multiple columns for the same datatype. In the future, if you want to expand to 6 skills, you have to add 6 columns, edit a bunch of code, its messy.

    Instead, you can create a mapping table like so:

    Code:
    CREATE TABLE skillMap(
        user_id int not null,
        skill_id int not null,
        skillNum int not null,
        primary key (user_id, skill_id)
    );
    The skillNum is only there in case you care which skill is first, second, third. You can drop this if it doesn't matter.

    This way, if you want a user's skills, all you have to do is join skill on skillMap where user_id = something. And in the future, if you decide to drop to 2 skills or go up to 20, no (or nominal) changes are necessary.
    Thanks a lot, definitely makes a lot more sense using that new table instead.

    Think I've cracked the logic, will give it a go and let you know how I get on.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •