searching for full word

Space_Cowboy

Hello,

I have a field in mySQL which contains numbers seperated by comma's
ie |6,8,9,22,45,77| etc etc

In my search query I want to be able to search for numbers. However if I search for '1', it is bringing up results for '11', '21' etc etc

How do I make it just search the specific number im asking for?
Ive done a quick search and seen a couple of solutions, but none which actually make it clear how I would implement into my search query

$GetContact = "SELECT * FROM stock WHERE sub_categories LIKE '%".$description."%'";

($description is the number im trying to find/search for).

Thanks

NogDog

The best solution is probably to normalize your database design and not store multiple values in one field. 🙂

If that is not practical (time, you don't "own" the DB, etc.), then you can use the REGEXP function.

$sql = "SELECT . . . WHERE col_name REGEXP '(^|,)$number(,|$)'";

This will not be as efficient though as normalizing the data design (probably using a separate many-to-one relation table) which can then take advantage of indexes on that column to optimize performance.

bradgrafelman

Since we're talking about MySQL, there is also the FIND_IN_SET() function as well, which might be more appropriate than using regexp's depending upon your exact needs.

Again, however, the real solution is to normalize your database so you aren't running into this problem in the first place.

Ashley_Sheridan

If you have to retain the data structure you have here, are you able at all to change the end delimiters on the numbers? For example, if you changed |6,8,9,22,45,77| to ,6,8,9,22,45,77, you can then just do this:

$GetContact = "SELECT * FROM stock WHERE sub_categories LIKE ',$description,'";

where description is your number.

Also, just to point out, you don't need to break out of your string if you're using double quotes. Double quotes in PHP tells PHP that any variables in string are parsed as variables and not just text.