How to convert sql query result in Json Object {"timestamp": value}

Question

i want to use Cal-Heatmap to visualize data and my current sql result is returned as

[ { "dateStampSecs": "1499637600", "value": "1" } ]

but i want

[ { "1499637600" : 1 } ]

How do I stick them together?

I am using Codeigniter and below is my sql:

public function get_calData(){

        $this->db->select('unix_timestamp(STR_TO_DATE(date_start, "%Y-%m-%d")) dateStampSecs,count(*) as value');
        $this->db->from('calendar');
        $query = $this->db->get()->result();


        print json_encode($query,JSON_PRETTY_PRINT);
    }

Rahul Dhande · Accepted Answer · 2017-07-11 11:18:10Z

2

$dateStampSecs = array_column($query, "dateStampSecs");
$value = array_column($query, "value");
$result = array_combine($dateStampSecs, $value);
print json_encode($result,JSON_PRETTY_PRINT);

answered Jul 11, 2017 at 11:18

Rahul Dhande

4835 silver badges9 bronze badges

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

1 Comment

Cyber Over a year ago

Thanks, this is the one I was looking for.

sekaraja · Accepted Answer · 2017-07-11 11:08:40Z

0

you have to echo the result like following

echo json_encode($query);

exit();

answered Jul 11, 2017 at 11:08

sekaraja

1771 gold badge1 silver badge8 bronze badges

Comments

Bibhudatta Sahoo · Accepted Answer · 2017-07-11 11:15:51Z

0

Try like this

$finalData='';
foreach ($query as $data){
    $finalData[][$data['dateStampSecs']]=$data['value'];
}
$finalData=json_encode($finalData);
dd($query,$finalData);

It will give output as

edited Jul 11, 2017 at 11:15

answered Jul 11, 2017 at 11:07

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

1 Comment

Cyber Over a year ago

The SQL returns an array already, therefore decode doesn't work.

Shams Reza · Accepted Answer · 2017-07-11 11:34:00Z

0

Hope it will work

 $query = json_decode($query ,true);
    $query = [$query['dateStampSecs']=>$query['value']];
    dd(json_encode($query));`

answered Jul 11, 2017 at 11:34

Shams Reza

1,0979 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to convert sql query result in Json Object {"timestamp": value}

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related