Get data from database and create needed JSON format

Question

I have this php code that fetch data from database activity:

try {

$dateString = '09.03.2014';
$startDate = new DateTime($dateString);
$endDate = clone $startDate;
$startDate = $startDate->format("U") . PHP_EOL;
$period = new DateInterval('P1M');

$endDate->add($period);
$endDate = $endDate->format("U") . PHP_EOL;

$interval = new DateInterval('P1D');
$sql = <<<EOD
SELECT timestamp_day,name FROM activity WHERE timestamp_day BETWEEN $startDate AND $endDate
ORDER BY timestamp_day
EOD;
$data = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$output = ['data' => $data];
$jsonTable = json_encode($output); //$date->format("U") . PHP_EOL;//format your date to timestamp here


    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
    echo $jsonTable;

This code give me data from date to date and I get this JSON:

data: [{timestamp_day:1394319600, name:Meeting}, {timestamp_day:1394319600, name:Car repair},…]
0: {timestamp_day:1394319600, name:Meeting}
1: {timestamp_day:1394319600, name:Car repair}
2: {timestamp_day:1394406000, name:Travel}
3: {timestamp_day:1394492400, name:Work}
4: {timestamp_day:1394578800, name:Vacation}

(copied from chrome browser console)

Now you can see that I have a from some dates two records (0-1) and I want to put on the same object so to be like this:

 {timestamp_day:1394319600, name:Meeting, Car repair}
 {timestamp_day:1394406000, name:Travel}
 {timestamp_day:1394492400, name:Work}
 {timestamp_day:1394578800, name:Vacation}

Also I have one more question how I can now work with this element becouse I need to put in html code: timestamp_day ?

Also dates that dont have a record I nee to put in JSON so to be something like:

 {timestamp_day:1394406000, name:'<a>No data</a>'}

Some ideas?

Barmar · Accepted Answer · 2014-11-08 18:19:32Z

2

Use GROUP_CONCAT to combine values from records with the same timestamp:

SELECT timestamp_day, GROUP_CONCAT(name SEPARATOR ', ') AS name 
FROM activity 
WHERE timestamp_day BETWEEN $startDate AND $endDate
GROUP BY timestamp_day
ORDER BY timestamp_day

P.S. you should use prepared statements instead of variable substitution.

Here's how to use a prepared statement and group it by day:

$stmt = $db->prepare("
    SELECT DATE(FROM_UNIXTIME(timestamp_day)) AS date, GROUP_CONCAT(name SEPARATOR ', ' ORDER BY timestamp_day) AS name 
    FROM activity 
    WHERE timestamp_day BETWEEN :startDate AND :endDate
    GROUP BY date
    ORDER BY date");
$stmt->bindParam(':startDate', $startDate);
$stmt->bindParam(':endDate', $endDate);
$stmt->execute();
$data = $stmt->fetchAll();

edited Nov 8, 2014 at 18:19

answered Nov 8, 2014 at 17:07

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

2 Comments

James D. Over a year ago

please show here in answer how to use prepared statements, also can you tell me how to add empty rows if there is no data for that day: {timestamp_day:1394406000, name:'<a>No data</a>'}

James D. Over a year ago

Can I use here Interval so to check timestamp_day by day interval not every second...

Collectives™ on Stack Overflow

Get data from database and create needed JSON format

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related