Sort search results from MySQL by the position of the search query in the string using PHP

mysql, php, sorting, strpos

Solution

You can do this easily in MySQL.

 SELECT  title,LOCATE('banana',title)
 FROM myTable   
 WHERE  LOCATE('banana',title) > 0
 ORDER BY LOCATE('banana',title)

title represent column of MySql table.

Problem

I want my search results to be in order of string position from smallest to greatest. For example, searching for "banana" returns: Babyfood, plums, bananas and rice, strained Bananas, dehydrated, or banana powder Bananas, raw Bread, banana, prepared from recipe, made with margarine CAMPBELL Soup Company, V8 SPLASH Juice Drinks, Strawberry Banana CAMPBELL Soup Company, V8 SPLASH Smoothies, Strawberry Banana CAMPBELL Soup Company, V8 V. FUSION Juices, Strawberry Banana I want "Bananas, raw" to come first because "banana" is the first word in the result, and I want "CAMPBELL Soup..." to come up last because "banana" is the last word. I know I can use strpos() to find the position, but how do I put it all together?

Original source