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 8 of 8
  1. #1
    Regular Coder
    Join Date
    Nov 2008
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to save countries to database

    I am doing the script for a dating site.
    In the form, there is an option for a person to choose what country they want their partner to be from. It is a simple solution if they just select one country, but how would i do it if they wanted their partner to be from 20 different countries. I can not save it to the normal table where all the other records are.

    So if I save it to a whole new table, how would i do the query?

    For example ...table 1 has age, sex, race
    Table 2 has the list of countries

    How would i do a search if user is looking for an 18 year old white female from USA or france or Britain or Brazil or Canada?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You need a MANY-TO-MANY table.

    Thus:
    Code:
    Table: People
        personid int auto_increment primary key
        name
        age
        gender
        homecountryid int references Countries(countryid)
        ... etc ...
    
    Table: Countries
        countryid int auto_increment primary key
        countryname
        
    Table: CountryPreferences
        personid int references People(personid),
        countryid int references Countries(countryid)
    Do it this way, and every person can express a preference for any number of countries.

  • #3
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    You could also make the 2 columns in CountryPreferences the primary key to avoid accidental, or otherwise, duplicate records.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Yes, re making the composite primary key. You'd be surprised how many people have no idea (a) that you can have a composite primary key or (b) how to specify one.

  • #5
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    there's plenty of examples of setting PK constraints on the www

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    LOL! Sure, and there are plenty of examples of normalization and foreign keys and inner and outer joins and we could go on all day. Does that stop people from asking here, first, instead of doing a little research? Not last I checked.

  • #7
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    not sure what your issue is.

    All I said, and it wasn't directed at you personally but as just a general comment, was:

    You could also make the 2 columns in CountryPreferences the primary key to avoid accidental, or otherwise, duplicate records.
    If they want to ask here first, I don't have any problem with that

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    I think you are taking OP the wrong way Bullant. You're on the same page, different paragraphs.

    I too think people should take the time to look up how stuff works, it is obvious many don't take the time.

    Your point about the composite key was well received from what I can tell, he was just emphasizing the point that many people don't know you can do that.


  •  

    Posting Permissions

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