How to build a JSON array from mysql database

Question

Okay I have been racking my brain trying to build a JSON array from mysql. The array MUST be in the following format. I am using fullcalendar and want to make the events on the calendar dynamic. Below is the code that builds the array, but currently it does not get the information from mysql

$year = date('Y'); $month = date('m');  echo json_encode(array(      //Each array below must be pulled from database         //1st record         array(         'id' => 111,         'title' => "Event1",         'start' => "$year-$month-10",         'url' => "http://yahoo.com/"     ),           //2nd record          array(         'id' => 222,         'title' => "Event2",         'start' => "$year-$month-20",         'end' => "$year-$month-22",         'url' => "http://yahoo.com/"     )  )); 

Answer 1

Is something like this what you want to do?

$return_arr = array();  $fetch = mysql_query("SELECT * FROM table");   while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {     $row_array['id'] = $row['id'];     $row_array['col1'] = $row['col1'];     $row_array['col2'] = $row['col2'];      array_push($return_arr,$row_array); }  echo json_encode($return_arr); 

It returns a json string in this format:

[{"id":"1","col1":"col1_value","col2":"col2_value"},{"id":"2","col1":"col1_value","col2":"col2_value"}] 

OR something like this:

$year = date('Y'); $month = date('m');  $json_array = array(  //Each array below must be pulled from database     //1st record     array(     'id' => 111,     'title' => "Event1",     'start' => "$year-$month-10",     'url' => "http://yahoo.com/" ),       //2nd record      array(     'id' => 222,     'title' => "Event2",     'start' => "$year-$month-20",     'end' => "$year-$month-22",     'url' => "http://yahoo.com/" )  );  echo json_encode($json_array); 

Answer 2

The PDO solution, just for a better implementation then mysql_*:

$array = $pdo->query("SELECT id, title, '$year-month-10' as start,url    FROM table")->fetchAll(PDO::FETCH_ASSOC); echo json_encode($array); 

Nice feature is also that it will leave integers as integers as opposed to strings.