mysql php array to specific format

Question

I have the following code:

   <?php
//mysqli query here

$table = array();
$table['cols'] = array(

array('id' => '', 'label' => 'Date', 'type' => 'date'),
array('id' => '', 'label' => 'Amount ', 'type' => 'number'),
); 
$rows = array();
foreach($result as $row){
$temp = array();

$date1 = date_create_from_format('Y-m-d H:i:s', $row['Date']);
$date2 = date_format($date1, 'm-d-Y');

$temp[] = array('v' => (string)'new Date("'.$date2.'")');
$temp[] = array('v' => (float) $row['Amount']);
$rows[] = array('c' => $temp);
}

$result->free();
$table['rows'] = $rows;
$jsonTable = json_encode($table, true);
echo $jsonTable;
?>

This outputs the following data to a google line chart:

{"cols":[
{"label":"Reading Date","type":"date"},
{"label":"Reading ","type":"number"},
"rows":[
{"c":[{"v":"new Date(10\/04\/2015)"},{"v":0.4}]},
{"c":[{"v":"new Date(02\/18\/2016)"},{"v":0.6}]}]}

However i need it to be in the exact following format for the chart to work properly:

{"cols":[
      {"label":"Reading Date","type":"date"},
      {"label":"Cl Reading(mg\/l) ","type":"number"}
    ],
    "rows":[
      {"c":[{"v":new Date("10/04/2015")},{"v":0.4}]},
      {"c":[{"v":new Date("02/18/2016")},{"v":0.6}]}
    ]}

so really im looking at just one line that needs changing but cant seem to get it into the format i need:

$temp[] = array('v' => (string)'new Date("'.$date2.'")');

Can anyone help me on this? I know its probably simple but just cant seem to get it..

score 2 · Accepted Answer · edited May 23 '17 at 11:50

something more readable would help

<?php
//mysqli query here

$table = array(
    'cols' => array(
        array('id' => '', 'label' => 'Date', 'type' => 'date'),
        array('id' => '', 'label' => 'Amount ', 'type' => 'number'),
    ),
    'rows' => array(),
);
foreach($result as $row){
    $date1 = date_create_from_format('Y-m-d H:i:s', $row['Date']);
    $date2 = date_format($date1, 'Y, m, d');   // this
    $table['rows'][] = array(
        'c' => array(
            array('v' => '\'Date('.$date2.')\''),  // and this
            array('v' => (float) $row['Amount']),
        )
    );
}

$result->free();
$jsonTable = json_encode($table, true);
echo $jsonTable;
?>

now, what is it that needs changed?

Edit:
OK, I see now... you're not wanting to generate JSON, you think you're wanting to generate javascript.. Don't.. just keep it JSON...
(see What is the difference between JSON and Object Literal Notation?)

Generate the Date objects after the fact. You'll have the date string.. just pass that do the Date constructor.

Edit 2:
see Google Documentation - Dates and Times Using the Date String Representation
you just need to surround new Date(...) in quotes (

Edit 3: I updated my code block to reflect this the google documentation

score 0 · Answer 2 · answered Feb 23 '16 at 09:52

Thanks to Brad Kent for pointing me in the right direction:

<?php

//mysql query 


    $table = array(
    'cols' => array(
        array('id' => '', 'label' => 'Date', 'type' => 'date'),
        array('id' => '', 'label' => 'Amount ', 'type' => 'number'),

    ),
    'rows' => array(),
);
foreach($result as $row){
    $date1 = date_create_from_format('Y-m-d H:i:s', $row['Date']);
    $date2 = date_format($date1, 'Y, m, d'); 
    $table['rows'][] = array(
        'c' => array(
            array('v' => 'Date('.$date2.')'), 
            array('v' => (float) $row['Amount'])

        )
    );
}

$result->free();
$jsonTable = json_encode($table, true);
echo $jsonTable;

    ?>

mysql php array to specific format

2 Answers2