PHP Convert Date Then JSON Encode

Question

I've got the following MySQL query:

<?php
$sql = "SELECT * FROM my_table";
$result = mysql_db_query($DBname,$sql,$link) or die(mysql_error()); 

$rows = array();

while($r = mysql_fetch_assoc($result)) {
$rows[] = $r;
 }

 print json_encode($rows);

?>

Which outputs the following JSON format:

 [
  {
   "id":"100",
   "due_date":"2010-08-24 10:00:36"
  }
 ]

My question is: In my MySQL query, how would I be able to first convert/modify one row (in this case, the date),or rather, how would I modify my current query, using something like:

$date = strtotime($r['due_date']);
$new_date = date('j M', $date);

And then JSON encode the results, to return this instead:

[
 {
 "id":"100",
 "due_date":"24 Aug"
  }
]

Amber · Accepted Answer · 2010-08-01 17:37:42Z

7

You could use MySQL's DATE_FORMAT() to do it.

SELECT id, DATE_FORMAT(due_date_field, "%e %b") as due_date FROM my_table;

answered Aug 1, 2010 at 17:37

Amber

531k89 gold badges643 silver badges558 bronze badges

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

Collectives™ on Stack Overflow

PHP Convert Date Then JSON Encode

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related