Multiple Select - php, mysql

gerry87

Hey, I'm having a lot of trouble with multiple select and PHP.

Take the multiple select list:

List 1

• A
• B
• C

How are these normally used with php and mysql? At the moment if the user selects a few choices, and presses submit, only one choice is submitted to the database. How do I submit them all?

I would like it that if the user chooses say A B and C, then all three are written to a cell in the database - so under column 'List 1' row 'uid' it has 'A,B,C' in that cell.

Is this possible? Or do I have to have a column for each choice in the list with a 1 if the user selects this choice and 0 if they don't? The problem with this is that the list is going to be quite long so It would be a pain have so many columns.

Hope this makes some slight sense!

laugh

It isn't recommended to have multiple distinct number or string values in a field, you have to do more complex work to split out (parse) the individual values afterward.

Have a table for the choices and then another table which matches user_id's to choice_id's, this is the correct way to manage a many to many relationship.

Liam Byrne

You could implode the equivalent POST array to write it to the database, and then read and explode it when you're reading from it.

But as laugh correctly pointed out, this isn't often suitable. It'd be OK for a simple "save and retrieve", but if the app does anything more complex, or has dependencies on those options, or needs to search on the options, the "simple" solution will make everything else unnecessarily complex.

MOH

Not quite sure what you mean by a "cell" in the database, but if you're saying you write:

List1, uid, "A, B, C"

You need to write 3 records instead - one for each selection chosen:
List1, uid, A
List1, uid, B
List1, uid, C

When reading it back your query should loop through all the entries for List1, uid

This is all assuming List1 and uid are your keys.

gerry87

Sorry, i haven't really got my head around the 'lingo' yet, so not really sure how to say what i'm asking. By cell i mean this:

[U]
uid| location   |Offering                    | Seeking[/U]
1  | Ireland    |Accountancy, Legal          | Golf Clubs           
2  | England    |Legal                       |Financial Advice, Painting,
3  | USA        |Painting, Golf Clubs        |Financial Advice, Legal  
4  | Ireland    |Pool Table, Financial Advice|Legal, Web Design,
5  | England    |Web Design, Legal           |Pool Table, Financial Advice

This is how I thought It would look in the database, so 'Accountancy, Legal' is in the 'Offering' for person 1. Then I want to be able to return person 1 is looking for golf clubs and person 3 has golf clubs...

I can have 1 thing in offering and one thing in seeking no problem, but are you saying that if I want to have multiple choices it has to be more like this?

[U]
uid| location   |Offering Accountancy | Seeking Accountancy | Offering Legal | Seeking Legal[/U]
1  | Ireland    |         1           |          0          |        1       |    0          
2  | England    |         0           |          0          |        1       |    0   
3  | USA        |         0           |          0          |        0       |    1   
4  | Ireland    |         0           |          0          |        0       |    1   
5  | England    |         0           |          0          |        1       |    0   

and so on? I'm probably making this way more complicated than it has to be

laugh

users table

id| name | location 
1  | jimmy | Ireland           
2  | johnny |England   
3  | james | USA       
4  | jim | Ireland   
5  | john |England

products (or services table, you may want to split this into products and services or something). Edit - you could refer to them all as items.

id| name  
1  | Web Design      
2  | Accountancy
3  | Pool Table       
4  | Golf Clubs 
5  | Gardening

<Removed a mistake there, you will need the two tables.>

userstoproducts_offering table

user_id| product_id  
1  | 1      
1  | 2
2  | 3       
4  | 1 
5  | 4

To select what jimmy is offering select the product_id where the user_id = 1 and join the table to the product table to get the product names.

userstoproducts_wants table

user_id| product_id  
1  | 1      
1  | 2
2  | 3       
4  | 1 
5  | 4

MOH

Just to confuse you, you're looking at database normalization
(Google might give better examples)