Help with logic UPDATE using queries

Question

It’s the following guys, I have two tables, the table esc_usuarios which contains information from registered users and esc_usuarios_slog, that contains the records of the casualties, in which they are performed through a button that changes the column usu_situacao for ativo.

What I want is to create a function where, if there are no casualties in the last 30 days, the user is set to inativo.

esc_usuarios:

 ________________________________
|usu_codigo|usu_nome|usu_situacao|
|----------|--------|------------|
|    32    |  Diogo |   ativo    |
|________________________________|

esc_usuarios_slog: usu_slog_codigo is the user id

 ________________________________________________________
|usu_slog|usu_slog_codigo|usu_slog_data|usu_slog_situacao|
|--------|---------------|-------------|-----------------|
|    4   |      32       |  2018-10-08 |      ativo      |
|________________________________________________________|

What I have so far is the query that looks for casualties in the last 30 days:

SELECT * FROM esc_usuarios_slog 
WHERE usu_slog_data 
between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
ORDER BY usu_slog_data DESC

And also the one who does the UPDATE for inativo:

UPDATE esc_usuarios SET usu_situacao = 'inativo'";

These two queries are dealt with within if and if Else. Hence I need to give this update to users who do not have a low table esc_usuarios_slog.

Answer 1

You can assign the result of query looking for casualties in the last 30 days to a variable. So, you can use the function mysqli_num_rows, making a condition, that if you have more than 0 (zero) records, you loop the low query that updates the status for each user, using as a condition the user’s code.

Example:

$baixas = mysqli_query($conexao, "SELECT * FROM esc_usuarios_slog 
WHERE usu_slog_data 
between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
ORDER BY usu_slog_data DESC");

// aqui verifico se foi retornado alguma baixa
if(mysqli_num_rows($baixas) > 0) {
   // aqui percorro a lista de baixas retornadas
   foreach ($baixas as $baixa) {
       // aqui executo a query passando a condição WHERE
       mysqli_query($conexao, "UPDATE esc_usuarios SET usu_situacao = 'inativo' WHERE usu_codigo = " . $baixa['usu_slog_codigo']);
       // perceba que estou acessando o atributo usu_slog_codigo como array, para exemplo, vai depender de como você está retornando no seu, caso seja objeto, o acesso deve ser assim: $baixa->usu_slog_codigo
   }
}

I set this example to understand how it works, then just adapt to how you’re doing it.