What is the best way to store multiple selections from the drop-down list to the database?

advertisements

I have a multi select drop down list. I:

I'm just wondering which is the best way to store it into MySQL database: 1) serialise() method of PHP or 2) in specific format with different symbols like comma (,) vertical bar (|) and etc.

For example, I have cities table. It contains city_id and city_name. One person may travel to more than one city. So he will select multiple cities. I want to store this selection somehow so that I can use them whenever I need by mysql joins.

I use Laravel 5.


The proper way that will store your data in a normalized way is to have a separate table for the selected cities.

I don't know what entitity your selections are connected to, but say it's users.

users table

id   int
name string

etc.

userCities table:

id       int
userId   int
cityId   int

cities

id       int
name     string

So if a user selects three cities, he or she will have one row in the users table and three rows in the userCities. Doing this will enable you to create much better and faster queries than if you store the selections as a concatenated value in one column in the users table. To retrieve a user and its cities you do an outer join with userCities and cities (so that users without cities are also returned).