Pulling data from MySQL into json array

json, mysql, php

Solution

If you wanted to do this with PDO then here is an example:

<?php 
$dbh = new PDO("mysql:host=localhost;dbname=DBNAME", $username, $password);

$sql = "SELECT `id`, `title`, `time`, `start`, `backgroundColor` 
        FROM my_table";

$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
//To output as-is json data result
//header('Content-type: application/json');
//echo json_encode($result);

//Or if you need to edit/manipulate the result before output
$return = [];
foreach ($result as $row) {
    $return[] = [ 
        'id' => $row['id'],
        'title' => $row['title'],
        'start' => $row['start'].' '.$row['time'],
        'backgroundColor' => $row['backgroundColor']
    ];
}
$dbh = null;

header('Content-type: application/json');
echo json_encode($return);
?>

Problem

I'm trying to pull data from my database using json in php. I have a few elements I need to specific then to post them on a page. I want to "fetch" the data from mysql and return it to a json_encode. How can I do this using the SELECT method. Some had used PDO methods and other have used mysql_assoc, which confuses me. For instance, I have rows of: 'id' , 'title' , 'start', 'backgroundColor'...etc. along with a default value for all of them. ($array[] = "someValue = default") I want it to export like so: ``` array( 'id' => 1, 'title' => "someTitle", 'start' => "2012-04-16", 'backgroundColor' => "blue", 'someValue' = > "default", ... ), .... )); ``` If anyone could help me with this with the best detail, I'd be awesome!

Original source