How to add inner join or left join in PHP mysql

Question

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

PHP

<?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 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);
   $stmt->execute();
	  	}
   $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

SQL

(SELECT count(*) unread from users INNER JOIN pm ON pm.from_id=users.userid where to_id=? and read=0)as unread

Posted 17-Jan-20 7:28am

previned

Updated 17-Jan-20 13:25pm

Patrice T

v4

Add a Solution

1 solution

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)

Wendelius · Answer 1 · 2020-01-17T08:43:00

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

SQL

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 pm2.read=0)as unread
FROM pm 
WHERE pm.from_id = ? 
or    pm.to_id = ?   
ORDER BY pm.id DESC

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

SQL

SELECT count(*) FROM pm where pm.to_id=? and pm2.read=0

Also note that my_username is again repeating data so fetching that only once should be sufficient.