0

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) > 0;
  • No GROUP BY?.. – Rick James Mar 14 '16 at 4:19
  • Don't store data in commalist; make another table. – Rick James Mar 14 '16 at 4:19
2

So if these are your requirements:

  1. User select a few countries on a web page.
  2. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.