Short SQL query

Question

Short SQL query

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

1

In an application, if the user inserts a value in a input , the variable named $nro_entrada_registro will take that value and in addition a current date will automatically be inserted in a field called fecha_registro . But when updating the value entered in input by another, the field fecha_registro is also updated and I do not want that. I want the date to be the first time the data was entered in input and can not be modified. If the value is updated, the date does not change. This query works well for me:

if ($nro_entrada_registro == '') {

    $sql4 = "UPDATE tramites SET estado_tramite='$estado', 
             nro_entrada_registro='$nro_entrada_registro', 
             fecha_registro=now() WHERE id_tramite='$id_tramite'";

    $res = mysqli_query($con, $sql4);

} else {

    $sql4 = "UPDATE tramites SET estado_tramite='$estado', 
             nro_entrada_registro='$nro_entrada_registro' WHERE 
             id_tramite='$id_tramite'";

    $res = mysqli_query($con, $sql4);

}

I would like to know if there is a way to abbreviate this code so that only one question remains? Perform a kind of if like the one I did, but within a query. I do not know if it's possible.

mysql php

asked by fed R 11.04.2017 в 22:46

source

3 answers

Python - Convert float to string Collect url from the post in wordpress

score 0 · Answer 1

I think it would be something like this:

IF EXISTS(SELECT * FROM tramites WHERE id_tramite='$id_tramite' AND estado_tramite='$estado' AND nro_entrada_registro='') 
THEN
UPDATE tramites SET estado_tramite='$estado', nro_entrada_registro='$nro_entrada_registro', fecha_registro=now() WHERE id_tramite='$id_tramite' 
ELSE 
UPDATE tramites SET estado_tramite='$estado', nro_entrada_registro='$nro_entrada_registro' WHERE id_tramite='$id_tramite'";

score 0 · Answer 2

Mysql has the function case

link

you could do something like

SELECT CASE WHEN $nro_entrada_registro='' THEN query1 ELSE query2 END;

but I think it would be better if you used a trigger to set the date

CREATE TRIGGER put_date AFTER UPDATE ON tramites 
       FOR EACH ROW 
       BEGIN
         IF NEW.nro_entrada_registro = '' THEN  
            UPDATE tramites SET fecha_registro=now()
         END IF;
      END;

That way you would just have to use the second query and the trigger would add the date when necessary.

score 0 · Answer 3

Well, you can simply rearrange the code in PHP:

$sql4 = "UPDATE tramites SET estado_tramite='$estado', nro_entrada_registro='$nro_entrada_registro'";
if($nro_entrada_registro == '') {
    $sql4 = $sql4 . ", fecha_registro=now() WHERE id_tramite='$id_tramite'";
} else {
    $sql4 = $sql4 . " WHERE id_tramite='$id_tramite'";
}
$res = mysqli_query($con, $sql4);