Click here to Skip to main content
15,063,166 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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.
Posted
Updated 16-Jan-20 4:53am
Comments
Richard MacCutchan 16-Jan-20 4:17am
   
Where is line 123?

Maybe I don't understand MySQL that well (although I use it somewhat), but your used of count() is in error: MySQL COUNT() Function[^]
(SELECT from_id, COUNT(*) FROM pm WHERE to_id=? AND read='0' GROUP BY from_id)
It should more like:
SQL
SELECT from_id, count(unread) from . . . group by from_id, unread.


The missing comma after count() would be a problem.
Not having unread in the group-by causes an aggregate problem (error).
As would using count(*) instead of count(one of the aggregated values).

That's a start.
   
v2
Comments
previned 16-Jan-20 11:29am
   
i added you script but Fattel error message display
W Balboos, GHB 16-Jan-20 11:31am
   
My script, with the ". . ." was not a script. It was an example showing the beginning and end of the query. In other words, it was incomplete (that's what the . . . means).

If the error was something else then you should remember that if you don't post the error message no one can solve your problems.
previned 16-Jan-20 11:33am
   
please help me i have lot of tried
W Balboos, GHB 16-Jan-20 11:35am
   
In my original answer I gave you a link to a tutorial that makes it very simple with examples. Please go there and see how it is supposed to be done and then you can understand why yours doesn't work.
previned 16-Jan-20 11:40am
   
i cant understand brother plz help me i have tried on one month but still i couldn't do that
Hello from my undestanding, you want to check how many unread message a user has in total, not by each user he chats.

for one user
SELECT u.username ,COUNT(*) unread FROM pm join Users u
on pm.to_id=u.Id
 WHERE pm.to_id=? isReaded=0 GROUP BY u.Firstname



for all users
SELECT u.username ,COUNT(*) unread FROM pm join Users u
on pm.to_id=u.Id
 WHERE isReaded=0 GROUP BY u.username 
   
Comments
previned 16-Jan-20 4:22am
   
not working display Fatal error: Call to a member function get_result() on boolean in C:\xampp\htdocs\demo\npm\cons.php on line 423
tninis 16-Jan-20 4:27am
   
Check this
https://stackoverflow.com/questions/42177605/error-call-to-a-member-function-get-results
previned 16-Jan-20 4:38am
   
now problem one by one users unread message not counting display same row example user1 unread 2,user2 unread 2, userid3 unread 2,
tninis 16-Jan-20 4:47am
   
show me exactly the query you are using ,
i tested on a test database and was working
previned 16-Jan-20 4:39am
   
please help me i have lot of tried to create
previned 16-Jan-20 4:52am
   
one by one users messages not counting same row display
previned 16-Jan-20 4:53am
   
i added like this $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 COUNT(*) unread FROM pm join users u on pm.from_id=u.userid where to_id=? and pm.from_id=u.userid and read=0 group by from_id)AS unread

FROM pm WHERE from_id = ? or to_id=? ORDER BY id DESC";
previned 16-Jan-20 4:55am
   
when i added here like this SELECT u.username , it not working fattal error message display
tninis 16-Jan-20 5:24am
   
from my understanding and based on pm table schema, in order to retrieve the unread message of a user you should not do the above query you posted.
The user from "to_id" is receiving message and he has unread message not the user from "from_id" .
You said you wanted a result like :

user1 unread message 2
user2 unread message 7
user3 unread message 4

so take the query "for all users" from solution i submitted and test it, is will show the usernames and the total of the unread messages for each user.
previned 16-Jan-20 5:30am
   
i checked but same results Fatal error: Call to a member function get_result() on boolean in C:\xampp\htdocs\demo\npm\cons.php on line 417
previned 16-Jan-20 5:34am
   
i checked like this $sql = "

SELECT u.username ,COUNT(*) unread FROM pm join users u
on pm.to_id=u.userid
WHERE read=0 GROUP BY u.username )AS unread

FROM pm WHERE from_id = ? or to_id=? ORDER BY id DESC";
previned 16-Jan-20 5:35am
   
but same results error
tninis 16-Jan-20 5:41am
   
try this
SELECT u.username,COUNT(*) unread FROM pm join users u
on pm.to_id=u.userid
WHERE from_id = ? or to_id=? and isReaded=0 GROUP BY u.username
previned 16-Jan-20 5:48am
   
not working brother display like this Notice: Undefined index: from_id in C:\xampp\htdocs\demo\npm\cons.php on line 423
User - 2
tninis 16-Jan-20 5:49am
   
ok show me the cons.php on that line
previned 16-Jan-20 5:48am
   
not counting
previned 16-Jan-20 5:56am
   
can you join this as two sql statetement to one sql1= and sql2= like this
previned 16-Jan-20 6:05am
   
can you add like this
$sql1 = "SELECT from_id, COUNT(*) unread FROM pm WHERE to_id=? AND read='0' GROUP BY from_id";

$sql2 = "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


FROM pm WHERE from_id = ? or to_id=? ORDER BY id DESC";

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900