query a comma seperated sql field

[CanMan2004]

Hi all

I have a page which is returning results based on a number search.

I have a sql database which has a field called "products", data is stored in this field in the following format

34,45,56,78

What I want to be able to do is to perform a search on the field "products" and check to see if the number I search is stored anywhere within the commas seperated data. The query I tried was

$productsearch = $_GET['productsearch'];
SELECT * FROM data WHERE $productsearch IN (products)

The problem is that the above is just returning rows where there is a single value stored in products field, it does not seem to return results where the number is stored somewhere in the comma seperated value.

Hope that makes sense

Thanks in advance

Dave

[hvle]

When you store a serie of number separated by comma, mysql only see it as a single string, and that's how it is behave that way.

[CanMan2004]

ok, i see

so how would I make the query read from a comma seperated value, taking numbers between the commas

thanks

dave

[ShogunWarrior]

[b]Quoted from mySQL documentation:[/b]
MySQL does not include a function to split a delimited string. Although separated data would normally be split into separate fields within a relation data, spliting such can be useful either during initial data load/validation or where such data is held in a text field.

[CanMan2004]

I gather by that message that it is quite tricky to do

[hvle]

There is actually a bad alternative thru this problem:

1. Read the entired database.
2. for each record, explode the products field into an array.
3. Do a in_array check.

This way is so bad, because it retrieve the entired database.  I would not use it in million years.
You should redesign your database to use list instead.

[holowugz]

there is another alternative i had this problem before the way i would do it for yours would be:

$productsearch = $_GET['productsearch'];

SELECT * FROM data WHERE (products LIKE '%,$productsearch,%') OR (products LIKE '%,$productsearch') OR (products LIKE '$productsearch,%') OR (products LIKE  '$productsearch')

lets say that $productsearch was 16, that query will check your Comma seperated list for each of the following:

products LIKE '%,$productsearch,%' = ,16, (This checks to see if 16 is inbetween other values likle this 12,13,15,16,21,43)
products LIKE '%,$productsearch' = ,16 (This checks to see if 16 is at the end of the list like this 12,13,16)
products LIKE '$productsearch,%' = 16, (this checks to see if 16 is at the beginning of the list like this 16,12,13,14)
products LIKE  '$productsearch' = 16 (This checks to see if 16 is just on its own)

[Barand]

If you write your list as

,12,13,15,16,21,43,

then it becomes "... LIKE '%,$productsearch,%' ".

But the real solution is to normalize your data properly.