[RESOLVED] Searching for needle in haystack (php/mysql)

Cent

Hi All,

In a field called "savedata" in a table, we store data like:
1|3|5|10

It's basically a list of numbers that correspond to data from an array set.

I'm trying to search it with PHP and not having much luck.

Currently using:

$query = "SELECT * FROM database.table WHERE savedata LIKE '1'";

if record A has savedata = 1
and
record B has savedata = 12|14|7|1|2

then the above only finds record A even though record B has a 1 as well.

I tried LIKE '%1%' with no luck.

Is there a way to do this or perhaps store the data differently in the field so that its searchable?

Best,
Cent

bogu

U need to check for 4 cases, if the savedata is 1 or if 1 is at the begining or if 1 is in the middle or if 1 is at the end ...

SELECT * FROM database.table WHERE savedata LIKE '1' or savedata LIKE '1|%' or savedata LIKE '%|1|%' or savedata LIKE '%|1'

barand

I just set up a test table with values like this, with '|' at beginning and end. This lets you search for '1' without pulling '12' or '31'

REC  SAVEDATA
A    |1|2|5|7|
B    |5|6|10|12|

WHERE savedata LIKE '%|1|%'

found rec A but not B

WHERE savedata LIKE '%|12|%'

found rec B but not A

A better solution would be to have a normalized "savedata" table with values like

REC  | SDATA |
-----|-------|
  A  |    1  |
  A  |    2  |
  A  |    5  |
  A  |    7  |
  B  |    5  |
  B  |    6  |
  B  |   10  |
  B  |   12  |

Cent

hi barand,

your suggestion of putting the delimiter before and after the data worked exactly and makes perfect sense.

WHERE savedata LIKE '%|1|%'

our other 2 options were a normalized table and using MySQL's SET function. creating the table would generate upwards of 45000+ records. for this particular need, the data is read once and written back completely if they update their preferences. so its "rarely" used. MySQL's SET function seemed like a good choice but we would have to set all of the options, and if we added anything new to the site, we'd have to modify the database to accomodate.

but like I said, you solution worked great. Thanks.

Best,
Cent