inserting into and retrieving from database multiple selected values from a list

[alvaro]

 

 

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 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 different values.

 

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. Soy in the table of mysql that should look like

 

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.

 

Ain't got a clue how to put it so that takes every value of the list and can send all 3  options with one submit.

 

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

 

========================================================

 

 

 

 

 

 

[ignace]

this entirely depends on your database normalization, i would use an intersection (many-to-many) table to store the ids of what the user has choosen, so for your example i would have the following fields:

 

country_id

task_id

timeofday_id

 

where task and timeofday would come in a listbox and country in a combobox because we can only perform one task during a certain point in the day, but we could do this in different countries (theoretically anyway)

 

although i think that this was not a good example

[alvaro]

 

 

Thank you for taking the time to reply.  Maybe I didnt manage to describe the task properly.

 

As per the ethicorigin, just think of a pool of painters all in the same company, so dont think about them being in different countries. In fact, this is not an example but a real project. I have people from different nationalities who are going to do different tasks. They and many others will fill a form and some of them may want to choose to be available for different tasks (hence multivalue select boxes).

 

The coding is purely PHP, not anything to do with tables, that is, I can perfectly do with two tables, personaldata and servicesprovided.

 

So if Mr.David chooses 2 tasks for which he thinks he is apt such as painting and carpeting, he will enter his skills like two entries in the database. If you look at the table services, there will be these 2 entries

 

TaskID    TaskDescription  PersonToDoIt    TimeOftheday

 

  1          Painting              David              Mornings

  2          Carpeting            David              Afternoons

 

 

 

so, at the FORM, at the SelectList, select 2 and send them with one click to the database.

 

 

 

Alvaro

 

 

========================================================

 

 

 

 

 

[GingerRobot]

You need to name your select box as an array. E.g. If it's called job, name it job[].

 

You can then cycle through the array like so:

 

foreach($_POST['job'] as $v){
echo $v.'<br /> ';
}

 

Of course, you'll be wanting to do something more than echo the values; but that was just by way of an example.