Question Extract user id and total points of each users

[galerio]

Hi,
I need to extract a list of user id with associated their activity points (the total).

Can you help me to find a db command to do that from shell?

I need it because of lack of contest's feature to count point in a range of time.

My intent is to register all the points of each the users at the begin of my contest, and then to register all the points at the end and make a rank with Excel.

Any help, please?

 

[Fillip H.]

SELECT dbtech_vbactivity_pointscache FROM user WHERE userid = x

Alternatively

SELECT SUM(points) AS points FROM dbtech_vbactivity_pointslog WHERE userid = x AND dateline >= start AND dateline <= end

Where start and end are unix timestamps of the start and end respectively.

 

[galerio]

You are fantastic! 4 minutes to answer! Great! I'll try it and let you know.

 

[galerio]

I have made this script for who wants to download the complete list of users' id with their total points gained.
You can use it in Excel and manage it for example to know how many points the users have made from day x to day y and find the best one (useful for contests):

<?php
mysql_connect("localhost","user","password") or die("error connection");
mysql_select_db("database-name") or die("error connection");

   $query    = <<<QUERY
SELECT userid, SUM(dbtech_vbactivity_pointscache) AS punti
    FROM user
    GROUP BY userid
    ORDER BY punti DESC;
QUERY;

    $elenco = mysql_query($query) or die(mysql_error());

    Header("Content-Type: application/vnd.ms-excel");
    Header("Content-Disposition: attachment; filename=\"query.xls\"");

    print "ID Utente\tPunti\n";
    while($riga = mysql_fetch_array($elenco))
    {
        print implode("\t", $riga);
        print "\n";
    }
    mysql_free_result($elenco);
?>