Parsing MySQL to JSON array, with data manipulation in PHP

Question

I'm tring to parse a MySQL request to a JSON file, with this PHP function,

<?php
    //Define possible Argument request
    $format = $_GET['format'];

    if($format=='json')    {
        header("Content-type: text/json");
    }

    //Define database credential
    $servername = "localhost";
    $username   = "";
    $password   = "";
    $dbname     = "";
    try {
        //Open connection to mysql_db from defined Database credentials
        $connection = mysqli_connect($servername, $username, $password, $dbname) or die ("Error " . mysqli_error($connection));
        //Fetch table rows from mysql db
        $sql   = "SELECT TS, time1, time2, time3 FROM time ORDER BY TS";
        $result = mysqli_query($connection, $sql) or die ("Error in Selecting " . mysqli_error($connection));
        //Create an array
        $series0 = array();
        $series0['name'] = 'TS';

        $series1 = array();
        $series1['name'] = 'time1';

        $series2 = array();
        $series2['name'] = 'time2';

        $series3 = array();
        $series3['name'] = 'time3';

while($r = mysqli_fetch_array($result))
 {
    $series0['data'][] = $r['TS'];
    $series1['data'][] = $r['time1'];
    $series2['data'][] = $r['time2'];
    $series3['data'][] = $r['timez3'];  
}

$result = array();
array_push($result,$series0);
array_push($result,$series1);
array_push($result,$series2);
array_push($result,$series3);

print json_encode($result, JSON_NUMERIC_CHECK);

        mysqli_close($connection);
    }

    catch(PDOException $e)  {
        echo $e->getMessage();
    }
?>

Who gives me this JSON output:

[{
    "name": "TS",   
    "data": ["00:08:31.227","00:09:02.434","00:09:38.573"]
  },{
    "name":"time1",
    "data":["00:05:11.220","00:05:05.420","00:03:32.540"]
  },{
    "name":"time2",
    "data":["00:04:11.820","00:08:05.660","00:01:24.330"]
  },{
    "name":"time3",
    "data":["00:02:11.990","00:09:05.570","00:15:25.200"]
}]

Now the problem is that I have to convert the times from "HH:MM:SS.fff", to seconds (SS.fff), but trying to apply my conversion function I came to an error becouse I cant applay my formulas to an array, is there a way to intercept the data and manipulate it before they goes inside the array?

And a second minor problem, becouse the final array will get 100+ dataseries, is there a way to don't assign manually the name to the series, but give them the same name as the column of MySQL table from where they came from??

Thank for all the suggestions, Best regards.

then just apply your formula function inside the while block, then push it inside when done — Kevin
– Kevin, Commented Apr 27, 2016 at 23:02

Amit · Accepted Answer · 2016-04-28 01:30:49Z

1

You can use mysql TIME_TO_SEC function.

    mysql> SELECT TIME_TO_SEC('22:23:00');
        -> 80580
    mysql> SELECT TIME_TO_SEC('00:39:38');
        -> 2378

so change

$sql   = "SELECT TS, time1, time2, time3 FROM time ORDER BY TS";

to

$sql   = "SELECT TS, TIME_TO_SEC(time1), TIME_TO_SEC(time2), TIME_TO_SEC(time3) FROM time ORDER BY TS";

answered Apr 28, 2016 at 1:30

Amit

6706 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Parsing MySQL to JSON array, with data manipulation in PHP

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related