Get chat message data from table, grouped by date

Question

Giving the following table:

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'm getting it by first querying for the date and then grouping by dates. After, I loop through thoses dates to retrives date's data.

However, I think using loops with a lot of dates and users will slow the app quickly

The code I've 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;
}

mickmackusa · Accepted Answer · 2018-12-27 12:41:28Z

Lots of little pieces of advice:

Tab the lines of code inside of your loops and functions (etc) for improved readability.
Only declare the $key variable in your foreach() loop if you intend to use it.
Avoid performing iterated trips to the database. Furthermore, always endeavor to minimize total trips to the database unless you have a data volume issue to contend with.
Use meaningful variable names. $checkArr does not speak clearly of the data that it holds.
Quote your associative array keys.
You aren't filtering out any dates between the two selected users, so you can avoid calling date-fetching queries entirely.
After writing your WHERE condition to isolate the messages between the pair of users, sort the rows by descending datetime.
Use all uppercase for MySQL reserved words for clarity / readability.
Before return $data, you can run a simple loop to group the message data by date. Use the date value as the first level keys and just push the row data as subarrays.

Suggested single query: (well... really I recommend a prepared statement)

$query =
"SELECT DATE(date_added) AS `date`, sender_id, receiver_id, `msg
FROM tb_chats
WHERE
  (sender_id = " . (int)$checkArr['sender_id'] . " AND receiver_id = " . (int)$checkArr['receiver_id'] . ")
  OR
  (sender_id = " . (int)$checkArr['receiver_id'] . " AND receiver_id = " . (int)$checkArr['sender_id'] . ")
ORDER BY date_added DESC";

Grouping with php:

foreach ($data as $row) {
    $grouped[array_shift($row)][] = $row;
}
// this step can be performed in a number of different ways according to style preference / micro-optimization
return $grouped;

I don't know code igniter, so if the grouping can be done with an available function, I don't know it.

Good advice, but I think you should be a bit more emphatic in recommending a prepared statement. Splicing values like that is a really bad (dangerous) habit to get into! — Toby Speight
– Toby Speight, Commented Jan 10, 2019 at 18:13

Stack Exchange Network

Get chat message data from table, grouped by date

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Get chat message data from table, grouped by date

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions