Leaderboard

Alright, this one took me a while to remember how to do. It's part of a class of problems of manipulating one number's digits to come up with another number, and the work to solve it tends to have a common pattern.

I agree with mac_gyver except for the use of OR in the WHERE clause. Consider this product table... +------------+-------------------------+ | product_id | description | +------------+-------------------------+ | 1 | Black mamba | | 2 | Fireball XL5 | | 3 | Single coat black paint | | 4 | Coat of many colours | | 5 | Black coat XL | | 6 | Not of interest | +------------+-------------------------+ Code... $search = 'coat black xl'; $params = array_map(fn($v)=>"%$v%" , explode(' ', $search)); $q1 = "SELECT description FROM product -- search query using OR WHERE description LIKE ? OR description LIKE ? OR description LIKE ? "; $q2 = "SELECT description FROM product -- search query using AND WHERE description LIKE ? AND description LIKE ? AND description LIKE ? "; Results... Results using OR +-------------------------+ | description | +-------------------------+ | Black mamba | | Fireball XL5 | | Single coat black paint | | Coat of many colours | | Black coat XL | +-------------------------+ Results using AND +---------------+ | description | +---------------+ | Black coat XL | +---------------+ A couple of other options are open to you FULLTEXT Add fulltext index on description and $q3 = "SELECT description , MATCH(description) AGAINST('coat black xl') as relevance FROM product WHERE MATCH(description) AGAINST('coat black xl') ORDER BY relevance DESC " ; +-------------------------+-------------------+ | description | relevance | +-------------------------+-------------------+ | Black coat XL | 0.18123811483383 | | Single coat black paint | 0.18123811483383 | | Coat of many colours | 0.090619057416916 | | Black mamba | 0.090619057416916 | +-------------------------+-------------------+ NOTE: with fulltext, words of 3 or less characters (eg "XL") are ignored. Use separate columns for category, colour and size and search on those.