Search code examples
phpmysqlsortingstrpos

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

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?

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.