inserting into and retrieving from DB from multiple selected values in a SelectList

alvaroesc

Hello,

As I was doing the insert form I have come across a double problem: 😕

When from a Multivalue selectlist the user chooses more than one value, how should that be coded when inserting so that they dont go to the same cell of the table of the database? That is, mysql would have to create a row for each of the values selected from that SelectList Menu, while at the same time keeping in mind the other checkboxes that hold values (task and when)

Real example: the Multivalue select list contains EthnicOrigins, so they are different and all of them are chosen, while the other checkboxes refer to the same thing like, Painting, afternoons, which are common to all of the ethnic origins. So in the table of mysql that should look like:

ID ETHNICORIGIN TASK WHEN
1 african painting afternoons
2 spanish painting afternoons
3 caucasian painting afternoons

that person wanted to insert that common task (to do painting in the afternoons) for all of the ethnic origins.

I dont know how to put it so that takes every value of the list and can send all 3 options with one submit.😕

because if you go like insert ($ ethnicorigin,....)
values $_POST[ethnicorigin]

if the user has chosen 3 ethicorigins, what does that $_POST actually contain? probably only the last selected one...

And then, I would have the other side of the problem. 😕 A search form where someone wants people to do painting in the afternoons and also doesn't care where they are from, so again searches and selects all of the values from the EthnicOrigin dropdown menu select list.

thanks a million

Alvaro

Sxooter

You need to normalize your data. Which means having a one to many relationship with this drop down.

create table main_table (id int primary key, task text, when timestamp);
create table ethnic_origin (id int foreign key references main_table(id), origin text);

Sxooter

Note that that's still not really normalized, but more so than what you've got now.

What you really should use is a table with all the ethnic origins in it and a lookup table between the main table and that one.

Sxooter

OK, brain fully online now (sick the last week) let me show you what I really mean.

create table main_table (id int primary key, task text, when timestamp);
create table ethnic_origin (id int primary key, origin text);
insert into ethnic_origin (id, origin) values (1,'African'),(2,'Asian'),(3,'European');

Now we've got two tables holding our data. The second table is the lookup table for creating your form btw, so to add an ethnicity, just add to that table. Now we create a many to many lookup table:

create table main_to_eth (main_id int foreign key references main_table(id), eth_id int foreign key references ethnic_origin(id));

and you put entries in there that tie the two tables together.