All, I'm creating some JSON arrays by looping through my database and populating some values. The code to do this is:

$return_arr = array();
$fetch = mysql_query("SELECT * FROM calendar_events"); 
while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
    $row_array['id'] = $row['id'];
    $row_array['title'] = $row['title'];
    $row_array['start'] = $row['start'];
    $row_array['end'] = $row['end'];
    $row_array['allDay'] = $row['allDay'];
    $row_array['description'] = $row['description'];

    array_push($return_arr,$row_array);
}
echo json_encode($return_arr);

This works fine except it doesn't display the date correctly. When I find examples of ones that have a date display correctly it looks like this:

{
    title: 'Birthday Party',
    start: new Date(y, m, d, 12, 0),
    end: new Date(y, m, d, 14, 0),
    allDay: false
}

How can I use PHP to format my date like this so it looks the same. My data is stored as a DATETIME in the database. Any ideas would be greatly appreciated!

Thanks!

All, I'm creating some JSON arrays by looping through my database and populating some values. The code to do this is:

$return_arr = array();
$fetch = mysql_query("SELECT * FROM calendar_events"); 
while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
    $row_array['id'] = $row['id'];
    $row_array['title'] = $row['title'];
    $row_array['start'] = $row['start'];
    $row_array['end'] = $row['end'];
    $row_array['allDay'] = $row['allDay'];
    $row_array['description'] = $row['description'];

    array_push($return_arr,$row_array);
}
echo json_encode($return_arr);

This works fine except it doesn't display the date correctly. When I find examples of ones that have a date display correctly it looks like this:

{
    title: 'Birthday Party',
    start: new Date(y, m, d, 12, 0),
    end: new Date(y, m, d, 14, 0),
    allDay: false
}

How can I use PHP to format my date like this so it looks the same. My data is stored as a DATETIME in the database. Any ideas would be greatly appreciated!

Thanks!

• php
• javascript
• jquery
• json
• date

• new Date(y, m, d, 12, 0) is certainly not JSON. It's Javascript code. – deceze ♦ Commented Feb 1, 2012 at 4:05
• you should pass through this and run something like row.start = new Date(row.start); – vp_arth Commented Dec 15, 2014 at 12:44

3 Answers 3

try this:

 $row_array['start'] = date('Y-m-d', strtotime($row['start']));
 $row_array['end'] = date('Y-m-d', strtotime($row['end']));

The dates being retrieved from MySQL (in the $row array) will be of the form 'YYYY-MM-DD HH:MM:SS' as they are stored with the MySQL DATETIME type.

"The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format." - The DATE, DATETIME, and TIMESTAMP Types - MySQL 5.5 Reference Manual

This format is one of the Supported Date and Time Formats of the PHP DateTime class. This means you can easily construct a PHP DateTime object from your MySQL DATETIME value.

e.g. for your 'start' date

$start = new DateTime($row['start']);

You can then use the DateTime::format method on your newly created DateTime object to print it out as a string in any format you like.

e.g. if you wanted to put the date into your $row_array in the form DD-MM-YYYY

$row_array['start'] = $start->format('d-m-Y');

Note also that it's remended to use the MySQL Improved Extension - mysqli for connecting to a MySQL database.

"If you are using MySQL versions 4.1.3 or later it is strongly remended that you use the mysqli extension instead." - php - mysqli overview

Use php's date() function to create the date string.

$dateStr = date("Y-m-d", $time);

If you need to pass the js date object then this isn't the solution for you.