How to get two Query in one JSON to display on Chart.js line Chart?

in my project statistics about charts are displayed.

One of the data is the number of hits on the website from this week and from the last week. Both are currently in their own line charts. There everything is also displayed without any problems. But now I want to put the data of the current week and the data of the last week into a single Line Chart to have a quick comparison of the visits of the website from this week to last week.

So I tried to get the output of both queries into one JSON, but I always get this error (json_encode() expects parameter 2 to be int, array given).

I think this should be solved differently, but I haven’t found any other idea or approach yet.

Code:

    header("Content-Type: application/json");

    include "../../../includes/db.php";

    $stmt = $connection->prepare("SELECT website_stats_homepage AS current_week FROM website_stats WHERE yearweek(DATE(website_stats_date), 1) = yearweek(curdate(), 1) ORDER BY website_stats_date");
    //$stmt = $connection->prepare("SELECT website_stats_homepage FROM website_stats WHERE website_stats_date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY AND website_stats_date < curdate() - INTERVAL DAYOFWEEK(curdate())-2 DAY");
    $stmt->execute();
    $result = $stmt->get_result();

    $stmt1 = $connection->prepare("SELECT website_stats_homepage AS last_week FROM website_stats WHERE website_stats_date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY AND website_stats_date < curdate() - INTERVAL DAYOFWEEK(curdate())-2 DAY");
    $stmt1->execute();
    $result1 = $stmt1->get_result();

    $data = array();
    foreach($result as $row) {
        $data[] = $row;
    }

    $data1 = array();
    foreach($result1 as $row1) {
        $data1[] = $row1;
    }

    $stmt->close();
    $stmt1->close();

    echo json_encode($data, $data1);

Answer

Pack the data in a associative array

echo json_encode(['current' => $data, 'last' => $data1]);

As an alternative you can try to merge the data already through the query by using sub-queries or joins.

Advertisement

Answer