Nsryjdtyk

id sender_id receiver_id msg  date_added

    1    2          5       hii     2018-10-24 16:42:41

    2    5          2       hello     2018-10-25 16:42:41

i want to extract data as:

[2018-10-24]=>[{

                sender_id=>2,

                receiver_id=>5,

                msg=>hii}],

[2018-10-25]=>[{

               sender_id=>5,

                receiver_id=>2,

                msg=>hello

              }]

i have done this by first querying for the date first and applying group by to dates. then looping through dates and finding the data for those dates. but i think use of loops for that much of dates and that much of user will make the app slow in the coming future

The code i have used is:

 //the dates is used to get the dates from table using group by

$dates = $this->get_date_list($checkArr);

////then i loop through each date to get the corresponding data 

foreach($dates as $key=>$value){

$msg = $this->get_msg_by_date($value,$checkArr);

$data[$value] = $msg;

}



//here is the code to get the date from the date column

public function get_date_list($checkArr){

$sql = "Select date(date_added) as new_date from tb_chats where (sender_id = $checkArr[sender_id] AND receiver_id = $checkArr[receiver_id]) OR (sender_id = $checkArr[receiver_id] AND receiver_id = $checkArr[sender_id]) GROUP BY new_date";

$query = $this->db->query($sql);

$data  = $query->result_array();

return array_column($data,'new_date');

}



//here is the code to get the data for the looped dates

public function get_msg_by_date($date,$checkArr){

$sql = "Select * from tb_chats where (sender_id = $checkArr[sender_id] AND receiver_id = $checkArr[receiver_id] AND date_added LIKE '$date%') OR (sender_id = $checkArr[receiver_id] AND receiver_id = $checkArr[sender_id] AND date_added LIKE '$date%')";

$query = $this->db->query($sql);

$data  = $query->result_array();

return $data;

}