design table to store country name I have a php page where user can select multiple locations like USA, UK, Finland etc

At the moment i am storing all the data in mysql table comma separated.
It is working fine but when i try to get count for individual locations i am having problem to write sql query.

I want to know what is the best way to save multiple locations in db table.

Edit:
Sample data

Location
India
UK,Finland, India
USA, NZ, AUS, Spain


Please advise.

select l.location, count(*)
from posting l join
     posting p
     on find_in_set(l.location, p.location) &gt; 0;
So if these are your requirements:


User select a few countries on a web page.
Countries selected by that user are stored in database.


You need to do the following:

create table country(
countryID number,
name varchar(50));


Set countryId as a primary key.

create table user(
userId number,
username varchar(50));


set userId as a primary key.

Since they are connecting to your page to click on these countries, we can call these "sessions", so new rule:


Every time a user connects to the web site, a session is logged.


Which then produces this table

create table session(
sessionId number,
userId number,
countryID number))


Set sessionID as a primary key.
Set userID as a Foreign key referencing table user.userId.
Set countryID as a foreign key referencing table country.countryId.

If you want to count how many countries each user has selected in a given session, you would then do the following:

select sessionId,UserId, count(*)
from session
group by sessionId,UserId


If you want to know all countries selected for every time a user went on your web page, group by UserId only.

Again, this is based on very very limited requirements.
You should be able to reuse the concept for your own purpose however.

I hope that helps.