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

Question

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?

Answer 1

You could also select INSTR(titles, 'banana') as firstIndex and order by that value, ASC. This will return the index of the first located index of the needle within the haystack. Tag on a WHERE clause that omits anything that isn't LIKE '%banana%' and you should be set:

SELECT id, pubdate, title, INSTR(title, 'tea') as `index`
FROM article
WHERE title LIKE '%tea%'
ORDER BY `index` ASC;

Answer 2

It will involve a needlessly complex usort or something similar in PHP, best to do it in the query, for example:

SELECT data, INSTR(data, 'banana') as index
FROM table
WHERE data LIKE '%banana%'
ORDER BY index != 0, index

Answer 3

if you just want to emulate strpos:

select col, locate('banana', col) as pos from t
order by pos < 1, pos, length(col)

Answer 4

If you don't get that data from an SQL query, you can sort that using usort and stripos ; something like this should do :

$arr = array(
    "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", 
);

function compare_position($a, $b) {
    return stripos($a, 'banana') - stripos($b, 'banana');
}

usort($arr, 'compare_position');
var_dump($arr);

i.e. you are here sorting with you own defined function, that compares the position (case-insentive) of "Banana" in the two strings it receives as parameters.

And you'll get this kind of output for your array, once sorted :

$ /usr/local/php-5.3/bin/php temp.php
array(7) {
  [0]=>
  string(37) "Bananas, dehydrated, or banana powder"
  [1]=>
  string(12) "Bananas, raw"
  [2]=>
  string(56) "Bread, banana, prepared from recipe, made with margarine"
  [3]=>
  string(43) "Babyfood, plums, bananas and rice, strained"
  [4]=>
  string(61) "CAMPBELL Soup Company, V8 SPLASH Smoothies, Strawberry Banana"
  [5]=>
  string(61) "CAMPBELL Soup Company, V8 V. FUSION Juices, Strawberry Banana"
  [6]=>
  string(64) "CAMPBELL Soup Company, V8 SPLASH Juice Drinks, Strawberry Banana"
}

Of course, if you get that data from an SQL query, it might be easier to do some additionnal calculations on the SQL side...

Answer 5

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.