Jump to content


Photo

query a comma seperated sql field


  • Please log in to reply
7 replies to this topic

#1 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 23 July 2006 - 03:11 PM

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

#2 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 23 July 2006 - 03:16 PM

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.
Life's too short for arguing.

#3 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 23 July 2006 - 03:18 PM

ok, i see

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

thanks

dave

#4 ShogunWarrior

ShogunWarrior
  • Members
  • PipPipPip
  • Advanced Member
  • 528 posts
  • LocationIreland

Posted 23 July 2006 - 03:30 PM

Quoted from mySQL documentation:
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.
<a href="http://www.daviddora...nmedia.com/">My New Site/Blog</a> | <a href="http://www.daviddora...m/check/">Check your page for broken links/images/scripts</a>

Zend Certified Engineer
Follow me on Twitter: http://twitter.com/davidd

#5 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 23 July 2006 - 03:36 PM

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

#6 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 23 July 2006 - 04:16 PM

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.

Life's too short for arguing.

#7 holowugz

holowugz
  • Members
  • PipPipPip
  • Advanced Member
  • 46 posts

Posted 23 July 2006 - 04:51 PM

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)






#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 23 July 2006 - 07:09 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users