its long, but it could be worth it.

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.

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...

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...

okay...now my code is there....whatever. i'm confused now.

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.

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.

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";

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...