Convert MySQL data to JSON and include MySQL count

Question

I have a custom database table for a WordPress site. I am trying to save the data from MySQL call to JSON. The table contains a date column. I would like to count how many times each date appears. I would then like to save the date and the number of times it appears into a JSON object. Here is my code:

global $wpdb;
$table_name = $wpdb->prefix . "rlwpentries";

$query = $wpdb->get_results( "SELECT date, count(date) FROM $table_name group by date" );

$data = array();
foreach ($query as $row) {
$data[] = array('date' => $row->date, 'count' => $row->count);
}

print json_encode($data);

Here are my results:

[  
    {  
        "date":"0000-00-00 00:00:00",
        "count":null
    },
    {  
        "date":"2019-02-22 00:00:00",
        "count":null
    },
    {  
        "date":"2019-02-23 00:00:00",
        "count":null
    },
    {  
        "date":"2019-02-24 00:00:00",
        "count":null
    },
    {  
        "date":"2019-02-25 08:00:00",
        "count":null
    },
    {  
        "date":"2019-02-26 00:00:00",
        "count":null
    }
]

Can someone explain how I can get the correct "count" value to show up in my JSON?

Machavity · Accepted Answer · 2019-03-01 03:20:49Z

2

You have to cast the value first

SELECT date, COUNT(date) AS `count`

answered Mar 1, 2019 at 3:20

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert MySQL data to JSON and include MySQL count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related