I’m in need of some quick help on matching a field in my database that stores all of the “parent” categories for my online store. Here’s an example of how my “parents” are stored in the table via one field named Parent:
MENS MENS-BRANDS MENS-SHIRTS MENS-T-SHIRTS
Here is my query in PHP to perform the call:
$query = "SELECT id FROM $usertable where parent like '".strtoupper($parent)."'";
The problem is, if I am on MENS-BRANDS, this will also return those products who are listed in every other category because it contains the word “MENS.” Since all of the parents are stored in one field, how can I make my SQL query only recognize each physical word that is separated by spaces in the field itself, instead of it trying to find every instance of different fragments of a word throughout the field?
I hope this makes sense, and any help is surely appreciated.
Ideally you can change your schema so that you have a separate table linking these categories to your existing entries. This way you can have one row per product and you can easily write a SQL query that looks for the specific word you want without the need for a
LIKE match. Added bonus: this will improve performance.
However, if you absolutely cannot change this schema, your best bet is probably to use a regular expression like
WHERE parent REGEXP ':<:MENS:>:'
I’m here using MySQL regular expressions. If you’re using a different database management system the same concept will work, but the exact syntax may be different.