Searching records with mysql

[Jonob]

I have a setup where users can store 'templates' in a table. When they add new products, they can apply these templates to the new product - basically it pre-fills fields in the product form.

 

For example, they can store a template with a name of 'red tomato', and if they create a new product called 'red tomato', then the system will automatically find the rule, and apply the template.

 

At the moment, this is super simple, and performed via a query as follows:

 

SELECT * FROM (`templates`) WHERE `rule` like '%red tomato%' LIMIT 1

 

I'd like to take this to the next step, and give users more flexibility in how the search is performed. For example, I'd like to allow users to save wildcards with their template rules. So, they could create a template rule called '*tomato' and this would match any of the following search terms: 'tomato', 'red tomato' or 'green tomato'.

 

Do I need to be careful with the wildcards that are allowed (reserved or special chars ?).

 

Any other general concerns or comments would be appreciated.

[Adam]

Can you explain a little more what the template's job is? I'm having a hard time picturing what you're trying to do here.

[fenway]

Since your script will be doing the substitution, mysql will have no idea -- and you can store any text you want in a string field.