PHP (oop - pdo) get last unread reply

[Fabel]

Hello. 

My first topic here was about getting the amount of unread messages. I'm now working on showing the unread messages in the inbox. 

Let's say we have 2 users. User1 sends user2 a message with the title: this is a title. User2 read the message and replied. User1 now has to see: RE: this is a title. 

This is the code in: class Pm {}

    public function get_unread_pm()
    {
        $stmt = $this->db->prepare('
        SELECT pm.title, pm.sender_id, pm.timestamp, (SELECT COUNT(pm2.id) FROM pm as pm1, pm as pm2 WHERE pm1.parent_id=pm2.id) AS replies, u.username as sender
        FROM pm    
        LEFT JOIN users AS u ON u.id=pm.sender_id 
        WHERE pm.receiver_id=:user_id AND pm.unread=1 AND pm.parent_id=pm.id');    
        
        $stmt->bindParam('user_id', $_SESSION['userid']); 
        $stmt->execute();
        return $stmt->fetchAll();
    }

I now get the original message because of the pm.parent=pm.id, but I want to get the message where the parent_id of a reply is the same parent_id as where the parent_id is equal to it's id.

I think a less complicated description is: How do I get the last reply instead of the original message? 

 

parent_id: When I reply  to a message, my id still counts up, the parent_id is the id of the original message. This is what my data-table looks like: 

The content:

 

If I'm doing this stuff really inefficient, I would be happy to know how to make it better

Fabian 

Edited September 19, 2019 by Fabel

[Fabel]

 

Damn I think I figured it out:

	FROM pm as pm1, pm as pm2
	LEFT JOIN users AS u ON u.id=pm2.sender_id 
	WHERE pm1.receiver_id=:user_id AND pm1.unread=1 AND pm1.parent_id=pm2.parent_id AND pm2.parent_id=pm2.id');	

 

[Fabel]

Nope I didn't figure it out completely, I still have a problem: 

with: 

		LEFT JOIN users AS u ON u.id=pm1.sender_id

I get this error: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pm1.sender_id' in 'on clause'' in C:\xampp\htdocs\core\class\pm.php:32 Stack trace: #0 C:\xampp\htdocs\core\class\pm.php(32): PDOStatement->execute() #1 C:\xampp\htdocs\list_pm.php(83): Pm->unread_pm() #2 {main} thrown in C:\xampp\htdocs\core\class\pm.php on line 32

doing it this way won't result in an error but will always give the username of the user who send the original message, not the username of the user who last replied at the original message

		LEFT JOIN users AS u ON u.id=pm2.sender_id

Edited September 19, 2019 by Fabel

[Barand]

Thanks for letting us know - I was just about to look at this one.

[Fabel]

	$stmt = $this->db->prepare('
		SELECT pm1.title, pm1.timestamp, u.username as sender,
		(SELECT COUNT(pm2.id) FROM pm as pm1, pm as pm2 WHERE pm1.parent_id=pm2.id) AS replies
		FROM pm as pm1, pm as pm2, users as u
		WHERE pm1.receiver_id=:user_id AND pm1.unread=1 AND pm1.sender_id=u.id AND pm1.parent_id=pm2.parent_id AND pm2.parent_id=pm2.id');	
		

This seems to work for now. I hope it actually will keep working 😂

Thanks anyway

[Psycho]

4 hours ago, Fabel said:

If I'm doing this stuff really inefficient, I would be happy to know how to make it better  

Not knowing how you are putting it all together, that's a very generic request. I would start by saying you could format your SQL queries for better readability. It helps when looking at a query to easily "see" what's going on. As to your problem, I don't think that last query is correct. The sub-query for the replies has no filter to limit to only the messages respective the the outer conversation. It is difficult from your posts to exactly understand what you are trying to do. It sounds as if you are wanting to get a single record for "conversation" (i.e. the parent_id is the same) that a user has received messages where any of those messages are unread. Further, I think you want the timestamp of the last unread message (to the user) in that conversation. But, what do you want for the title since the title can change? Do you want the title of the parent or the title of the last unread message to the user? note that I assume that a user can read later messages w/o reading all the earlier ones. So, the last unread message is not, necessarily, the last message in that conversation. Also, I'm not sure if the count should be all the messages to the user in that conversation or only the count of unread messages.

I think there are problems in the last query you submitted, but I can't say w/o understanding what you are trying to achieve. Perhaps you can supply some sample data and an expected output.

Edited September 19, 2019 by Psycho

[Fabel]

Ugh it's a bad query. I have to accept that I can't figure it out without help. A lot of things in my query are, as you said, going wrong. 

When user1 sends one email and replies to it 2 times, my query will just grab them all. The count doesn't work too.  

  

----

2 hours ago, Psycho said:

The sub-query for the replies has no filter to limit to only the messages respective the the outer conversation

I substract one (the outer conversation) inline to get the right number:

<td><?php echo $row1['replies']-1; ?></td>

----

2 hours ago, Psycho said:

But, what do you want for the title since the title can change? Do you want the title of the parent or the title of the last unread message to the user? note that I assume that a user can read later messages w/o reading all the earlier ones. So, the last unread message is not, necessarily, the last message in that conversation. Also, I'm not sure if the count should be all the messages to the user in that conversation or only the count of unread messages.

I don't want users to be able to change the title when they reply to an existing email. I want the title of the last unread message. I don't want to make it even more complex for myself so when they open a conversation/email, I want to set all the incoming unread replies in that email as read. I want to count all the replies in one conversation, not only the unread. 

I want to make a small email inbox. 

 

Thank you for taking the time to try to understand it. I hope I've explained it better now.

Fabian 

Edited September 19, 2019 by Fabel

[Barand]

I'm curious about what constitutes a reply.

In your data above they are all sent by 2 to 1 (all in the same direction) so technically there are no replies.

[Psycho]

Sorry, but I am still not clear. If the messages are all supposed to be "consumed" as a conversation (i.e. user open the conversation not individual messages), then this is the wrong DB structure. Also, your UI example shows each message as a separate record. If it is to be consumed as a conversation, there should only be one record in the UI (with the original title) and when opened, will show the entire conversation. That UI looks like there are three messages with 2 replies each - very confusing. You state the messages will all have the same title - but they don't. The replies all have "RE:".

Based on my understanding, you should have one table for each conversation with at least Conversation ID & Title. Then in the PM table, remove the title field and the parent_id will reference the conversation ID.

 

[Fabel]

2 hours ago, Psycho said:

That UI looks like there are three messages with 2 replies each - very confusing. You state the messages will all have the same title - but they don't. The replies all have "RE:".

Because it's going fairly wrong... And yes I can just do it without the 'RE:' but I wanted to make clear that those titles where 'replies', so that I could show you that my code doesn't work.

You suggest I need 2 tables? That's a good idea. Thanks for the insight! 

 

3 hours ago, Barand said:

I'm curious about what constitutes a reply.

In your data above they are all sent by 2 to 1 (all in the same direction) so technically there are no replies.

With 'reply' I mean a reaction to an email. wether it's your own email or someone else's. I would have to make a new account to show actual replies and I didn't think it would be much of a difference, but if it helps you to understand the output I can do that. 

Edited September 19, 2019 by Fabel