Mysql sorting problem mysql queries a data list. The data list is one page by page, for example, five records.
$page = $_REQUEST['page'];select * from user limit ($page-1)*5,5;
Then, the queried array is $ user_list, and then the user_list is recycled.
Foreach ($ user_list as $ k => $ v) {// calculate a new field cc $ user_list [$ k] ['CC'] = aa ($ v ['id']) based on a function such as aa;}
Then, I want to sort the user table by the cc field. The problem is that the list is sorted by the first page of the page.
After clicking the second page, the entire data is sorted from small to large instead of by overall sorting.
Cc is a frequently changed field and cannot be stored in the database. it must be computed to find a solution.
Reply to discussion (solution)
You can insert your formula aa into the statement to calculate CC during query (if aa is not complex) and then sort it by CC directly.
You can insert your formula aa into the statement to calculate CC during query (if aa is not complex) and then sort it by CC directly.
For example, the distance value of the SQL statement I wrote cannot be calculated.
$sql = "select id,avg_point,name,xpoint,ypoint,index_img,dp_count,deal_cate_id,ratio,note ,".intval(GetDistance($ypoint,$xpoint,'ypoint','xpoint')*1000)." as distance from ".DB_PREFIX."supplier_location where ".$where;
// Write your aa function as a mysql function, and then use sqlselect *, aa (id) cc from table order by cc desc;
// Write your aa function as a mysql function, and then use sqlselect *, aa (id) cc from table order by cc desc;
The aa function is a self-written function that implements a specific function. mysql does not
Then how should we implement the msyql user-defined function aa (variable 1, variable 2, Table Field 1, table Field 2) like this)
Then how should we implement the msyql user-defined function aa (variable 1, variable 2, Table Field 1, table Field 2) like this)
// Your aa function requires four variables select *, aa (variable 1, variable 2, Field 1, field 2) cc from table order by cc desc;
Http://www.baidu.com? Wd = mysql % 20% E8 % 87% AA % E5 % AE % 9A % E4 % B9 % 89% E5 % 87% BD % E6 % 95% B0 & rsv_spt = 1 & issp = 1 & f = 8 & rsv_bp = 0 & ie = UTF-8 & tn = baiduhome_pg & rsv_enter = 1 & rsv_sug3 = 3 & rsv_sug4 = 97 & rsv_sug1 = 3 & rsv_sug2 = 0 & inputT = 2568
Use create temporary table temp
Create a temporary table and save it as a field in temp after cc computation. sort the temporary table by page.
Use create temporary table temp
Create a temporary table and save it as a field in temp after cc computation. sort the temporary table by page.
Thank you for your enthusiastic answer. cc is actually a series of businesses drawn from the user's location. if a temporary table is used, there will be too much data for multiple users.
You only calculated some of the data you read. of course, the sorting effect is not as expected.
If you want to sort by distance, you can do this:
Assume that the X and Y fields in the table store the merchant's longitude and latitude respectively.
$ X and $ y indicate the longitude and latitude of the user.
Select * from user order by abs ($ x-X), abs ($ y-Y) limit ($ page-1) * 5;
Must be all ?? After sorting ?. No? No ?? .