#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    4
    Rep Power
    0
    Here is my situation:

    I have a table called "user" that holds user info such as username, id number, email address, etc. Each user of the system has a unique entry in this table.

    I have an html form that each user can fill in. On the form are many
    checkboxes, each representing a name of a country in the world. The point is,
    after a user logs into the system, he or she can select the countries that they
    have travelled to. I need some way to store this data with their user profile
    data, maybe not in the same table, but for each user, i need to know which
    countries they have travelled to. The question boils down to this: how do I
    store this data?

    I really don't want to use a column in the "user" table called countries of
    type SET because that type is limited to 64 entries and there are more than 64
    countries that i want to keep track of.

    I have thought of creating a separate table called "possible" which would hold
    only the names of the countries i wanted to keep track of. Then I would have
    another table called "countryLookup" which could have a userid column and a
    country column. The userid column would have an int representing the user, and
    the country column would have an int representing the entry in the possible
    table. Since a particular user could have travelled to serveral countries,
    there could be several rows in this "countryLookup" table, each with the same
    userid number.

    The problems that I see (as a novice) are the are the following:
    1. If a user checks 10 countries on the form and i have to insert this data
    into the database, do I have to do 10 inserts?

    2. I cant seem to create a select statement for searching for all users who
    have been to a set of countries...ex. everyone in the db who has gone
    to Brazil and Germany.

    Can someone help me with the above problems? I know this must be possible
    because I have seen things like it on web sites. I just can't seem to think howto do it. Is there a better way to store this data? Any help will be appreciated. If anyone wants to converse about this over email, let me know. If anyone
    can succsessfully help me, there might be a reward. Thanks.
  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,648
    Rep Power
    4493
    I'm sure it's better to divide the data into a second table, maybe with userid and the name of the country, or even better, like you mentioned, would be two more tables, one holding the countries and a unique id for each one, the other holding the userid and a countryid.

    you could use your countries table to actually create all of the checkboxes on your site, rather than writing them out by hand. that way, if you wanted to add a country, you simply put it in the database, and the next time the page is loaded, it's there.

    users
    +--------+--------+
    | userid | name |
    +--------+--------+
    | 1 | John |
    | 2 | Billy |
    | 3 | George |
    +--------+--------+

    countries
    +---------+---------+
    | countid | country |
    +---------+---------+
    | 1 | Brazil |
    | 2 | Germany |
    | 3 | USA |
    +---------+---------+

    visited
    +--------+---------+
    | userid | countid |
    +--------+---------+
    | 1 | 1 |
    | 1 | 2 |
    | 1 | 3 |
    | 2 | 3 |
    | 3 | 2 |
    | 3 | 3 |
    +--------+---------+

    to create your checkboxes:
    <?php
    $result = mysql_query("select * from countries");
    while (list($id,$country) = mysql_fetch_row($result))
    {
    echo "<input type=checkbox name="countries[]" value="$id">$country";
    }
    ?>

    I think you will have to do an insert for every country the person checks, unless you are using a version of mysql past 3.22.5, where you can do mulitple inserts with one command.

    Inserting the countries
    <?php
    //assumes you already know userid
    for ($count=0;$count<count($countries);$count++)
    {
    $country = $countries[$count];
    mysql_query("insert into visited values ('$userid','$country')");
    }
    ?>

    to see everyone who has visited USA

    <?php
    $result = mysql_query("select user.name from user,visited,countries where user.id=visited.userid
    and visited.countid = countries.countid and countries.country='USA'");
    while (list($name) = mysql_fetch_row($result))
    {
    echo $name;
    }
    ?>

    It's kind of hard typing all that into this little box, but hopefully that gives you some ideas. anyone else please feel free to correct me or offer better solutions...this is just what i came up with on the fly...

    have fun...
  4. #3
  5. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,648
    Rep Power
    4493
    my code didn't show up for some reason...

    to create your checkboxes:

    $result = mysql_query("select * from countries");
    while (list($id,$country) = mysql_fetch_row($result))
    {
    echo "<input type=checkbox name="countries[]" value="$id">$country";
    }

    I think you will have to do an insert for every country the person checks, unless you are using a version of mysql past 3.22.5, where you can do mulitple inserts with one command.

    Inserting the countries

    //assumes you already know userid
    for ($count=0;$count<count($countries);$count++)
    {
    $country = $countries[$count];
    mysql_query("insert into visited values ('$userid','$country')");
    }


    to see everyone who has visited USA


    $result = mysql_query("select user.name from user,visited,countries where user.id=visited.userid
    and visited.countid = countries.countid and countries.country='USA'");
    while (list($name) = mysql_fetch_row($result))
    {
    echo $name;
    }


    It's kind of hard typing all that into this little box, but hopefully that gives you some ideas. anyone else please feel free to correct me or offer better solutions...this is just what i came up with on the fly...

    have fun...
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,648
    Rep Power
    4493
    okay...now my code is there....whatever. i'm confused now.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    32
    Rep Power
    15
    You could just store the countries in a text field, separated by a character such as "`". Assuming you're using PHP a simple implode() would do the job. Then it's just one INSERT to write the data and you can easily search on countries using LIKE, and avoid the joins you'd have to use with multiple tables.
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    4
    Rep Power
    0
    Ok, now that I have two different reccommendations, can anyone suggest which one i might want to do? Both sound reasonable to me. I guess the first one is more "proper" in that the data is normalized further. The downside to that is the joins that need to be done to do searching. I have written the sql and it works nicely although the code is ugly and if i want to search off some other things in addition to country, i need to craft a pretty complicated sql statement but oh well.

    The second solution seems to require much less effort in the way of creating the database and writing the sql statements to query it. It seems to be very straightforward although it would take more space (each user will store a list of countries instead of an int).

    Even though the sql for the first solution is complicated, will it any slower than the second one, or will the second one actually be slower? I suppose that if the second is slower and obviously takes up more space, than the first one is the one to use regardless of the length and complication of the first's sql. any comments? by the way, much thanks to both of you for responding.
  12. #7
  13. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    The first method is better. Joins are not difficult and the ability to search on integers vs text will more than make up for the small performance hit of doing a join.

    Also, there is no performance hit for doing a multiple insert as you don't need to, you simply give multiple values in the insert.

    Using PHP for example you could build you query this way (assuming you've stored the checkbox values in the array $countries by using name=countries[] in the checkbox elements):

    $values="";
    for ($i=0;$i<count($countries);$i++)
    {
    if ($i!=0) $values.=",";
    $values.="($userid,$countries[$i])";
    }

    $result=mysql_query("insert into visited values $values");

    Then if you want to see the names of all users you've visited a country (stored in the value $vis_country in the example) you can do this:

    $query="select u.username from users u, visited v,countries c where c.country='$vis_country' AND c.countryid=v.countryid AND u.userid=v.userid";

  14. #8
  15. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,648
    Rep Power
    4493
    Again, it's versions past 3.22.5 that support multiple inserts like that on one line, so be sure your up to date before you try it.

    ---John Holmes...

Similar Threads

  1. DEBUGGER in VC++ dumping memory leaks
    By kavi_s in forum C Programming
    Replies: 3
    Last Post: December 3rd, 2004, 03:00 PM
  2. Help me please :((
    By IamaVBNewbie in forum Visual Basic Programming
    Replies: 10
    Last Post: February 4th, 2004, 03:00 AM
  3. display DOS message
    By childish_gal in forum Visual Basic Programming
    Replies: 4
    Last Post: December 28th, 2003, 07:35 AM
  4. Vbscript Questions
    By makirkp in forum Visual Basic Programming
    Replies: 2
    Last Post: October 30th, 2003, 10:45 PM
  5. kernel32.dll etc info on functions ?
    By Mozo_Grand in forum Visual Basic Programming
    Replies: 17
    Last Post: August 28th, 2003, 03:55 PM

IMN logo majestic logo threadwatch logo seochat tools logo