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

[mongoose00318]

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.

[Barand]

You can't pass identifiers (table/column names) as parameters

[Barand]

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    |
+---------------+-------+

 

[mongoose00318]

Cool man. Ya that works. I was trying to see how much of it I could do using just SQL and PHPMyAdmin. Thanks!