How to sort the mysql result array in descending order on the bases of date column in php?

Question

I am not native english, and new on stackoverflow so please correct me if i do something worng. My problem is that i retrieve data from two different tables of mysql database names are.

1. Event
2. Message

code used for fetching data is below.

$msgres=$ticket->getMessages();
$arr1 = array();
while($msg_row_one = db_fetch_array($msgres)){
    $arr1[] = $msg_row_one;
}
$noteres=$ticket->getNotes();
$arr2 = array();
while ($msg_row_two = db_fetch_array($noteres)){
    $arr2[] = $msg_row_two;
}

After this process i am merging the both array.

$arr1[]

$arr2[]

like this.

$merge = array_merge($arr1,$arr2);

Then i get an array as below.

Array
(
    [0] => Array
        (
            [msg_id] => 7563
            [ticket_id] => 1768
            [messageId] => 
            [msg_type] => R
            [message] => this is a link system . this is a link system .
            [staff_id] => 1
            [staff_name] => System Administrator
            [headers] => 
            [source] => staff
            [ip_address] => 10.12.145.174
            [created] => 2016-09-27 01:49:27
            [attachments] => 0
        )

    [1] => Array
        (
            [msg_id] => 7562
            [ticket_id] => 1768
            [messageId] => 
            [msg_type] => R
            [message] => Last message is this.
            [staff_id] => 1
            [staff_name] => System Administrator
            [headers] => 
            [source] => staff
            [ip_address] => 10.12.145.174
            [created] => 2016-09-26 08:39:46
            [attachments] => 0
        )

    [2] => Array
        (
            [msg_id] => 7561
            [ticket_id] => 1768
            [messageId] => 
            [msg_type] => R
            [message] => Last message is this.
            [staff_id] => 1
            [staff_name] => System Administrator
            [headers] => 
            [source] => staff
            [ip_address] => 10.12.145.174
            [created] => 2016-09-26 08:37:25
            [attachments] => 0
        )

    [3] => Array
        (
            [msg_id] => 7558
            [ticket_id] => 1768
            [messageId] => 
            [msg_type] => R
            [message] => mmmmmmmmmmmmmmmmmmmmmmmmmmm
            [staff_id] => 1
            [staff_name] => System Administrator
            [headers] => 
            [source] => staff
            [ip_address] => 10.12.145.174
            [created] => 2016-09-26 07:47:51
            [attachments] => 0
        )
  )

Then i sorting the dates of $merge array in descending order by follwing code.

$strdate  = array();
$formated = array();
foreach ($merge as $ascdate){
    $strdate[] = strtotime($ascdate['created']);
}
rsort($strdate);
foreach($strdate as $descdate){
    $formated[] = date('Y-m-d H:i:s',$descdate);
}

Now i want to sort the whole $merge array in descending order on behalf of dates. Means the biggest date $merge array should come on top. I have no idea how to apply above sorted dates $formated in $merge array.

Answer 1

Use usort to sort the whole array not just extracted dates

usort($merge,function ($a,$b) {
      return $a['created'] < $b['created'];
});

Answer 2

Do like this ,It is that you are looking for after merging both array , It use array multisort -

function array_orderby()
{
    $args = func_get_args();
    $data = array_shift($args);
    foreach ($args as $n => $field) {
        if (is_string($field)) {
            $tmp = array();
            foreach ($data as $key => $row)
                $tmp[$key] = $row[$field];
                $args[$n] = $tmp;
            }
    }
    $args[] = &$data;
    call_user_func_array('array_multisort', $args);
    return array_pop($args);
}

$arr = array(
        array('msg_id'=>123,'ticket_id'=>'32424','date'=>1233455,'created' => '2016-09-26 07:47:51'),
        array('msg_id'=>143,'ticket_id'=>'sssds','date'=>44724,'created' => '2016-09-27 07:47:51'),
        array('msg_id'=>13,'ticket_id'=>'3xxxx','date'=>434442424,'created' => '2016-10-26 07:47:51'),
        array('msg_id'=>723,'ticket_id'=>'eeeeeee','date'=>9908,'created' => '2016-06-06 17:47:51'),
        array('msg_id'=>793,'ticket_id'=>'fffff','date'=>9908,'created' => '2016-06-06 17:47:56')
    );
echo "Before Sorting";
print_r($arr);
$arr = array_orderby($arr, 'created', SORT_DESC) ;
echo "After sorting";
print_r($arr);

Just run this example and you will get what you are looking for

Answer 3

You can use the multi sort API for this

Ref: Example #3 Sorting database results

http://php.net/manual/en/function.array-multisort.php

<?php
$aMultiSort = array();
$aTimeStamp  = array();
foreach ($merge as $key=>$ascdate){
    //create strtotime for numerical sorting
    $ascdate['timestamp'] = strtotime($ascdate['created']);
    $aMultiSort[$key] = $ascdate;
    $aTimeStamp[$key] = $ascdate['timestamp'];
}

//multi sort with timestamp desc
array_multisort($aTimeStamp, SORT_DESC, $aMultiSort);

Answer 4

In this case I'd probably make a new method, which I'd call getAllComments() (or something similar). In this method I'd probably use a JOIN or a UNION query, to select all info from both tables/rows(?) at the same time. In this query it would be very simple to order by date, especially if it's a JOIN query.

In case of a UNION query you can wrap it in an outer SELECT, in order to sort the data. Something like this:

SELECT * FROM (
    SELECT id, text, date FROM messages
    UNION SELECT id, text, date FROM notes
) ORDER BY date

No need to involve (relatively) costly PHP sorting, nor all the extra work involved in writing and testing the code. The SQL engines are made especially for these kind of operations, after all. :)