Flexible use of appropriate sorting methods (sphsort, SQL, yii, array) in the project MySQL to replace charindex functions substring_index, find_in_set

This article summarizes the sorting methods used in recent projects. If you need to reprint them, please noteArticleSource

1. sphsort sorting

 

 

 
Yii: Import ('ext. sphinxclient '); $ sphinxapi = new sphinxclient (); $ sphinxapi-> setserver ('2017. 0.0.1 ', 10312); $ sphinxapi-> setconnecttimeout (3); $ sphinxapi-> setmatchmode (sph_match_boolean); $ sphinxapi-> setarrayresult (true ); $ sphinxapi-> setgroupby ('type', sph_groupby_attr, "@ count DESC"); $ sphinxapi-> setlimits (0, 9999,100 00 ); $ result = $ sphinxapi-> query ('', 'main; Delta '); unset ($ sphinxapi );
 

Where

 
Sph_groupby_attr, "@ count DESC"
 

Specified sort by @ count in descending order

 

2. SQL sorting (sort a column by the specified array instead of the current table field)

Sometimes we need to sort the data from the database in the order of our existing arrays, rather than simply order by based on the data table fields.

For example:

We have an array $ idarray = array (, 6 );

Now we need to query 19 pieces of data from the shop table, and arrange the shop IDs according to the order in $ idarray.

Available,Find_in_set(ID, '*************') to achieve

 

Select ID, name from shop where ID in (, 6) order by find_in_set (ID, '13, 24,2, 21 ')
 

Of course, we often encounter dynamic conditions, which can be joined using arrays> strings.

 

 

 
$ SQL = "select ID, name from shop where ID in (". join (',', array_keys ($ idarray )). ") order by find_in_set (ID ,'". join (',', array_keys ($ idarray )). "')"; $ rawdataall = yii: APP ()-> DB-> createcommand ($ SQL)-> queryall ();
 

 

AboutFind_in_set(ID ,'************').

Reference: substring_index and find_in_set functions that replace charindex in MySQL

3. The array is sorted by any element of the array.

Encapsulate this method into a class and call it at will in the yii project.

 

 
Arrayhelper: array_sort ($ bodyarray, 'Count', 'desc ')
 

 

 

<? Phpclass arrayhelper extends ccontroller {public function array_sort ($ arr, $ keys, $ type = 'asc ') {$ keysvalue = $ new_array = array (); foreach ($ arr as $ k => $ v) {$ keysvalue [$ K] = $ V [$ Keys];} if ($ type = 'asc ') {asort ($ keysvalue);} else {arsort ($ keysvalue);} reset ($ keysvalue); foreach ($ keysvalue as $ k => $ V) {$ new_array [$ K] = $ arr [$ K];} return $ new_array ;}}?>