All right, first of all the disclaimer: I know using cursors is one of the worst practices for database programming, but I didn't find any other way of doing this.
I am creating a blog/chat like application so that our employees can communicate with the client, and have all company/client interactions saved and timestamped (there has been some response time comlaints).
Anyway, I am struggling to keep track of the unread messages, so I figured that I could make a simple math if I knew which messages have been read for each user, so whenever my app calls the messages table for the messages, it will update a separate table called "Read messages" where I will keep track of which messages have been read.
I think I have the logic, but whenever I try to run it, mysql just hangs thinking until it times out, however, if I check the 'messages read' table, the info was indeed updated.
Here's the code:
BEGIN
DROP TEMPORARY TABLE IF EXISTS fetchedMessages;
CREATE TEMPORARY TABLE fetchedMessages AS (
SELECT
blg.pkIdEntrada AS idMensaje,
blg.grlContenido AS Contenido,
blg.grlTimestamp AS FechaHora,
blg.grlReferencia AS Referencia,
usu.pkIdUsuario AS IdUsuario,
usu.gPrimerNombre AS PrimerNombre,
usu.gPrimerApellido AS PrimerApellido,
tml.pkIdRead AS idLectura
FROM blgEntries blg
LEFT JOIN tbl_usuarios usu ON blg.fkIdUsuario = usu.pkIdUsuario
LEFT JOIN tbl_mensajesLeidos tml ON blg.pkIdEntrada = tml.fkIdMensaje AND blg.fkIdUsuario = tml.fkIdUser
WHERE
blg.grlReferencia = numReferencia
ORDER BY
FechaHora DESC
);
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE c_idMess INT;
DECLARE curs CURSOR
FOR SELECT idMensaje FROM fetchedMessages WHERE idLectura IS NULL AND idMensaje IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN curs;
LOOP
FETCH curs INTO c_idMess;
INSERT INTO tbl_mensajesLeidos (fkIdMensaje, fkIdUser) VALUES (c_idMess, idEmp) ON DUPLICATE KEY UPDATE fkIdMensaje = c_IdMess, fkIdUser = idEmp;
##SELECT CONCAT(c_idMess, ' - ', idEmp);
END LOOP;
CLOSE curs;
END;
SELECT * FROM fetchedMessages;
END
Any ideas??
