Perform a DELETE with Where being the result of a SELECT (MYSQL)

Asked

Viewed 334 times

3

Guys, have a code in which I use the result of a SELECT to be a condition in the WHERE of a DELETE.

I’ll leave the code part to see if you can spot any errors. UPDATE (which is at the top of the code) works, but the DELETE part at the end is not working. 

<?php

    date_default_timezone_set('America/Sao_Paulo');

    // CHAMANDO O ARQUIVO DE CONEXÃO AO BANCO
    require_once("../conexao/conexao-com-banco.php"); 

    //CONSULTA NO BANCO QUE MUDA O STATUS NO BANCO DE DADOS 


    session_start();

    $usuario = $_SESSION["nome"];

    //$estate = $_POST['estate'];

    $tipo_atividade = $_POST['tipoatividade'];
    $codi = $_POST['codigo'];
    $cod = $_POST['cod'];

    $data = date("Y-m-d H:i:s");


    $inserirStandBy = "UPDATE tbl_atividades set CONFERENCIA = 'STANDY BY', DETALHES = CONCAT(IFNULL(DETALHES, 'Atualizações:'), 'ATIVIDADE PARA POR FALTA DE ARQUIVOS'), STATUS = 'STAND_BY', ATUALIZADO = 'S', DT_INICIO = NULL, DT_FIM = NULL WHERE codigo = $codi";
    $resultado_update_stand = mysqli_query($conecta, $inserirStandBy);


    $select_data = "SELECT DT_VENCIMENTO FROM tbl_atividades WHERE codigo = $codi";
    $result_select_stand = mysqli_fetch_assoc(mysqli_query($conecta, $select_data));

    $dt_vencimento = $result_select_stand['DT_VENCIMENTO'];



    $deletar_atividades = "DELETE FROM tbl_atividades WHERE TIPO_ATIVIDADE = '$tipo_atividade' AND COD = '$cod' AND DT_VENCIMENTO > $dt_vencimento";
    $result_delete_stand = mysqli_query($conecta, $deletar_atividades);

?>

NOTE: The result of SELECT is a DATE field.

I’m only posting the part of the code in PHP, I think it is not necessary the part of AJAX.

Thanks for your attention.

php mysql

  • Have you tried to do a small test by manually printing the result of your variable that received SELECT and then trying to perform this delete manually to see if the problem is not in the value contained in the variable ?

2 answers

5


The result of function recall mysqli_query is not a scalar value, but an object of the type mysqli_result (PHP: mysqli::query - Manual).

What you need to do is get the date value and then use DELETE then, in this way:

$select_data = "SELECT DT_VENCIMENTO FROM tbl_atividades WHERE codigo = $codi";
$result_select_stand = mysqli_fetch_assoc(mysqli_query($conecta, $select_data));

$dt_vencimento = $result_select_stand['DT_VENCIMENTO'];

$deletar_atividades = "DELETE FROM tbl_atividades WHERE TIPO_ATIVIDADE = '$tipo_atividade' AND COD = '$cod' AND DT_VENCIMENTO > '$dt_vencimento'";
$result_delete_stand = mysqli_query($conecta, $deletar_atividades);

  • Hello, João. I made the changes here, but DELETE was not executed. The data coming from the form, via AJAX, is coming, because the first UPDATE of the code works normally. You wouldn’t happen to know if there might be another problem related to my code ?

  • 1

    If you make a echo $dt_vencimento what is returned to you? Try placing the date between quotes: DT_VENCIMENTO > '$dt_vencimento'.

  • João Martins, it was the same (quotation marks). I added this and his code worked. But the variable is a DATE field. Would you like to explain why I use variable quotes that store data to type DATE ? Change your answer there and I’ll accept it. Thanks!

  • Edited answer. Yes, despite being of the type DATE, it is necessary to put the requests to be understood as text, otherwise it will be just numbers with a separator, something that is not accepted by the compiler.

2

One solution is to include this first SELECT as a sub-select of the DELETE function. Ex.:

$deletar_atividades = "DELETE FROM tbl_atividades WHERE TIPO_ATIVIDADE = '$tipo_atividade' AND COD = '$cod' AND DT_VENCIMENTO > (SELECT DT_VENCIMENTO FROM tbl_atividades WHERE codigo = $codi LIMIT 0, 1)";
$result_delete_stand = mysqli_query($conecta, $deletar_atividades);

  • I tried your way too, Diego Marques, but it didn’t work either.

Browser other questions tagged php mysql

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