Sort JSON data by datetime from MySQL data in PHP

Question

I've got a MySQL database populated with data received from the YouTube API v3. I've managed to output this data into a JSON file with the below code, however I want to have that data outputted into descending order of date and time. The json string is named "publishedAt" and the format is "2015-03-26 15:59:35". I've tried various other similar answers and cannot seem to get it to work with the usort function, so was wondering if someone could help. I'm very new to PHP so try and be as specific as possible, please.

Thanks.

<?php
//open connection to mysql db
$connection = mysqli_connect("host","username","password","Dbname") or die("Error " . mysqli_error($connection));

//fetch table rows from mysql db
$sql = "select * from database";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

// perform the query and store the result
$result = $connection->query($sql);

//create an array
$songarray = array();
while($row =mysqli_fetch_object($result))
{
    $songarray[] = $row;
}

echo json_encode($songarray);
//close the db connection
mysqli_close($connection);

$fp = fopen('jsondata.json', 'w');
fwrite($fp, json_encode($songarray));
fclose($fp);
?>

Answer 1

I would suggest you modify your sql query to sort the returned data by datetime.

Depending on your table structure you would need something like:

$sql = "select * from database order by date_column_name";

Answer 2

Try modifying your SQL to

$sql = "select * from database sort by publishedAt desc";