Mysql event to block user with x denunciations

Asked

Viewed 118 times

2

I have a table of complaints 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 do a daily event in mysql that checks whether the number of times the user was reported is greater than an X number of denunciations in order to block the same

I will do a check once a day, just since there are many people who like to "flood" in the denunciations

I 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

I’m really not good at asking the questions, I wouldn’t 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 someone has a suggestion based on the answer and question can edit to help other community members

mysql sql

  • As I answered, I realized the question is not very clear. Could you edit it to clarify if you want what I answered with the first two queries, what I answered with the third, or if it’s something else?

  • You could create a Rigger to check if the user had number >= x its status could change. [=

  • 1

    Another factor so I was in doubt, is by day ? IE, has a date involved ?

  • depends @Fullvio is executed 1 time a day, but it is not evaluated if the user was reported x times that day but in a total of complaints since he registered

  • I got it @Rodrigoborth ... vlw

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 complaints):

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

  • perfect, already acoplei within an update and came out running round :D... I didn’t know the HAVING yet

  • the first and third query helped me to solve the problem, the first because with it I understood the logic the third pq was the application of logica ready

  • @Rodrigoborth O HAVING is a kind of WHERE on aggregated columns (in this case the COUNT(*), which is the total of complaints reported).

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.