count messages two users in php mysqli

Question

I am creating message conversation script between two users in PHP MYSQLI.I want to know how to count unread message between two users.My script not showing anything.

my database pm table

id  from_id     to_id    msg               sent_date              read     
1   2           3        hi how are you?   2019-12-05 04:14:20    1                
2   3           2        fine              2019-12-05 05:15:58    0               
3   2           3        hi                2019-12-05 03:20:34    1                  
4   5           2        hi                2019-12-05 08:30:40    0

Here is my source code

<?php
$unread_messages = 0;       
if (isset($_SESSION['userid'])) {
    $session_id = $_SESSION['userid'];
}

$sql =  ("SELECT  COUNT(*) AS unread_messages
FROM    pm
WHERE   pm.to_id = ? and pm.from_id=from_id
        AND read = 0");
if ($stmt = $con->prepare($sql)) {
 $stmt->bind_param('i', $session_id);
 $stmt->execute();
}

$result = $stmt->get_result();
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
    echo $unread_messages;
  }
}
?>

GMB · Answer 1 · 2020-01-08T11:23:54.837

1

I am creating message conversation script between two users.

I would phrase this as:

select sum(*) cnt_unread
from pm
where 
    read = 0
    and (
        (pm.from_id = :userid1 and pm.to_id = :userid2)
        or (pm.from_id = :userid2 and pm.to_id = :userid1)
    )

This gives you the count of unread messages between :userid1 and :userid2 (whether from user 1 to user 2 or the other way around).

edited Jan 08 '20 at 11:23

answered Jan 08 '20 at 11:20

GMB

216,147
25
84
135

display like this message Fatal error: Call to a member function get_result() on boolean in C:\xampp\htdocs\demo\npm\cons.php on line 392 – Jan 08 '20 at 11:22
i dont know how to add it can you add to my code plz bro – Jan 08 '20 at 11:24
@previn: please check for errors after `prepare()` and `execute()`, and then share the relevant SQL error message. – GMB Jan 08 '20 at 11:25
i added like this but display zero only $sql = ("select sum(seen = 0) cnt_unread from pm where (pm.from_id = from_id and pm.to_id = to_id) or (pm.from_id = from_id and pm.to_id = ?)"); – Jan 08 '20 at 11:27
@previn: please run the query exactly as provided. My query expects two parameters `:userid1` and `:userid2`, yours has just one. – GMB Jan 08 '20 at 11:28
display like this message Fatal error: Call to a member function get_result() on boolean in C:\xampp\htdocs\demo\npm\cons.php on line 395 – Jan 08 '20 at 11:31
@previn: check for errors... see [this link](https://stackoverflow.com/questions/18050071/php-parse-syntax-errors-and-how-to-solve-them) for how to proceed. – GMB Jan 08 '20 at 11:37
Anyone can help me plz – Jan 08 '20 at 11:44

Ahmed Kilany · Answer 2 · 2020-01-08T14:44:49.520

0

SET @u1 = 2;  -- id of first user
SET @u2 = 3;  -- id of 2nd user

SELECT count(`read`) as unread FROM pm 
WHERE `read` = 0 AND (from_id in (@u1,@u2) OR to_id in (@u1,@u2));

edited Jan 08 '20 at 14:44

answered Jan 08 '20 at 13:35

Ahmed Kilany

76
6

i checked display zero value only – Jan 08 '20 at 13:39
first problem is using "read" as field name while it is a keyword in mysql. this problem can be solved by surrounding it with symbol ` as `read` – Ahmed Kilany Jan 08 '20 at 14:49
another advice is to work directly with database until you get expected results then move you SQL to PHP code. – Ahmed Kilany Jan 08 '20 at 14:59

count messages two users in php mysqli

2 Answers2