Skip to content
Advertisement

Why does my data from SQL table delete after I run this code?

I have this code that I want to run and it’s connected to my database and it uses to show the values on the table but every time I do an UPDATE query.

It updates the rest of the row with zeros except the one I’ve chosen. I want to keep my numbers on the row but only update the one that i chose but I can’t figure it out how to!

I initialized the table with a row with Zeros (0s) and then with my web page application I want to change those values.

But if I have another value on the rest of the columns and I want to change this one, the rest of the values go to zero (the initial state of the table) (I don’t know how to explain it better I think)

<?php 
                      $showData = ("SELECT * FROM horario");
                      if ($new_user_activity['data'] == 'Seg' ) {
                          $segHora = $new_user_activity['horas'];
                      }
                          $sql = ("UPDATE horario SET seg=$segHora WHERE id=1");
                          if(!mysqli_query($conn,$sql)) {
                            echo("ERRO NA QUERY" );
                          }
                          else {
                            if(mysqli_query($conn,$sql)){
                              $sqlData = mysqli_query($conn,$showData);
                              while ($row = mysqli_fetch_array($sqlData,MYSQLI_ASSOC)){
                              echo($row['seg']);
                              }     

                        }

                        }

            ?>

Its expected to keep my values from the other columns on the same row!

PICTURE OF WHAT IS HAPPENING TO THE DATABASE

Advertisement

Answer

This code you’ve shown is not responsible for resetting the row. Something else must be doing it. There is another update somewhere, possibly a trigger on the database, but it’s not coming from the code sample you’ve posted.

Regarding your code, there are several things wrong, which others have already pointed out. I would like to add a couple more

  1. Ensure you SANITIZE your data before inserting it in MySQL. Check out PDO and bindparam, for starters. Short of that, use (int) to cast the variable to an integer.
  2. The update should only happen if there is new user activity.

Code:

 if ($new_user_activity['data'] == 'Seg') {
     $segHora = (int)$new_user_activity['horas'];
     $sql = ("UPDATE horario SET seg=$segHora WHERE id=1");

     if (!mysqli_query($conn, $sql)) {
         echo ("ERRO NA QUERY");
         return; // or exit, if this is not a function
     }
 }

 $showData = ("SELECT * FROM horario");
 $sqlData = mysqli_query($conn, $showData);

 while ($row = mysqli_fetch_array($sqlData, MYSQLI_ASSOC)) {
     echo ($row['seg']);
 }
3 People found this is helpful
Advertisement