php汉字转数字排序,php – 以数字方式排序表并获取列表编号

在我的WordPress网站中,我有自定义用户元数据信誉.该值为数字,与Stackoverflow中的点/信号使用方式类似.

我想得到用户排名的人数.

浏览数据库,我需要做的就是按降序列出表格,我可以看到谁排名为1(最多点)等等……

有什么办法可以向用户显示排名吗？我猜最好的方法是按降序排序表,并以某种方式获取用户在该列表中的位置数量？

我想获取用户的排名.与下面的函数类似,获取用户的总评论数.我需要user_id作为参数：

function get_user_comments_count( $uid ){

global $wpdb;

$where = 'WHERE comment_approved = 1 AND user_id = ' . $uid ;

$comment_count = $wpdb->get_var("SELECT COUNT( * ) AS total

FROM {$wpdb->comments}

{$where}");

return $comment_count;

}

解决方法:

您可以使用连接作为用户表和元数据,并按列中存储的meta_value进行排序,

SELECT u.*

FROM `wp_users` u

JOIN `wp_usermeta` um ON(u.ID = um.`user_id`)

WHERE um.`meta_key` = 'reputation'

ORDER BY um.`meta_value` * 1 DESC

按顺序使用* 1是一个简单的转换技巧到整数,desc将按用户的信誉评分列出用户,这样就会首先列出信誉较高的用户,依此类推

global $wpdb;

$query=" SELECT u.*,um.`meta_value` as `rank`

FROM $wpdb->users u

JOIN $wpdb->usermeta um ON(u.ID = um.`user_id`)

WHERE um.`meta_key` = 'reputation'

ORDER BY um.`meta_value` * 1 DESC";

$results = $wpdb->get_results($query, OBJECT );

循环搜索结果

foreach($results as $r){

echo $r->rank; // rank of user

}

从评论中编辑

这是你的功能

function getReputationByUser($user_id){

if(!is_numeric($user_id)){

return false;

}

global $wpdb;

$query=" SELECT um.`meta_value` as `rank`

FROM $wpdb->usermeta um

WHERE um.`meta_key` = 'reputation'

AND um.user_id = '".$user_id."'

";

$result = $wpdb->get_row($query);

if(!empty($result)){

return $result->rank;

}

return false;

}

echo getReputationByUser(100);

上面的代码用于返回声誉,如果你想要基于声誉的用户等级你可以通过下面的查询得到这个,但我不确定你是否可以在WPDB类中使用@variables of Mysql

SELECT t1.`rank`

FROM(

SELECT

um.user_id,

@rownum:= @rownum + 1 `rank`

FROM

wp_usermeta um

CROSS JOIN (SELECT @rownum := 0) t

WHERE um.`meta_key` = 'reputation'

ORDER BY um.`meta_value` * 1 DESC

) t1

WHERE t1.user_id = 100

上述查询的功能

function getReputationByUser($user_id){

if(!is_numeric($user_id)){

return false;

}

global $wpdb;

$query=" SELECT t1.`rank`

FROM(

SELECT

um.user_id,

@rownum:= @rownum + 1 `rank`

FROM

$wpdb->usermeta um

CROSS JOIN (SELECT @rownum := 0) t

WHERE um.`meta_key` = 'reputation'

ORDER BY um.`meta_value` * 1 DESC

) t1

WHERE t1.user_id = '".$user_id."'";

$result = $wpdb->get_row($query);

if(!empty($result)){

return $result->rank;

}

return false;

}

echo getReputationByUser(100);

这是使用group_concat和find_in_set函数查找用户排名的另一种方法

SELECT user_id,FIND_IN_SET(

user_id,(SELECT GROUP_CONCAT( user_id ORDER BY meta_value * 1 DESC)

FROM `wp_usermeta`

WHERE meta_key ='reputation')

) AS rank

FROM wp_usermeta

WHERE meta_key ='reputation'

AND user_id = 100

function getReputationByUser($user_id){

if(!is_numeric($user_id)){

return false;

}

global $wpdb;

$query="SELECT user_id,FIND_IN_SET(

user_id,(SELECT GROUP_CONCAT( user_id ORDER BY meta_value * 1 DESC)

FROM $wpdb->usermeta

WHERE meta_key ='reputation')

) AS rank

FROM $wpdb->usermeta

WHERE meta_key ='reputation'

AND user_id = '".$user_id."'

";

$result = $wpdb->get_row($query);

if(!empty($result)){

return $result->rank;

}

return false;

}

echo getReputationByUser(100);

According to docs The result is truncated to the maximum length that

is given by the group_concat_max_len system variable, which has a

default value of 1024. The value can be set higher, although the

effective maximum length of the return value is constrained by the

value of max_allowed_packet.

标签：wordpress,php,mysql,database,wpdb

来源： https://codeday.me/bug/20190612/1224648.html