MySQL query help - searching an integer list

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

MySQL query help - searching an integer list

Post by crabyars »

Hi, I hope someone can help me. I've been looking half the night for a solution but I'm at a dead end.

I have a set of records, each has a field with a list of integers. I would like throw a list of integers at a search query and get matching rows.

example records:
1 - 1,3,5,8,10
2 - 2,51,59
3 - 4,9,10,23

So if I searched for "8,23,99" the query should return records 1 and 3.

I have tried "LIKE", I've tried "IN", and I've tried FULLTEXT searches. To use fulltext, I would have to change the minimum search length to 1, which feels like the wrong thing to do.

If anyone has any suggestions they will be much appreciated, thanks.
swdev
Forum Commoner
Posts: 59
Joined: Mon Oct 25, 2004 8:04 am

Post by swdev »

The best solution would be to re-design the table such that it didn't store multiple integers in a single field.

If that is not possible, then I suggest running 1 query for each integer to be found, in your example, this would mean running 3 queries.

Hope this helps
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

Post by crabyars »

Thanks for your reply. I don't think I can run multiple queries there may be too many integers. I didn't think this should be so tricky as to call for a re-design. Hmmm
Post Reply