CODEWITHSUNDEEP
phpsql
lol25500
lol25500

Reputation: 92

Group SQL data by date column and count occurrences per date

I am trying to make some statistics over visits on my site. I know that I could use Google Analytics(which I do), but I want to try to do something myself and it is a good way to learn it.

Problem: I select dates in my database and sort them to fit this week. After that, I want to add them to a json file. That json file are used by CanvasJS to make a Chart. I have tried some different ways, just to get it simi-working. But the format of the json array, is not the one CanvasJS want.

What I need:

{ visits:[[2019-02-12, 49,],[2019-02-13,40,],[2019-02-14,46,],[2019-02-15,37,], [2019-02-16,31,],[2019-02-17,38,],[2019-02-18,4,] }

What I get:

{ "visits":{"2019-02-12":49,"2019-02-13":40,"2019-02-14":46,"2019-02-15":37,"2019-02-16":31,"2019-02-17":38,"2019-02-18":4} }

My PHP Script:

// Get first and last day of the current week
$first_day = date('Y-m-d', strtotime("- 6 days"));
$last_day = date('Y-m-d', strtotime("+ 1 days "));

// Return all results the past 7 days
$sql = "SELECT date FROM table WHERE date >= '" . $first_day . "' AND date < '" . $last_day . "'";
if($result = $conn->query($sql)){
    
    $response = array(); 
    $visits = array();

    while($row = $result->fetch_array(MYSQLI_ASSOC)){
        
        $old_date = $row['date'];
        $old_date_timestamp = strtotime($old_date);
        $new_date = date('Y-m-d', $old_date_timestamp);
        
                // I don't need the keys, but I cant avoid it to
                // get it to work....
        $visits[] = array(
            'date' => $new_date
        );
        
    }
    
    // Add sum of Dates
    $response['visits'] = array_count_values(array_column($visits, 'date'));
    
    // Save to json File
    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($response));
    fclose($fp);
}
$conn->close();

Upvotes: 2

Views: 705

Answers (2)

Kyaw Kyaw Soe
Kyaw Kyaw Soe

Reputation: 3370

What I understand is you want to group same date as array inside visits, if I understand correctly here is a solution.

Add one for more loop to make format php array before change it to json string.

$first_day = date('Y-m-d', strtotime('- 6 days'));
$last_day = date('Y-m-d', strtotime('+ 1 days '));

$sql = "SELECT date FROM table WHERE date >= '" . $first_day . "' AND date < '" . $last_day . "'";
if ($result = $conn->query($sql)) {
    $response = [];
    $visits = [];

    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
        $old_date = $row['date'];
        $old_date_timestamp = strtotime($old_date);
        $new_date = date('Y-m-d', $old_date_timestamp);

        $visits[] = [
        'date' => $new_date
    ];
    }

    // here the change start
    $format = [];
    foreach ($visits as $visit) {
        $format[$visit['date']][] = $visit['date'];
    }
    $response['visits'] = array_values($format);
    // here the change end

    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($response));
    fclose($fp);
}
$conn->close();

if you don't need the key date here another solution

$first_day = date('Y-m-d', strtotime('- 6 days'));
$last_day = date('Y-m-d', strtotime('+ 1 days '));

$sql = "SELECT date FROM table WHERE date >= '" . $first_day . "' AND date < '" . $last_day . "'";
if ($result = $conn->query($sql)) {
    $response = [];
    $visits = [];

    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
        $old_date = $row['date'];
        $old_date_timestamp = strtotime($old_date);
        $new_date = date('Y-m-d', $old_date_timestamp);

        $visits[$new_date][] = $new_date; // change here
    }

    $response['visits'] = array_values($visits); // change here

    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($response));
    fclose($fp);
}
$conn->close();

Explanation

in PHP there are two type of array indexed and associative, when you change PHP array to json string, indexed array goes to be array and associative goes to be object.

example

$indexed = [
    0 => 'foo',
    1 => 'bar'
];

$associative = [
    'one' => 'foo',
    'two' => 'bar'
];

var_dump(json_encode($indexed));
// [
//   "foo",
//   "bar"
// ]

var_dump(json_encode($associative));
// {
//   one: "foo",
//   two: "bar"
// }

in my code I use visit date as a key, that way the same date will goes into same array, and I array_values to convert associative to indexed

Upvotes: 0

Phil
Phil

Reputation: 165059

Ignoring any quote-related issues you might think are problems (but aren't), it seems your main difference is between what you want...

[[2019-02-12, 49,],...

and what you have...

{"2019-02-12":49,...

This is because array_count_values() creates an associative array, with your dates as keys.

Your issue could be greatly simplified by getting your database to group and count instead of doing it in PHP. You can also benefit from using a prepared statement instead of direct value injection.

// Get first and last day of the current week
$first_day = date('Y-m-d', strtotime("- 6 days"));
$last_day = date('Y-m-d', strtotime("+ 1 days "));

$sql = <<<_SQL
SELECT DATE(`date`), COUNT(1)
FROM `table` WHERE `date` BETWEEN ? AND ?
GROUP BY DATE(`date`)
_SQL;

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $first_day, $last_day);
$stmt->execute();
$stmt->bind_result($date, $count);
while ($stmt->fetch()) {
    $visits[] = [$date, $count];
}
$response = [ 'visits' => $visits ];

// Save to json File
$fp = fopen('results.json', 'w');
fwrite($fp, json_encode($response));
fclose($fp);

Upvotes: 1

Related Questions