Result and Sort by Date

Result and Sort by Date
This is my ControlleR:

Code:

$data['reg'] = $this->register->registerAcc($schoolID);
$data['upd'] =$this->register->UpdateAcc($schoolID);
$data['sus'] =$this->register->suspendAcc($schoolID);
$this->load->view('account/accountInfo',$data);

This is my View Code: 

Code:

<thead style="width:300px;">
<th>Date</th>
<th>Function</th>
<th>UserName</th>
</thead>
<?php
foreach ($reg as $row) { ?>
<tr>
<td ><?php echo $row->registerDate;?></td>
<td><?php echo $row->registerStatus></td>
<td><?php echo $row->registerUserName></td>
</tr>
<?php
foreach ($upd as $row) { ?>
<tr>
<td ><?php echo $row->UpdateDate;?></td>
<td><?php echo $row->UpdateStatus></td>
<td><?php echo $row->UpdateUserName></td>
</tr>
<
?php
foreach ($sus as $row) { ?>
<tr>
<td ><?php echo $row->SuspendDate;?></td>
<td><?php echo $row->SuspendStatus></td>
<td><?php echo $row->SuspendUserName></td>
</tr>

But i have a problem , when i show in the page the list will go from

Code:

Date           Function       UserName
05-09-2019  Register    Tom
04-09-2019  Register    Bors
01-09-2019  Register    Berry
03-09-2019  Update      Laxy   
05-09-2019  Suspend     Loi

What i wanted is

Code:

Date           Function       UserName
05-09-2019  Register    Tom
05-09-2019  Suspend     Loi
04-09-2019  Register    Bors
03-09-2019  Update      Laxy   
01-09-2019  Register    Berry

below is my model 

Code:

public function registerAcc($schoolID){
$this->db->select('*');
$this->db->from('registerdata');
$this->db->where('school_ID',$schoolID);
$this->db->order_by('registerDateTime','DESC');
$query = $this->db->get();
return $result = $query->result();
}

public function UpdateAcc($schoolID){
$this->db->select('*');
$this->db->from('UserUpdateLog');
$this->db->where('school_ID',$schoolID);
$this->db->order_by('updateLog','DESC');
$query = $this->db->get();
return $result = $query->result();
}

public function suspendAcc($schoolID){
$this->db->select('*');
$this->db->from('SuspendedData');
$this->db->where('school_ID',$schoolID);
$this->db->order_by('suspendLog','DESC');
$query = $this->db->get();
return $result = $query->result();
}

Any idea ?

Hard to offer help without knowing what the data return from the models looks like. It might also be useful to show the model code.

Hard to offer help without knowing what the data return from the models looks like. It might also be useful to show the model code.

i have added my model to the first post. 

@incognitorecon,

What type of field is the date field? varchar? datetime?

However, I did find this solution... https://stackoverflow.com/questions/1693...ate-format

Any idea ?

You are making 3 separate request, that's why the order is not what you expect. I can think of 2 ways to fix this: 

1. Combine your 3 select query in a single one with UNION and add an order by for the results returned by all 3 selects.
Example: https://stackoverflow.com/questions/4374...nion-mysql

2. Combine the results of your 3 select in a single array, and sort this new array, maybe with usort, depending on the content: https://www.php.net/usort

2. Combine the results of your 3 select in a single array, and sort this new array, maybe with usort, depending on the content: https://www.php.net/usort

I thought this might work too but it seems to me that the combined array has to sort by keys. A sort by value is - pardon the pun - of no value. (usort sorts on values)

If we use dates for the keys we are limited to one date in the array.

Maybe a UNION will work.

UPDATE

It seems a UNION will work. I used the query below in phpMyAdmin and got the desired results. The various "date" columns all use the "date" datatype.

Code:

SELECT DATE_FORMAT(`registerDate`, '%m-%d-%Y') AS 'Date',`registerStatus` AS 'Function',`registerUserName` AS 'Name' FROM `registerdata` WHERE `schoolID`=5
UNION
SELECT DATE_FORMAT(`SuspendDate`, '%m-%d-%Y'),`SuspendStatus` ,`SuspendUserName` FROM `SuspendedData` WHERE `schoolID`=5
UNION
SELECT DATE_FORMAT(`UpdateDate`, '%m-%d-%Y'),`UpdateStatus` ,`UpdateUserName` FROM `UserUpdateLog` WHERE `schoolID`=5
ORDER BY `Date` DESC

So that means only one query and resulting dataset, so the view only needs one foreach loop.

2. Combine the results of your 3 select in a single array, and sort this new array, maybe with usort, depending on the content: https://www.php.net/usort

I thought this might work too but it seems to me that the combined array has to sort by keys. A sort by value is - pardon the pun - of no value. (usort sorts on values)

Why would you want to sort by key? You want to sort by date, so by value. You don't need to change the structure of the array. Each element is a database row. You reorder the rows based on the date value.

Why would you want to sort by key? You want to sort by date, so by value. You don't need to change the structure of the array. Each element is a database row. You reorder the rows based on the date value.

All the sort functions work on a single array. A database result set is an indexed array containing either array or object items. (Let's call those sub-arrays/objects $rows.) What is needed is a way to sort the $rows within the containing array.

As I said, my first inclination was to simply take the date from each $rows and use it as the key in the containing array. But the date values are not unique so that won't work.

I kind of remember a way to do this using array_column() and array_multisort(), but the UNION query is easier so I've stopped thinking about it.

Here is what I had in mind for the usort function.

Lets take 3 simple arrays and combined them with array_merge:

PHP Code:

$arr1 = array(
  array('date' => '2019-08-30', 'name' => 'Joe'),
  array('date' => '2019-07-05', 'name' => 'Tom')
);
$arr2 = array(
  array('date' => '2019-02-02',  'name' => 'Sandra'),
  array('date' => '2019-10-12',  'name' => 'Paul')
);
$arr3 = array(
  array('date' => '2019-12-01', 'name' => 'James'),
  array('date' => '2019-09-05', 'name' => 'John')
);

$arr4 = array_merge($arr1, $arr2, $arr3); 


This gives you this 4th array:

PHP Code:

Array
(
    [0] => Array
        (
            [date] => 2019-08-30
            [name] => Joe
        )

    [1] => Array
        (
            [date] => 2019-07-05
            [name] => Tom
        )

    [2] => Array
        (
            [date] => 2019-02-02
            [name] => Sandra
        )

    [3] => Array
        (
            [date] => 2019-10-12
            [name] => Paul
        )

    [4] => Array
        (
            [date] => 2019-12-01
            [name] => James
        )

    [5] => Array
        (
            [date] => 2019-09-05
            [name] => John
        )

) 


Now to sort by date, define a compare function and call usort:

PHP Code:

function cmp($a, $b)
{
  if ($a['date'] == $b['date']) {
    return 0;
  }
  return ($a['date'] < $b['date']) ? -1 : 1;
}

usort($arr4, "cmp"); 


This gives you this final array, sorted by date:

PHP Code:

Array
(
    [0] => Array
        (
            [date] => 2019-02-02
            [name] => Sandra
        )

    [1] => Array
        (
            [date] => 2019-07-05
            [name] => Tom
        )

    [2] => Array
        (
            [date] => 2019-08-30
            [name] => Joe
        )

    [3] => Array
        (
            [date] => 2019-09-05
            [name] => John
        )

    [4] => Array
        (
            [date] => 2019-10-12
            [name] => Paul
        )

    [5] => Array
        (
            [date] => 2019-12-01
            [name] => James
        )

)