Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+3 votes
in Plugins by
edited by

I am still searching for a solution to reward participating users. The previous question "Who is able to develop a plugin for most-points-per-week-per-user?" showed that it needs some plugin work to have the user points stored and compared every week.

As I am searching for the least effort, I thought we could do it differently:

Why not use the table qa_eventlog that has *datetime* stored, and search for user activities (instead of points) for a given time interval?

This way we could show "most active users this week" (or "top contributors this week") instead of "top users" (which are all time).

Any other suggestions or ideas? Thanks!
+ Is there anybody who can program that quickly, if not, I try to do it next month...


Great Idea. With this we can find out the most dedicated user for the week or month and we can also give some prize to those dedicated user. It will attract the user to our site also.

1 Answer

+1 vote
edited by

Alright, I took my time today and did the basic PHP for the most active user list, check it out below.

But hold on: I have no idea how to put this into a plugin, so: Who can help putting this into a plugin?

copy + paste the code into a php file, run it, enjoy:


    require_once( 'qa-config.php' );
    mysql_connect(QA_MYSQL_HOSTNAME, QA_MYSQL_USERNAME, QA_MYSQL_PASSWORD) or die(mysql_error());
    mysql_select_db(QA_MYSQL_DATABASE) or die(mysql_error());
    // get week range from current date, week starts sunday
    list($weekstart, $weekend) = x_week_range( date('Y-m-d') );

    // 'handle' holds each username, ignore anonym users with handle = NULL
    $query = mysql_query("SELECT handle,event from `qa_eventlog` WHERE `datetime` BETWEEN '$weekstart' AND '$weekend' AND `handle`!='NULL';") or die(mysql_error());
    /*     events that are regarded for activity points: badge_awarded, q_post, a_post, c_post, in_a_question, in_c_question, in_c_answer, q_vote_up, in_q_vote_up, in_a_vote_up
        Problem: in event_log something like "in_q_vote_up" registers the user who RECEIVED the vote, but not the one how voted!
        So we can only take the basic events that originate from the user: badge_awarded, q_post, a_post, c_post, q_vote_up, a_vote_up
        Edit: I checked the event logs as one of my users had many activity points without any answer or question, this was because he voted a lot (q_vote_up and a_vote_up), so those must be removed as well.
        Also the badge_awarded had to be removed as new users already get several awards.
        This is what was left:
    $activityEvents = array("q_post", "a_post", "c_post");
    $users = array();
    // count 1 point for each activity
    while ($row = mysql_fetch_assoc($query)) {
        if(in_array($row['event'], $activityEvents)) {
            // echo implode(",", $row)."<br />";
    // output the activity points for each user this week
    echo "<h2>Most active users this week</h2>";
    foreach ($users as $key => $val) {
        echo "$key ($val points)<br />";

    // function to get week range from given date
    // credits to http://stackoverflow.com/questions/923925/get-start-and-end-days-for-a-given-week-in-php
    function x_week_range($date) {
        $ts = strtotime($date);
        $start = (date('w', $ts) == 0) ? $ts : strtotime('last sunday', $ts);
        return array(date('Y-m-d', $start),
                     date('Y-m-d', strtotime('next saturday', $start)));

    // general question: handle and userid are saved in db together?



why not look at an existing plugin as a template?
yes, this is what i tried. But I was not able to use SELECT statements.

From another plugin I saw that I have to use the "API" (I mean the functions from qa-db.php, such as qa_db_single_select()) to connect. I have no idea how to connect to the table "qa_eventlog" that was created by another plugin.

I guess my programming skills and the level of q2a-knowlegde are not good enough.
To connect to the db, just call qa_db_connection().

Like this:

$query = your_query;
$db = qa_db_connection();
$result = @mysql_query($query, $db );
while ( $row = mysql_fetch_assoc($result) ) {
Just do it like the history plugin:

        $events = qa_db_query_sub(
            'SELECT event, BINARY params as params, UNIX_TIMESTAMP(datetime) AS datetime FROM ^eventlog WHERE userid = # AND DATE_SUB(CURDATE(),INTERVAL # DAY) <= datetime ORDER BY datetime DESC'.(qa_opt('user_act_list_max')?' LIMIT '.(int)qa_opt('user_act_list_max'):''),
            $userid, qa_opt('user_act_list_age')
@Merkus: (Edit) I tried that and it works!

@NoahY: I tried:
$events = qa_db_query_sub(
"SELECT handle,event from ^eventlog WHERE `datetime` BETWEEN '$weekstart' AND '$weekend' AND `handle`!='NULL';", $weekstart, $weekend );

However, this is giving me: "Insufficient parameters in query: SELECT handle,event from qa_eventlog WHERE `datetime` BETWEEN '2012-05-27' AND '2012-06-02' AND `handle`!='NULL';"
This is no MySQL error, but comes from the qa_db_query_sub(). What parameter should I pass?

implemented on this site (bottom right, scroll a bit):

Will release the plugin tomorrow (via github).

@NoahY: If your qa_db_query_sub() is the way to go (performance?) I would use this function.
You are inserting your variables manually and then telling it they are to be inserted... try this:

$events = qa_db_query_sub(
    "SELECT handle,event from ^eventlog WHERE datetime BETWEEN $ AND $ AND handle !=NULL", $weekstart, $weekend

Using the built-in functions has at least the benefit of ensuring no SQL injection.  That and it is convenient to use the substitution method as above.
edited by
Tried that, but it is not working. $events is empty.

EDIT: Okay, the problem was mysql. I need to put some more hyphens, this works:

$events = qa_db_query_sub("SELECT handle,event from `^eventlog` WHERE `datetime` BETWEEN $ AND $ AND `handle`!='NULL'", $weekstart, $weekend);