Geek Answers Handbook

Order the table numerically and get the list number

On my WordPress site, I have custom metadata reputation. The value is numeric and is used in the same way that points / reputations are used here in Stackoverflow.

I want the getnumber that the user puts in front of others.

Browsing the database, all I need to do is list the table in descending order, and I see who takes 1 (has the most points), etc ...

Is there any way to show rank number to user? I guess the best way is to sort the table in descending order and somehow get the user's placement number in this list?

I want to get the user ranking number. Similar to the function below, which allows you to calculate the total user account. I need user_id for the argument:

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;
}
+4
source share
1 answer

You can use the join for your user table, metadata, and order with the meta_value stored in the column,

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

Using * 1in order is a simple trick for integers, and desc will list the user by their reputation so that a user with a higher reputation is listed first and so on.

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 );

Scroll through your results

foreach($results as $r){
echo $r->rank; // rank of user
}

Edit Comment

here is your function

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);

, , , , , , @variables Mysql WPDB class

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);

, group_concat_max_len, 1024. , max_allowed_packet.

+7

More articles:


All Articles