Click here to Skip to main content
15,877,915 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i am creating unread messages count script in php mysqli. i have added inner join sql to my existing code it code display all users messages as a same unread message. i want to know how to join inner join or left join users table with pm table.

example like this
user1 unread 4
user2 unread 4
user3 unread 4

i want to display like this results
user1 unread 4
user2 unread 7
user3 unread 2

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

users table
userid    username
2         previn

3         raj

5         rajesh  

Here is my code

 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 count(*) unread from users INNER JOIN pm ON pm.from_id=users.userid where to_id=? and read=0)as unread

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

	if ($stmt = $con->prepare($sql)) {
   $stmt->bind_param('iiii',  $session_id, $session_id,$session_id,$session_id);
   $result = $stmt->get_result();
   if ($result->num_rows > 0) {
   while ($row = $result->fetch_assoc()) {
     echo $row['from_username'] . " -> " . $row['to_username'] . "<br>";
      echo 'User ' . $row['from_id'] . ' - ' . $row['unread'] . "<br>";


What I have tried:

I have tried here inner join
(SELECT count(*) unread from users INNER JOIN pm ON pm.from_id=users.userid where to_id=? and read=0)as unread
Updated 17-Jan-20 13:25pm

1 solution

If I understand the question correctly and the subquery mentioned is part of the bigger query, then the problem is that you're querying all records from table pm from specific user to another user. In other words, you would get the same unread count for all rows.

But if that is what you really want, then you could try something like
SELECT pm.*,
   (SELECT u.username FROM users u WHERE u.userid = pm.from_id) AS from_username,
   (SELECT u.username FROM users u WHERE u.userid = pm.to_id) AS to_username,
   (SELECT u.username FROM users u WHERE u.userid = ?) AS my_username,
   (SELECT count(*) FROM pm pm2 where pm2.to_id=pm.to_id and unread
FROM pm 
WHERE pm.from_id = ? 
or    pm.to_id = ?   

But as said, it looks like you're fetching completely different things in a single query so it probably would be best to split this to two different queries: On which fetches the pm data and another one which fetched the amount of unread messages. The latter would be simply
SELECT count(*) FROM pm where pm.to_id=? and

Also note that my_username is again repeating data so fetching that only once should be sufficient.
Share this answer
previned 17-Jan-20 14:52pm    
I checked your script but same results display all users unread messages same
Wendelius 17-Jan-20 14:59pm    
That's what I tried to say. The amount is repeated on each row.

If you want to fetch the amount of unread messages for a single user, I suggest you fetch it separately. This would be the second code block in the solution.
previned 17-Jan-20 15:42pm    
thank you very much

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