How do I fix the error when updating information in a MySQL table?

Emily

I'm having some problems trying to work out how to update a MySql table with my php code. This is the section so far, the code should either update the table or add a new column depending on weather an new column has already been made in the database on that date.

Edit: this is a lot of code, just to give context to what I am trying to do, the part of code throwing the error is shown separately below as well :)

  $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, peakflow2, coughing, tightChest, shortBreath, wheezing, symptomOne, symptomTwo, medication, mood, comments, overall WHERE day) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
      $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, "iisiiiiiiiiss",  $peakflow1, $peakflow2, $coughing,  $tightChest,    $shortBreath,   $wheezing,  $symptomOne,    $symptomTwo,    $medication,    $mood,  $comments,  $overall, $day);
        mysqli_stmt_execute($stmt); //executes the statement

        echo "<script type='text/javascript'>alert('Data entered successfully!');</script>";
        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
        echo "<script type='text/javascript'>alert('Data entered successfully!');</script>";
        header("Location: ../home.php?sql=success");
        exit();
      }
    }
  }

This is the part of code that the error is coming from:

  $sql = "UPDATE $username SET (peakflow1, peakflow2, coughing, tightChest, shortBreath, wheezing, symptomOne, symptomTwo, medication, mood, comments, overall WHERE day) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
  $stmt = mysqli_stmt_init($conn);
  if (!mysqli_stmt_prepare($stmt, $sql)) {

This is the error I am currently getting:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(peakflow1, peakflow2, coughing, tightChest, shortBreath, wheezing, symptomOne, ' at line 1 in C:\Users\MMRUD\Documents\XAMPP\htdocs\AsthmaAssistant\php_code\todaysdata.php:47 Stack trace: #0 C:\Users\MMRUD\Documents\XAMPP\htdocs\AsthmaAssistant\php_code\todaysdata.php(47): mysqli_stmt_prepare(Object(mysqli_stmt), 'UPDATE test SET...') #1 {main} thrown in C:\Users\MMRUD\Documents\XAMPP\htdocs\AsthmaAssistant\php_code\todaysdata.php on line 47

David

Your update syntax is wrong, it looks like you've confused it with INSERT syntax. Instead of something like this:

SET (Field1, Field2) = (?, ?)

you'd do something like this:

SET Field1 = ?, Field2 = ?

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How do I fix error in updating table in SQL?

How do I fix 404 error when updating package lists?

How do I fix this error with creating a virtual table in MySQL?

How do I fix and fit all the information to stay inside the table?

How do I fix this MYSQL//Phpmyadmin error?

I have an error when I run it, how do I fix it?

How to fix error when dropping a Foreign key in MySQL table?

How to fix "Failed to download repository information" error while updating?

How can I fix this error when i try to add a database DataSource to visual studio "Table mysql.proc doesn't exist"

Error: GooglePlayServicesUtil﹕ Internal error occurred. Please see logs for detailed information. How do I fix this?

Azure: How do I fix this Error when Creating Service Principle?

How do I fix the error when using Validation?

How do I fix 401 error when using OMDB API?

How do I fix an error when sending an email in django?

How to fix this mysql create table syntax error

How to fix broken line of table when querying information from database

How will I fix this table using MySQL?

How can I fix MySQL error #1064?

Error updating column in MySQL when using another table as reference

How do I fix this to avoid this key error?

How do I fix this Java notarization error?

Please how do I fix this SMTPConnectError Error?

How do I fix a dimension error in TensorFlow?

How do I fix this generic constraint error?

How do i fix this String formatting error

How do I fix this Eclipse error?

How do I fix the following error?

What is a exception error and how do I fix it?

What is a NoReverseMatch error, and how do I fix it?