Help with UPDATE in logic using queries

Question

Help with UPDATE in logic using queries

Navigation

#1 by (0 votes)

0

This is the following, I have two tables, the esc_usuarios table that contains information for registered users and esc_usuarios_slog , which contains the records of the lows, in which they are performed through a button that changes the usu_situacao column % to ativo .

What I want is to create a function where, if there is no drop 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 for now 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 that makes UPDATE to inativo :

UPDATE esc_usuarios SET usu_situacao = 'inativo'";

These two queries are handled inside if and else if. So I need to give this update to users who do not have a drop in the esc_usuarios_slog table.

php mysql query select

asked by anonymous 09.11.2018 / 20:09

1 answer

"libhostpolicy.so" error while rotating Docker image for .net core 2.0 project console application Stirring with date and time in PHP

score 0 · Answer 1

You can assign the query result that looks for a drop in the last 30 days to a variable. So, you can use the mysqli_num_rows function, making a condition, which if it has more than 0 (zero) records, you loop it down and execute the query updates the status for each user, using the user code as a condition.

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've put this example to understand how it works, then just adapt to how you're doing.