Sorting an associate array by date values

Question

I'm trying to sort an associate array by the posted value which is a date "%a, %e %b %Y %k:%i(%s)"

Array
(
    [0] => Array
        (
            [starName] => Natalie Peri
            [posted] => Wed, 2 Mar 2016 15:05(09) PM Pacific/Auckland
        )

    [1] => Array
        (
            [starName] => Taylor Hay
            [posted] => Wed, 2 Mar 2016 15:34(37) PM Pacific/Auckland
        )

    [2] => Array
        (
            [starName] => Amber Dawn Fox
            [posted] => Tue, 5 Jan 2016 5:54(48) PM Pacific/Auckland
        )

    [3] => Array
        (
            [starName] => Young Artist Awards 2016
            [posted] => Mon, 14 Mar 2016 11:12(20) PM Pacific/Auckland
        )

)

So it would be Young, Natalie, Taylor, Amber. (array 1 = gets key 1 and 2 and array 2 gets key 3 and 4 btw)

I have tried this:

        include("../database_connection.php");

$sth = "SELECT starName, date as posted
FROM blog
WHERE blog.approved =1
ORDER BY STR_TO_DATE( posted, '%a, %e %b %Y %k:%i(%s)' ) DESC
LIMIT 2"; 
$r = $dbc->query($sth); 
$results = array();

while($row = $r->fetch_assoc()) {
    $results[] = $row;
}

$sth2 = "SELECT starName, posted FROM questions 
                                      INNER JOIN stars ON questions.starID = stars.starID
                                      INNER JOIN roles ON stars.starID = roles.starID
                                      LEFT JOIN users ON users.userID = questions.userID
                                      WHERE returned =1
                                      AND most =1
                                      ORDER BY STR_TO_DATE( posted, '%a, %e %b %Y %k:%i(%s)' ) DESC, questionID
                                      LIMIT 2";
$r2 = $dbc->query($sth2); 
$results2 = array();

while($row2 = $r2->fetch_assoc()) {
    $results2[] = $row2;
}


$result = array_merge($results, $results2);


function compareByDate($item1, $item2) { 
    return strtotime($item1['posted']) - strtotime($item2['posted']);
}  
usort($result, 'compareByDate');


echo "<pre>";
print_r($result);
echo "</pre>";

but Amber Dawn Fox always shows up first, does anyone know where i'm going wrong?

convert the string date into unix timestamp or DateTime object inside your custom function first — Kevin, Mar 15 '16 at 02:11
@Ghost thanks for the reply, how would i go about that? like this: `function compareByDate($item1, $item2) { $item1 = date_format($item1['posted'], 'Y-m-d H:i:s'); $item2 = date_format($item2['posted'], 'Y-m-d H:i:s'); return $item1 - $item2; }` — jenstar, Mar 15 '16 at 02:22
@Dagon I haven't merged them in phpmyadmin used `$result = array_merge($results, $results2);` — jenstar, Mar 15 '16 at 02:40
i still think sorting at source will work, but if you would rather not thats fine — , Mar 15 '16 at 02:42
@Dagon i couldn't figure out how to haha, kinda new at this stuff — jenstar, Mar 15 '16 at 02:43

Kevin · Answer 1 · 2016-03-15T03:05:00.623

2

Just like I've said in the comments, either convert it to unix timestamp first, or use DateTime objects first.

I'm assuming rsort is just a typo. You mean usort. And ($result, 'date_compare'); use your custom function name: compareByDate. It doesn't match:

If you happen to go the DateTime route, just use createFromFormat method and provide the time string format you have.

D, j M Y G:i(s)

Example:

function compareByDate($item1, $item2) { 
    $a = DateTime::createFromFormat('D, j M Y G:i(s)', $item1['posted']);
    $b = DateTime::createFromFormat('D, j M Y G:i(s)', $item2['posted']);
    return $b->getTimeStamp() - $a->getTimeStamp(); 
}  
usort($result, 'compareByDate');

This return $b->getTimeStamp() - $a->getTimeStamp();

(b - a) this returns in descending fashion

swap it (a - b) when you need it ascending.

Sample Code

Sidenote: If for whatever reason, you want to preserve key relation, just use uasort.

Edit: For whatever reason, you need that special time formatting to be presented for the user, then just leave that intact from your query. I guess that format looks like:

SELECT DATE_FORMAT(`timestamp_column`, '%a, %e %b %Y %k:%i(%s)') AS `posted`

Remember, just add the raw timestamp_column YYYY-MM-DD HH:MM:SS along the query of the columns you want to include.

SELECT DATE_FORMAT(`timestamp_column`, '%a, %e %b %Y %k:%i(%s)') AS `posted`, `timestamp_column`

Then just use strtotime:

function compareByDate($item1, $item2) { 
    return strtotime($item2['timestamp_column']) - $item1['timestamp_column'];
}  
usort($result, 'compareByDate');

edited Mar 15 '16 at 03:05

answered Mar 15 '16 at 02:30

Kevin

41,694
12
53
70

Nothing shows up on the page pass this code, aka the footer or the print_r(), does it matter if i'm using below PHP 5.3 ? – jenstar Mar 15 '16 at 02:39
@jenstar what do you mean nothing shows up? you mean in your environment? i've already setup the online demo so that you could check – Kevin Mar 15 '16 at 02:43
@jenstar yes it does matter, [`creaFromFormat`](http://php.net/manual/en/datetime.createfromformat.php) needs >= 5.3 – Kevin Mar 15 '16 at 02:44
is there an alternative I can do, to get the same results? – jenstar Mar 15 '16 at 02:46
@jenstar did you format this date string in the database layer? you should have left it alone with `DATETIME` or `TIMESTAMP` format, then just simply use `strtotime` with the column value, that should replace `createFromFormat` just fine – Kevin Mar 15 '16 at 02:52
updated the code sample above, one is varchar and one is timestamp – jenstar Mar 15 '16 at 02:54
@jenstar check out the edit, this route should also work – Kevin Mar 15 '16 at 03:05
Edited the code above again, as made both of the posted columns varchar's in phpmyadmin but still in the wrong order – jenstar Mar 15 '16 at 03:29

Sorting an associate array by date values

1 Answers1