Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+3 votes
1.4k views
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...

 

by
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
by
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:

<?php  

    // CONNECT TO DATABASE
    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 />";
            $users[$row['handle']]++;
        }
    }
    
    // output the activity points for each user this week
    echo "<h2>Most active users this week</h2>";
    arsort($users);
    foreach ($users as $key => $val) {
        echo "$key ($val points)<br />";
    }
    die();
    

    // 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?

?>

 

by
implemented on this site (bottom right, scroll a bit):
http://www.gute-mathe-fragen.de/

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