views:

196

answers:

5

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?

A: 

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;
Jonathan Sampson
The index is not 0 based, i.e. 0 means it is not in the string, 1 means it is at the start of the string.
enbuyukfener
Yes, I'm aware of that enbuyukfener :)
Jonathan Sampson
A: 

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
enbuyukfener
A: 

if you just want to emulate strpos:

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

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...

Pascal MARTIN
+2  A: 

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.

Adeel
Yes!! This one works! I tried some of the other ones but I didn't get them to work. I think my MySQL knowledge was just not enough. But this one was so simple I just plugged in my table name and column name and bang-o it did exactly what I wanted.
Earl Hooker