I am trying to extract the number of unread messages that exist in each conversation but this is throwing me the wrong value, since the first user has only sent me 5 messages that I have not read and the others have only sent me each one single message. But for some unknown reason as a result it is getting me 6 unread messages in each conversation.
I have placed the query in the SQL of phpMyAdmin and I have discovered that it is getting the answer correctly, but nevertheless it is getting the number 6 in all the results of the conversations NOTE: the first conversation has 6 unread messages and I think that is where the problem is
Important note:
In the SQL code that I have placed below, I am getting the user's conversations correctly, the only problem is that I cannot get the unread messages that each user has to be displayed as a notification. Previously I was trying to get these unread messages using another SQL statement in a different file, but I was not successful because where I have to get this information is the same place I am getting the messages.
In the table messege
there is a field called view
that has data that varies depending on its status. If the data is equal to No
it is because the message has not been read, if the view = 'Si'
then it is because the message was read.
Message table:
CREATE TABLE messege(
id_messege int(255) AUTO_INCREMENT PRIMARY KEY,
id_emisor int(255) NOT NULL,
id_receptor int(255) NOT NULL,
messege text,
photo_messege varchar(200),
ip_messege varchar(200),
browser_messege varchar(200),
view varchar(50),
create_at_messege datetime,
CONSTRAINT fk_messege_emisor FOREIGN KEY(id_emisor) REFERENCES users(id_user),
CONSTRAINT fk_messege_receptor FOREIGN KEY(id_receptor) REFERENCES users(id_user)
)ENGINE=InnoDb;
My goal:
I want to show the number of unread messages I have in each conversation
SQL code that uses:
<?php
// Sacar los usuarios con los que he tenido una conversacion
$stetament = $conexion->prepare(
"SELECT `id_user`, id_emisor, name, surname, photo_profile,
create_at_messege, messege, view
FROM (
SELECT id_user,
MAX(id_messege) id_messege
FROM (
SELECT id_receptor id_user,
id_messege
FROM messege
WHERE id_emisor = $id
UNION
SELECT id_emisor id_user,
id_messege
FROM messege
WHERE id_receptor = $id
) c1 GROUP BY 1
) c2 JOIN messege USING(id_messege)
JOIN users USING(id_user)
ORDER BY id_messege DESC"
);
$stetament->execute(array());
$result = $stetament->fetchAll();
Your current strategy is a bit more complicated than it needs to be, all you need comes out with this query:
With this you can discard
count-messege.php
, since theSELECT
interior counts the messages at the same time that it obtains the id of the most recent, with whichSELECT
the content and date of the last message is obtained in the exterior.To complement the last message sent, we obtain in a scalar query the id of the last message sent; in the outer query we discern which message is more recent (the one sent or the one received) based on the value of the id, the highest being the most recent.
I'm not quite sure what you need.
This query returns the number of unread messages per sender for each of the receivers there are. If you need the information of a single receiver you should filter by the receiver.
Output (with sample data):
Solution
With a scheme like the following:
You can get all the messages that a user has not seen in the following way:
With a user with
:id = 4
whom you have not read messages, you would have the following responseSo for php you just need to interpolate that value:
More data ?
What if I wanted the information of the name, surname and photo of the person who sent me a message and not just the id?
Easy, you just add that data to the
select
:We have a response like the following (
:id = 4
)Bonus ?
You can play with a small dataset in SQLFiddle