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
