storing and retrieving multiselect values

chucklardo

Hi there,

I have a product database that allows products to be listed by application.

for example,

A garden 'Spade' could be listed under 'garden Tools', 'digging equipment','hand tools' etc.

I have a lookup table that lists all of these application categories and assigns an ID number to them

e.g.

gardentools - 1
diggingeq - 2
handtools - 3

so... a my product record has field called 'application' with the array of applications listed in it (these come from a multi-select box)

product_name prod_application
Spade 1,2,3
Pitch Fork 1,3
Garden Hose 1

with me so far?

So my question is, if I want to run a query that tests the application field ...

what is the MySQL syntax to check if a certain number is present in the applciation field?

e.g. select all the hand tools...

SELECT * FROM PRODUCTS WHERE prod_aplication .............. '3'

SELECT * FROM PRODUCTS WHERE prod_aplication CONTAINS '3' ??????

can anyone help?

cheers,
Mitch.

bretticus

You're looking for the string 3 appearing in prod_aplication? Well, I think you are after something else, but here that is...

SELECT * FROM PRODUCTS WHERE prod_aplication LIKE '%3%'

So does...

product_name prod_application
Spade 1,2,3
Pitch Fork 1,3
Garden Hose 1

..mean that you have this...

<select multiple>
<option value="1,2,3">Spade</option>
<option value="1,3">Pitch Fork</option>
<option value="1">Garden Hose</option>
...??

bretticus

Ah, understand you better the second time around...

Probably what you want (instead of storing a comma delimited list for every product) is a many-to-many table to associate product ids with category ids.

two columns will suffice.

this will allow you to do queries without parsing the prod_aplication column value with PHP everytime.

( I don't recommend it, but you could try inserting the value for prod_aplication like '|1|2|3|'. To do this you would have to insert from multiselect and stick a delimiter between values (I chose pipes) and wrap that in delimiters again on the end. Then you can use LIKE '%|3|%'. I suppose if you would rather just build '1,2,3' REGEXP (used similiar to LIKE) might do the trick (no sytax comes to mind.)