MySQL event to block user with x complaints

Navigation
2

I have a complaints table where I record the information of users who reported and who they reported 

   id | user   |   denunciado
    1 | userx  |   userY
    2 | userz  |   userY

And so on, now I would like to make a daily event in mysql that would check if the number of times the user was denounced is greater than an X number of complaints to be able to block it

I'll do a check once a day, just because there are a lot of people who like to "flood" complaints

I have decided as follows 

UPDATE usuarios SET cadastro=3 WHERE 
id IN (SELECT 
denunciado
FROM denuncias
GROUP BY denunciado
HAVING COUNT(*) > 30)

Inside an event that will run at 3 am each day

Actually, I'm not good at asking the questions, I would not have understood what I'm asking if I were going to answer that question, but it was the only way I could express myself, if anyone has a suggestion based on the answer and the question can edit to help other members of the community

    
asked by anonymous 14.04.2014 / 21:58

1 answer

4

You just need to count the results: 

SELECT COUNT(*) AS denuncias
FROM tabela
WHERE denunciado = 'userY'

Or, if you want the answer already in the query (example for more than 5 denunciations): 

SELECT CASE WHEN COUNT(*) > 5 THEN 1 ELSE 0 END AS bloquear
FROM tabela
WHERE denunciado = 'userY'

If you need this for multiple users simultaneously, use a grouping: 

SELECT 
    denunciado,
    COUNT(*) AS denuncias
FROM tabela
GROUP BY denunciado
HAVING COUNT(*) > 5
    
14.04.2014 / 22:02
div set when scrolling page down Internet Explorer mode Standards