Get data as array from DB for each array item in WordPress

user_10242841 0
Viewed 565

I'm coding a reactions plugin for WordPress and trying to create a chart with chart.js for admins of past 7 days reaction counts for each day. User reactions are recorded in database in following columns: postId, reactedToand reactedDate. example row will be 182, Haha and 27 Jun 2019.

I'm generating an array of past 7 days dates with the same format in JavaScript to create labels for the chart and also trying to send it on back-end via AJAX to get the count of each of this date record from database in an array format. So, for example, the database contains this data:

postId reactedTo reactedDate
145 Like 22 Jun 2019
182 Haha 24 Jun 2019
182 Haha 27 Jun 2019

I'm generating array of past 7 days using this code in JS:

    var months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
    var lastWeek = [];
    for (var i = 0; i < 7; i++) {
        var d = new Date();
        d.setDate(d.getDate() - i);
        lastWeek.push(d.getDate() + ' ' + months[d.getMonth()] + ' ' + d.getFullYear());
    }
    lastweek = lastWeek.reverse()

    console.log(lastweek);

outputs ["21 Jun 2019","22 Jun 2019","23 Jun 2019","24 Jun 2019","25 Jun 2019","26 Jun 2019","27 Jun 2019"]

Then I'm sending it to the server via AJAX and generating chart on success.

    jQuery.ajax({
        url: ajaxurl,
        dataType: 'text',
        type: 'POST',
        data: {
            action: 'reactions_analytics',
            dates: JSON.stringify(lastWeek)
        },
        success: function(response, textStatus, jqXhr) {
            var reactionsChart = new Chart(ctx, {
                type: 'line',
                data: {
                    labels: lastWeek,
                    datasets: [{
                        data: response,
                }
            });
        }
    });

The response from the server should be an array of numbers count for each date from the database. if date not found, then 0. So it should be [0,1,0,1,0,0,1].

Here's my current PHP code to search in the database and generate an array of date counts in the same order, but it outputs empty array [] instead.

    public function reactionsAnalytics() {
        global $wpdb;
        $tableName = $wpdb->prefix.'reactions';
        $dates = json_encode($_POST['dates']);
        $reacts = $wpdb->get_results("SELECT reactedDate, count(*) AS count FROM {$tableName} WHERE reactedDate IN ({$dates}) GROUP BY reactedDate", ARRAY_A);

        $result = array();
        foreach ($reacts as $react) {
            $result[] = $react['count'];
        }

        wp_die(json_encode($result));
    }

So I think I need help with my PHP. What is not correct in my logic? Can it do in a better way?

Basically I'm trying to send this to server: ["21 Jun 2019","22 Jun 2019","23 Jun 2019","24 Jun 2019","25 Jun 2019","26 Jun 2019","27 Jun 2019"]

And get same array with count numbers for each date found in DB [0,1,0,1,0,0,1]

wordpress mysql php
2 Answers

change the code as follows : 

public function reactionsAnalytics() {
global $wpdb;
$tableName = $wpdb->prefix.'reactions';
$dates = str_replace("\\", "", $_POST['dates']);
$reacts = $wpdb->get_results("SELECT reactedDate, count(*) AS count FROM {$tableName} WHERE reactedDate IN ({$dates}) GROUP BY reactedDate", ARRAY_A);

$result = array();
foreach ($reacts as $react) {
    $result[] = $react['count'];
}

wp_send_json_success($result);

}

Change the js code as follows:

jQuery.ajax({
url: ajaxurl,
dataType : "json",
type: 'POST',
data: {
    action: 'reactions_analytics',
    dates: lastWeek.reverse()
},
success: function(response, textStatus, jqXhr) {
    if ( response.success === true ) {
        var reactionsChart = new Chart(ctx, {
                    type: 'line',
                    data: {
                        labels: lastWeek,
                        datasets: [{
                            data: response.data,
                    }
                });
        } else {
            // error
            alert('error occured !');
        }
    }
}});
user_17768090

The issue was in $dates variable as it was returning array with [ ] and SQL Query needed ( ), so I used str_replace to replace [ ] with ( ) and query runs fine now.

user_102428410
Related