php - 获取过去7天的点击总和

Question

I have a table xeon_users_rented, with: clicks0, clicks1, clicks2, clicks3, clicks4, clicks5, clicks6

Each day, clicks0 will increase, and every day at midnight, a cronjob will run, making clicks0 = clicks1 (setting todays clicks, to yesterday clicks), and then set clicks0 to zero.

What I am trying to achieve is I want to make a graph, that shows the sum of clicks0, clicks1 etc., where clicks0 is todays date.

I have the query below:

$data = array();
for ($x = 0; $x <= 6; $x++) {
    $date = date("Y/m/d", time() - ($x * 86400));
    $queryE = $dbh->prepare("SELECT SUM(clicks$x) FROM xeon_users_rented WHERE user_by=:username");
    $queryE->bindParam(":username", $userdata['username']);
    $queryE->execute();
    $row = $queryE->fetch(PDO::FETCH_ASSOC);
    $dates[] = date("Y/m/d", time() - ($x * 86400));   
    $data[] = ($row['clicks'.$x.''] > 0 ? $row['clicks'.$x.''] : 0);
}
$days = array('Today');
for ($i = 0; $i < 6; $i++) {
    $days[$i] = date('d-m', strtotime('-'.($i + 0).' day'));
}

The $days is working perfectly - it will print out today, and the last couple of days.

The $data is not working. It is just printing out:

0,0,0,0,0,0,0

Can someone please help me out here.

Answer 1

The column from your SUM isn't going to be named clicks$x. It will be named something like SUM(clicks1).

Provide an explicit name in the SQL, like

SELECT SUM(clicks$x) as clickSum ...

Then reference it in row as

$row['clickSum']