Jump to content

query a comma seperated sql field


CanMan2004

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
Link to comment
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.
Link to comment
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.
Link to comment
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)




Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.