Jump to content

Archived

This topic is now archived and is closed to further replies.

CanMan2004

query a comma seperated sql field

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
ok, i see

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

thanks

dave

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)




Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.