CODEWITHSUNDEEP
phpmysqlarraysjsonformat
user3726827
user3726827

Reputation: 65

mysql php array to specific format

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..

Upvotes: 1

Views: 419

Answers (2)

user3726827
user3726827

Reputation: 65

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;

    ?>

Upvotes: 0

Brad Kent
Brad Kent

Reputation: 5098

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

Upvotes: 2

Related Questions