I am creating receiver and sender unread messages count script in php mysqli. i have one problem i have added sql count script to my existing code it not working. display like this error message
Fatal error: Call to a member function get_result() on boolean in C:\xampp\htdocs\demo\npm\cons.php on line 123
I added code here
(SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read='0' GROUP BY from_id)
this code problem here
from_id, COUNT(*)
when i removed this working but display same unread messages all from users.
display same results like this
user1 unread message 2
user2 unread message 2
user3 unread message 2
I want to get like this results
user1 unread message 2
user2 unread message 7
user3 unread message 4
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
if (isset($_SESSION['userid'])) {
$session_id = $_SESSION['userid'];
}
$sql = "SELECT *,
(SELECT username FROM users WHERE userid=from_id) AS from_username,
(SELECT username FROM users WHERE userid=to_id) AS to_username,
(SELECT username FROM users WHERE userid=?) AS my_username,
(SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read='0' GROUP BY from_id)
FROM pm WHERE from_id = ? or to_id=? ORDER BY id DESC";
if ($stmt->prepare($sql)) {
$stmt->bind_param('iiii', $session_id, $session_id,$session_id,$session_id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo $row['from_username'] . " -> " . $row['to_username'] . "<br>";
echo 'User ' . $row['from_id'] . ' - ' . $row['unread'];
}
}
?>
What I have tried:
(SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read='0' GROUP BY from_id)
Please anyone can help me i have lot of tried to create this. thanks.