Insert comma separated text from an input to database without a duplicate enrty

Question

I have a predefined list of cities in my database named "Cities".

id     name
1      City1
2      City2
3      City3
4      City4

The user need to add the cities he has visited in an input field in comma separated format, so I need to check all the cities he has added in the form one by one if it exists in the "Cities" table or not. If it exists then insert the user id and city id from "cities" in "city_visited" table if it doesn't exist then insert it first in "cities" and then to "city_visited" table.

I can get cities via explode

$city_list  = "city1,city2,city3,city4,city5,city6";
$city_visited = explode(",", $city_list);
$city_visited[0];
$city_visited[1];

How can I know how many cities the user has entered and then check for any existing data in "cities" table and then add the city's id in the "city_visited" table.

id     city_id    user_id
1      1          1
2      2          1
3      3          1
4      4          1

How can I add the cities entered by a user into Database without a duplicate entry in any table. — Sonix McDonald, May 12 '15 at 12:45
This should be helpful to you: http://stackoverflow.com/q/3164505/538216 — Levi Morrison, May 12 '15 at 14:19
best way is to use multi select option menu and use it in array format to avoid duplicate data — saurabh kamble, May 12 '15 at 20:44

score 0 · Answer 1 · answered May 12 '15 at 20:30

Not sure if this can be the best solution,but you can try to start from this idea:

create an index on the name column in the Cities and make sure that the field is unique, when you split $city_list, before inserting into the db, make each item of $city_visited uppercase.

So in your table you will have

id     name
1      CITY1
2      CITY2
3      CITY3
4      CITY4

This way it will be the db that will ensure that the name field will be unique, since if you will try to insert another time City1, the index kick in and produce a duplicate key error.

The only thing left out is if someone input a wrong spelled city name, but I think this is unavoidable.

Hope this help

Insert comma separated text from an input to database without a duplicate enrty

1 Answers1