Change from radio to checkboxes, mysql impact

[rwigs]

Ok, so I have been asked to help with an existing website. They have a form that users can submit information. One of the fields used to be a radio button selection but they changed it to checkboxes so that the user could select more than one option. They did it right as far as the checkboxes work and multiple selections get entered into the field in the database in a comma separated format. So they thought they were good and couldn't figure out why they can no longer select from the database where the field had one of the selections that they were looking for. What I mean is something like this:

 

The data from the insert from the checkboxes would put something like this in the field:

 

Tucson, Phoenix, Mesa

 

And they were running another function that was saying:

 

select * from eventstable where city = 'Tucson'

 

but it would not select the record. To you and I that makes sense, but I had a hard time explaining to them why it doesn't work. So that leads me to them asking for help.

 

So I am looking at a couple of different options here to address this:

 

1. Change the insert process to loop through each of the values in the checkboxes, inserting a record for each one with all the other data being the same, only entering one value for city per record.

 

2. Tackle it on the other end and create a triger that will go after each insert, that will see if there was more than one value entered in the city field and break that record into multiple records, having the same effect as the other option.

 

I have more experince working in SQL than PHP, so the first option is less attractive for me to tackle. But I don't want to let my fear of what I'm not used to prevent me from doing something that would be easy if I just learned how to do it.

 

Opinions?

[scootstah]

inserting a record for each one with all the other data being the same

No. It sounds like you need a one-to-many relation here. The schema was designed for only one city, but now it should be re-designed to support multiple cities. Don't copy data.

 

Once you have a new table for the event cities, you just need to insert the event, get the last insert ID, and then insert each city into its own table along with the event ID. Then when you select the event, you can JOIN the cities.

[rwigs]

Ok, that makes sense.

 

Since I won't the unique ID for the event record until it is created, I would do an AFTER trigger that could take the values entered in the city field and put them into the EventCities table with the eventID. Then when selecting from the events table join on the eventid columns.

 

Is that what you are thinking?

[gizmola]

Ok, that makes sense.

 

Since I won't the unique ID for the event record until it is created, I would do an AFTER trigger that could take the values entered in the city field and put them into the EventCities table with the eventID. Then when selecting from the events table join on the eventid columns.

 

Is that what you are thinking?

 

How about leaving triggers out of it and just have a proper relational structure?  MySQL triggers reduce concurrency and are very slow relatively speaking.

 

MySQL provides a way to find the auto_increment id of a just inserted row as alluded to by scotch

 

in pseudocode

 

-begin transaction

-insert parent

-get id

-insert many-to-many cities table rows for any relevant cities

-commit

 

 

There should be a cities table and not a redundant storage of the city name over and over.

 

 

I realize this was not your design, nor your design mistakes but when you have a requirement that you change something, it gives you an excuse to start doing things properly and this will improve the system in the process and preserve performance over time.