[RESOLVED] Storing multiple countries/values in MySQL field

dabdura

Hello all

here's my situation:
I want to display content based on users country ip
for example:
row1 display to "ZA, SE, PT, PR, NO, NL, LU, IT, IE, FR, FI, DK, DE, CH, BE, AT"
row2 display to "US, NL, LU, IT, IE, FR"
row2 display to "US, GB, CA, AU" etc. (sometimes these lists include 60plus counties)

i've red that storing multiple values in one field is bad idea, then i've red that there's a SET type of column which can store multiple values. I'm totally confused now.
please advise which type of column and properties to use, how store data and how to search/retrieve data.
or if there's totally different workaround for this.

thanks in advance

dagon

while you could use SET\ENUM, i would still use 1 field per country

something like:

display_id country

1 ZA
1 SE

etc

2 US
2 NL

...etc

then you can select display_id with a simple query SELECT display_id where country='$country'

dabdura

sorry if might sound like a moron but I don't get it
what you mean by:
1 ZA
1 SE
and
2 US
2 NL
what should I do when I want to display for example row1 to 50 countries?

NogDog

The data Dagon suggested would be in a separate table, where the first column would be a foreign key to the main table. So let's say that the main table has a row something like:

id  data
--  --------
1   This is some data for this row
2   Here is data for another row

Now suppose you want the first data to display to US, UK, and CAN while the 2nd would display to FR and SP. The country table would have a record for each relationship between country and data, each using the ID for the above row, and the applicable country code:

data_id  country
-------  -------
1        US
1        UK
1        CAN
2        FR
2        SP

Then you would select the data for a given country with something like:

SELECT * FROM data_table, country_table
WHERE data_table.id = country_table.data_id

dabdura

thanks for your time and answer, I got it now, but it seems more complicated than multi value field and it's too static, I have about 200 rows which are constantly added/removed, in some cases I want row x to show for
1 CAN
2 FR
so i'm stuck in that case, I guess better go "multi value field" way

dabdura

any other ideas????

laserlight

dabdura wrote:
it seems more complicated than multi value field and it's too static

No, it is neither complicated nor static.

dabdura wrote:
I have about 200 rows which are constantly added/removed, in some cases I want row x to show for
1 CAN
2 FR
so i'm stuck in that case

This is what NogDog outlined: the table would have a row corresponding to x and CAN, and another row corresponding to x and FR.

dabdura wrote:
I guess better go "multi value field" way

A multi-valued field is more complicated. You would need to use database functions that may not be standardised, or go through hoops to get exactly what you want. It is rather static.

NogDog

NogDog;10954024 wrote:

...

SELECT * FROM data_table, country_table
WHERE data_table.id = country_table.data_id

I just realized I left out the part of the query that would be needed to select for a given country code:

$country = 'US';
$query = "SELECT * FROM data_table, country_table
WHERE data_table.id = country_table.data_id AND 
country_table.country = '$country'";

dabdura

thanks again NogDog, this really is a great forum where people really try to help you.
anyways, that would mess up the way my code works and i'd have to rewrite most of it so I went the simplest way I could think of, duplicated rows with multiple countries and assigned single country to each, this way I have lot more rows but I think it should be faster than working with 2 tables

thanks again