How to count receiver and sender unread messages in PHP

Question

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 15-Jan-20 20:47pm

previned

Updated 16-Jan-20 4:53am

Add a Solution

Comments

Richard MacCutchan 16-Jan-20 4:17am

Where is line 123?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

W∴ Balboos, *GHB* · Answer 1 · 2020-01-16T04:54:00

Solution 2

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.

Posted 16-Jan-20 4:54am

W∴ Balboos, GHB

Updated 16-Jan-20 4:55am

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

tninis · Answer 2 · 2020-01-15T22:00:00

Solution 1

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

Posted 15-Jan-20 22:00pm

tninis

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";