Mysql search engine

Question

I have a setup where users can store 'rules' in a table, and they can then match new products against those rules.

For example, they can store a rule 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 certain parameters from the 'red tomato' rule to the new product.

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

SELECT * FROM (`rules`) 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 create a rule called 'tomato', and this would match any of the following search terms: 'tomato', 'red tomato' or 'green tomato'.

What would be the best approach for this?

Answer 1

What are you trying to do, could be done through implementing a token dictionary, tokenizing the rule. That way you will be able to create a table which will store the associations between products and rules, after you compare the tokens of product name with each rule token.

Answer 2

It sounds like you're trying to implement taxonomy, and then have things happen based on the items a product is associated with.

Recommending something is tough, because I'm not sure what sort of system you have now. I know that Drupal does taxonomy very well, and already has a rules system built that you can integrate with the taxonomies you create for your content items (products)

If it's the custom route, you can build something with CodeIgniter very quickly, and it will be much more customizable.

Explaining what you're working with right now would help quite a bit and allow more specific answers though.

Answer 3

I think something like Lucene would be better suited for this task than a relational database.