0
Exists as after a query delete the returned result from a bank?
I have a message table (from,to,message) each time I refer I should delete the read message(s).
1 answer
0
You can create a Stored Procedure in your database. See an example:
DELIMITER $$
USE `database`$$
DROP PROCEDURE IF EXISTS `consultaMensagem`$$
CREATE PROCEDURE `consultaMensagem`(IN idMensagem INT)
BEGIN
SELECT * FROM mensagem WHERE id = idMensagem;
DELETE FROM mensagem WHERE id = idMensagem;
END$$
DELIMITER ;
Being:
database -> your database query -> name of your process idMensage -> input parameter for query and deletion
Amid BEGIN and END you develop your database, in case this performs a query in the database filtering all records that have the id = idMensagem (parameter passed to precedent). Then, with the same filter, run a DELETE, thus eliminating the message consulted.
To execute the procedure, do so:
Call consultaMensagem(1);
Thus, the message with id = 1 will be returned and deleted soon after.
-
In fact as I do not know if it has message or not, my select is like this
SELECT * FROM MENSAGEM WHERE para=para -
No problem @Thiago, just change the
WHEREofSELECTand ofDELETE -
created as your example, but it is deleting all messages instead of deleting only from the user passed by the parameter:
CREATE DEFINER='root'@'localhost' PROCEDURE 'consultaMensagem'(IN idpara INT)
BEGIN

 SELECT * FROM mensagens WHERE idpara = idpara; 
 DELETE FROM mensagens WHERE idpara = idpara;

END -
I did a test here @Thiago and I realized when the name of the field and the parameter it receives has the same name, it gets lost and ends up displaying and, consequently, removing everything. Tries to change the name of your input parameter
idparaforidReceptor, for example. -
It worked! Thank you!
Browser other questions tagged php mysql database
You are not signed in. Login or sign up in order to post.
I imagine you are looking for an automatic mode, because using the same SELECT criteria in a DELETE you will delete the newly made query...
– Gustavo
select can ensure that data has been extracted, not read, information could be lost, a solution could be (depends on the application) read the latest data and an EVENT erases records prior to a time X. But direct and in a single operation does not occur to me.
– Motta
Yes, only the problem is that I can have insertions during the query. And when I delete * there are new unread records
– Thiago
Maybe if I stored the id of the queried objects and delete afterwards.
– Thiago
Record the insertion date & time and use this data&time to know which new (select) and which old (delete).
– Motta
It would be easier with a field of
status(read or not) to the message. I imagine you have this field, but post the table structure to give a better overview of the issue.– Thomas