0

I'm having some trouble with a project I'm working on regarding a report that shows user activity that I store in a mysql db table. The user activity data that gets stored are a time stamp, the user id, and the type of activity. On the front end, the tables display in each row the user, and totals the types of activities in each column, which I currently have working.

I would like to add an option on the front end that allows a user to filter the dates from which the data is being pulled(based off the time stamp), for example "filter last 7 days, last 30 days, last 60 days, etc" but I'm unsure how I would initially display this information and then have the option to filter by date.

Everything I have built is currently over Wordpress(so I'm using php for a lot, not sure if this is the best method to go about it either or if I should use js).

Any suggestion or nudge in the right direction is appreciated!

Below is a general example of the code

<table class="activityreport">
    <thead>
        <tr class="table100-head">
            <th class="column1">User</th>
            <th class="column1">Activity 1</th>
            <th class="column1">Activity 2</th>
            <th class="column1">Activity 3</th>
            <th class="column1">Activity 4</th>
            <th class="column1">Total Activities</th>
        </tr>
    </thead>
    <tbody>

<?php 
    global $wpdb;   
    $table_name = $wpdb->prefix . "_activities";
    $activity_log = $wpdb->get_results( "SELECT * FROM " . $table_name );
    $users = get_users();
    foreach($users as $user){
        $total_activities = 0;
        $total_activity1 = 0;
        $total_activity2 = 0;
        $total_activity3 = 0;
        $total_activity4 = 0;

        foreach($activity_log as $activity){
            if($activity->user_id == $user->ID){
                if($activity->activity_type == 'activity1'){
                    $total_activity1 += 1;
                }elseif($activity->activity_type == 'activity2'){
                    $total_activity2 += 1;
                }elseif($activity->activity_type == 'activity3'){
                    $total_activity3 += 1;
                }elseif($activity->activity_type == 'activity4'){
                    $total_activity4 += 1;
                }
            }else{}
            $total_activities = $total_activity1 + $total_activity2 +$total_activity3 + $total_activity4;

?>

<tr>
    <td><?php echo $user->first_name; ?></td>
    <td><?php echo $total_activity1; ?></td>
    <td><?php echo $total_activity2; ?></td>
    <td><?php echo $total_activity3; ?></td>
    <td><?php echo $total_activity4; ?></td>
    <td><?php echo $total_activities; ?></td>
</tr>

<?php
        }
    }
?>
    </tbody>
</table>
giovanni586
  • 3
  • 3
  • 1
    What exactly is the question? Are you stuck on something? Can you share code? – ficuscr May 02 '18 at 20:26
  • @ficuscr Sorry, little new to stackoverflow, I edited the original post to define the question a little better and add code – giovanni586 May 02 '18 at 20:58
  • Add an HTML form. That form when submitted should send data from the client to the server. It could be as simple as two text inputs with dates, though you probably want a date picker widget (jQuery or whatever). Server side you then take the date parameters and plug them into your SQL query. [Google](https://www.google.com/search?q=add+a+date+range+filter+php) should get you some examples. Don't forget to escape the user data! – ficuscr May 02 '18 at 21:09
  • You have several options, e.g. According to the front-end selected, let's say "last 7 days", then make a new Query to your DB and limit "last 7 days". Suggest that you take a look on [MySQL Select last 7 days ](https://stackoverflow.com/questions/24272335/mysql-select-last-7-days?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) – CK Wong May 03 '18 at 03:20

0 Answers0