Jump to content

Creating a view that shows a specific users activity for the past week that is then able to be graphed


Recommended Posts

Here is my query so far:

SELECT DISTINCT
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 7 DAY AND user_id = 7) AS Monday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 6 DAY AND user_id = 7) AS Tuesday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 5 DAY AND user_id = 7) AS Wednesday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 4 DAY AND user_id = 7) AS Thursday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 3 DAY AND user_id = 7) AS Friday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 2 DAY AND user_id = 7) AS Saturday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 1 DAY AND user_id = 7) AS Sunday
FROM
	:TABLE ps
WHERE
    ps.user_id = :USER

The results it is giving me are all wrong so I'm not sure what I am doing wrong. I'm new to using views but I created a few this morning to help me quickly analyze certain data. I want to use the graphing feature in PhpMyAdmin more.

What I'd like to be able to do is have a view that shows how many records were entered into the production_status table by the user for each day of the last week; or even better each day of this month and then chart it on a bar graph.

Link to post
Share on other sites

Perhaps

##
##  create a test table
##
$db->exec("CREATE TABLE IF NOT EXISTS production_status (
             id int not null auto_increment primary key,
             submit_time datetime
          )
          ");
          
          
##
##  randomly add 1,000 records for last 2 months
##
$data = [];
for ($i=0; $i < 1000; $i++) {
    $r = rand(1, 60);
    $dt = date('Y-m-d H:i:s', strtotime("-$r days"));
    $data[] = "('$dt')"; 
}
$db->exec("INSERT INTO production_status (submit_time) VALUES " . join(',', $data));



##
##  Now the bit you need
##

$start_date = date('Y-m-d', strtotime('first day of this month'));

$res = $db->prepare("SELECT date_format(submit_time, '%a %d-%b-%y') as date
                          , COUNT(*) as total
                       FROM production_status
                       WHERE date(submit_time) >= ?
                       GROUP BY date
                       ORDER BY submit_time
                       ");
$res->execute([ $start_date ]);

RESULTS

+---------------+-------+
| date          | total |
+---------------+-------+
| Tue 01-Sep-20 | 14    |
| Wed 02-Sep-20 | 24    |
| Thu 03-Sep-20 | 15    |
| Fri 04-Sep-20 | 21    |
| Sat 05-Sep-20 | 15    |
| Sun 06-Sep-20 | 24    |
| Mon 07-Sep-20 | 17    |
| Tue 08-Sep-20 | 17    |
| Wed 09-Sep-20 | 15    |
| Thu 10-Sep-20 | 11    |
| Fri 11-Sep-20 | 15    |
| Sat 12-Sep-20 | 16    |
| Sun 13-Sep-20 | 13    |
| Mon 14-Sep-20 | 11    |
| Tue 15-Sep-20 | 21    |
| Wed 16-Sep-20 | 21    |
| Thu 17-Sep-20 | 15    |
| Fri 18-Sep-20 | 17    |
| Sat 19-Sep-20 | 17    |
| Sun 20-Sep-20 | 14    |
+---------------+-------+

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.