Search code examples
phpmysqlsql-like

mysql search for a catid in a text field

I'm trying to do a mysql search (in php) for a catid number within a text field. I'm currently using LIKE but I need it more specific. Here's my code:

SELECT * FROM articlepix 
WHERE published = 1 AND catid LIKE '%86%' 
ORDER BY RAND() LIMIT 1

^^ I'm looking for the number 86 within the catid field, but I want it to look for only 86, currently it's pulling out the data with the number 186 too.

The above example grabs the field where the catid is "91,107,36,139,146,168,186".

Is it possible to search for just '86' rather than everything that includes %86%? If that makes sense?

Solution

  • MySQL can help you workaround this huge database design errors with function FIND_IN_SET. Give this a try:

    SELECT * FROM articlepix 
WHERE published = 1 AND FIND_IN_SET('86', catid) > 0
ORDER BY RAND() LIMIT 1

    Which of course can be simplified into:

    WHERE published = 1 AND FIND_IN_SET('86', catid)

    As the function only returns 0 when there is no match, but it might be harder to understand what it does :)