Geek Answers Handbook

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

I want my search results to be in the order of the rows from smallest to largest. For example, a search for "bananas" returns:

Baby food, plums, bananas and rice, tense

Bananas, Dehydrated or Banana Powders

Bananas, raw

Bread, banana, recipe made with margarine

CAMPBELL soup company, V8 SPLASH juices, strawberry banana

CAMPBELL soup company, V8 SPLASH cocktails, strawberry banana

CAMPBELL Soup Company, V8 V. FUSION Juices, Strawberry Banana

I want Banana Raw to come first because banana is the first word in the result, and I want CAMPBELL Soup ... to appear last because the last word is banana. p>

I know that I can use strpos () to find a position, but how do I put it all together?

+4
source share
5 answers

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 the column of the MySql table.

+3
source

You can also select INSTR(titles, 'banana') as firstIndex and order this value, ASC. This will return the index of the first located needle index in the haystack. The tag in the WHERE that omits everything that is not 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;
0
source

It will include unnecessarily complex usort or something similar in PHP, it is best to do this in a request, for example:

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

if you just want to emulate strpos:

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

If you do not get this data from the SQL query, you can sort it with 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 sorting here with your own specific function that compares the position (case-insentive) of "Banana" in the two lines that it receives as parameters.


And you get this output for your array, after sorting:

 $ /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 this data from an SQL query, it might be easier to do some extra calculations on the SQL side ...

0
source

More articles:


All Articles