Create json object from mysql database

Question

Hi I am trying to figure out how to create a json object with a very specific format using php and mysql:

My mysql table looks like this (Not the prettiest, I know):

CREATE TABLE IF NOT EXISTS `room120` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp_start` datetime NOT NULL,
  `timestamp_end` datetime NOT NULL,
  `month` int(2) NOT NULL,
  `day` int(2) NOT NULL,
  `year` int(4) NOT NULL,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `phone` text NOT NULL,
  `title` text NOT NULL,
  `start` varchar(5) NOT NULL,
  `end` varchar(5) NOT NULL,
  `approved` enum('true','false','new') NOT NULL DEFAULT 'new',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

How I need my JSON object to look:

[
    "10-23-2013": {
        0: {
            id : 1,
            title : "Hello World",
            time : "8:00 am - 10:00 am"
        },
        1: {
            id : 2,
            title : "Hello Universe",
            time : "1:00 pm - 3:00 pm"
        }
    }
]

I have my cuurent php loop that builds the id and title part but I am having an issue building the part that has the date.

Here is my php loop (Yes I know there is no code for building the date part, I am trying to figure it out.):

$return_arr = array();
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
    $date = str_pad($row[month], 2, "0", STR_PAD_LEFT).'-'.str_pad($row[day], 2, "0", STR_PAD_LEFT).'-'.$row[year];
    $start_time = DATE("g:i a", STRTOTIME($row[start]));
    $end_time = DATE("g:i a", STRTOTIME($row[end]));

    $row_array[id] = $row[id];
    $row_array[title] = $row[title];

    array_push($return_arr, $row_array);
}
echo json_encode(array("event" => $return_arr));

It currently returns something like this:

Object {event: Array[15]}
  event: Array[15]
    0: Object
      id: "1"
      title: "Hello World"

@Jeroen Those work fine, the problem is array push, I am unable to put the date where I want it instead where the date is it just has 0,1,2, etc. — Robert E. McIntosh
– Robert E. McIntosh, Commented Oct 23, 2013 at 18:07
Look at Cristian Bitoi's post. that should inject the date level in your array. — Jeroen
– Jeroen, Commented Oct 23, 2013 at 18:11

Cristian Bitoi · Accepted Answer · 2013-10-23 18:14:15Z

3

You need to store in $return_arr another sub-array row. Look here:

$return_arr = array();
while ( $row = $result->fetch_array(MYSQLI_ASSOC) ) {

    $date = str_pad($row[month], 2, "0", STR_PAD_LEFT).'-'.str_pad($row[day], 2, "0", STR_PAD_LEFT).'-'.$row[year];
    $start_time = DATE("g:i a", STRTOTIME($row[start]));
    $end_time = DATE("g:i a", STRTOTIME($row[end]));

   // create rowArr
    $rowArr = array(
        'id' => $row['id'],
        'title' => $row['title'],
        'time' => $startTime . ' - ' . $endTime
    );

    // store rowArr in $return_arr
    $return_arr[$date][] = $rowArr;

}

// display json encode

echo json_encode(array("event" => $return_arr));

Now your $return_arr is a multi dimensional array and should be echoed well.

edited Oct 23, 2013 at 18:14

answered Oct 23, 2013 at 18:08

Cristian Bitoi

1,5571 gold badge10 silver badges14 bronze badges

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

5 Comments

Robert E. McIntosh Over a year ago

Always the simplest answer, isn't it? haha. This works perfectly!

Jeroen Over a year ago

$return_arr[$start_time] > $return_arr[$date] , no?

Chris Baker Over a year ago

Personally, I would add one line, above // store rowArr in $return_arr -- if (!array_key_exists($start_time, $return_arr)) $return_arr[$start_time] = array();

Robert E. McIntosh Over a year ago

why would I use time instead of date?

Cristian Bitoi Over a year ago

My mistake. yes. date should be

Collectives™ on Stack Overflow

Create json object from mysql database

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related