Is there an error when I try to update information

Question

I have created a table in my database where a user can store daily information about themselves, the data saves successfully to the table but there is an error if I try to update data already in the table (this needs to happen if the user has already entered information on that day, instead of creating a new row).

  $sql = "SELECT * FROM $username WHERE day=?;";
  // Here we initialize a new statement by connecting to the database (dbh.php file)
  $stmt = mysqli_stmt_init($conn);
  if (!mysqli_stmt_prepare($stmt, $sql)) {
    // If there is an error the user is sent to the enter data page again
    header("Location: ../enterTodaysData.php?error=sqlerror");
    exit();
  }
  else { //if there are no errors...
    mysqli_stmt_bind_param($stmt, "s", $day); //binds the parameters to the statement
    mysqli_stmt_execute($stmt); //executes the statement

    $result = mysqli_stmt_get_result($stmt); //saves the result of the statement into the result variable

    if ($row = mysqli_fetch_assoc($result)) { //if the user HAS already made an entry that day
      $sql = "UPDATE $username (SET peakflow1 = $peakflow1 WHERE day=$day);";
      $sql = "UPDATE $username (SET peakflow2 = $peakflow2 WHERE day=$day);";
      $sql = "UPDATE $username (SET coughing = $coughing WHERE day=$day);";
      $sql = "UPDATE $username (SET tightChest = $tightChest WHERE day=$day);";
      $sql = "UPDATE $username (SET shortBreath = $shortBreath WHERE day=$day);";
      $sql = "UPDATE $username (SET wheezing = $wheezing WHERE day=$day);";
      $sql = "UPDATE $username (SET symptomOne = $symptomOne WHERE day=$day);";
      $sql = "UPDATE $username (SET symptomTwo = $symptomTwo WHERE day=$day);";
      $sql = "UPDATE $username (SET medication = $medication WHERE day=$day);";
      $sql = "UPDATE $username (SET mood = $mood WHERE day=$day);";
      $sql = "UPDATE $username (SET comments = $comments WHERE day=$day);";
      $sql = "UPDATE $username (SET overall = $overall WHERE day=$day);";
      header("Location: ../home.php?sql=success");
      exit();
    }
    else{ //if the user has not
      $sql = "INSERT INTO $username (day,   peakflow1,  peakflow2,  medication, mood,   coughing,   tightChest, shortBreath,    wheezing,   symptomOne, symptomTwo, overall,    comments) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"; //the question marks are placeholders
      $stmt = mysqli_stmt_init($conn);
      //an sql statement is prepared and the database is connected to
      if (!mysqli_stmt_prepare($stmt, $sql)) {
        // If there is an error the user is sent back to the signup page
        header("Location: ../enterTodaysdata.php?error=sqlerror");
        exit();
      }
      else {
        //binds the paramaters and data to the statement
        mysqli_stmt_bind_param($stmt, "siisiiiiiiiis", $day,    $peakflow1, $peakflow2, $medication,    $mood,  $coughing,  $tightChest,    $shortBreath,   $wheezing,  $symptomOne,    $symptomTwo,    $overall,   $comments);
        //this executes the prepared statement and send it to the database, this registers the user.
        mysqli_stmt_execute($stmt);
        //sends the user back to the signup page, with a message confirming that it was a success
        header("Location: ../home.php?sql=success");
        exit();
      }
    }
  }
}

The part of the code causing the problem starts at the line $sql = "UPDATE $username (SET peakflow1 = $peakflow1 WHERE day=$day);";. There are no results appearing on the screen, other than the "sqlerror" error message at the top of the screen, but the table does not update.

Answer 1

I see a few problems with the updates. (There may be other problems at runtime, but this is just what I see in the code you posted.)

1. In this part:

   if ($row = mysqli_fetch_assoc($result)) { //if the user HAS already made an entry that day
       $sql = "UPDATE $username (SET peakflow1 = $peakflow1 WHERE day=$day);";
       $sql = "UPDATE $username (SET peakflow2 = $peakflow2 WHERE day=$day);";
       ...
   

   each of those $sql = "UPDATE ... expressions is overwriting the $sql variable, so at the end of that section $sql will only hold the last query.

2. The parentheses around SET peakflow1 ... etc. are unnecessary at best, and I think they'll cause SQL syntax errors.

3. There are no quotes around any of the variables in those SQL strings, and some of them contain strings. This will cause SQL syntax errors. (See When to use single quotes, double quotes, and backticks in MySQL.) You should avoid this problem by executing the update the same way you are doing the insert, by binding the variables to placeholders in a prepared statement. By the way, you can do all of the updating with a single query, like:

   "UPDATE $username SET peakflow1 = ?, peakflow2 = ?, ... WHERE day = ?"
   

4. You aren't executing that SQL. You assign the SQL string to the $sql variable, then immediately exit() without doing anything with it.

With what you're doing here, you may be able to simplify your code by doing an "UPSERT", basically instead of running three queries, (check if exists, insert if not, update if so) you can run one query that will either insert or update. In MySQL you could use the INSERT... ON DUPLICATE KEY UPDATE syntax, provided the day column is defined as unique.