How to query ranking in php mysql

php Mysql query ranking method: 1. Rank all users through SQL statements; 2. Through "SELECT b.uid,b.rownum FROM(SELECT t.*, @rownum:...) " statement can query the ranking of a specific user.

The operating environment of this article: Windows 7 system, PHP version 7.1, DELL G3 computer

How to query the ranking of php mysql?

PHP mysql implements ranking and queries the ranking of specified users

The situation is as follows:

Implement ranking for the point size [point] of all users in the user table and get The specific ranking of a certain user's points

1. First, rank all users

$sql = "SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r,(SELECT uid,point FROM user ORDER BY `point` DESC) AS t ";
$sql = "SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r,(SELECT uid,point FROM user ORDER BY `point` DESC) AS t ";

Notes: 1. SELECT @rownum := 0: means assigning an initial value of 0 to rownum

2. @rownum := @rownum 1: Indicates adding 1 to rownum. The statement will start from 1, and each row will automatically add 1.

The above code will be based on the point in the user table Sort from largest to smallest.

2. Get the ranking of a specific user

Principle: Treat the data obtained in the previous step as a table, and query the ranking of a specific user based on uid

$sql = "SELECT b.uid,b.rownum FROM(SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r,(SELECT uid,point FROM user ORDER BY `point` DESC) AS t) AS b WHERE b.uid = {$uid} ";

Query results: {"uid":"300462","rownum":"10"}

where rownum is the ranking corresponding to the user.

Recommended learning: "PHP Video Tutorial"

The above is the detailed content of How to query ranking in php mysql. For more information, please follow other related articles on the PHP Chinese website!