尝试更新表中的信息时出现错误吗? [英] Is there an error when I try to update information in my table?

问题描述

我已经在数据库中创建了一个表，用户可以在其中存储有关自身的日常信息，数据已成功保存到表中，但是如果我尝试更新表中已有的数据就会出错(如果用户已经在当天输入了信息，而不是创建新行).

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();
      }
    }
  }
}

导致问题的代码部分从$sql = "UPDATE $username (SET peakflow1 = $peakflow1 WHERE day=$day);";行开始. 除了屏幕顶部的"sqlerror"错误消息外，屏幕上没有显示任何结果，但是表不会更新.

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.

推荐答案

我看到了更新的一些问题. (在运行时可能还会出现其他问题，但这正是我在您发布的代码中看到的.)

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. 在此部分:

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);";
    ...

所有这些$sql = "UPDATE ...表达式都覆盖了$sql变量，因此在该部分的末尾$sql将仅保留最后一个查询.

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.

SET peakflow1 ...等括号最多是不必要的，我认为它们会导致SQL语法错误.

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

这些SQL字符串中的任何变量都没有引号，并且其中一些包含字符串.这将导致SQL语法错误. (请参阅何时使用单引号，双引号，以及MySQL中的反引号.)您应该避免此问题，方法是像执行插入操作一样执行更新，方法是将变量绑定到预准备语句中的占位符.顺便说一句，您可以使用一个查询来完成所有更新，例如:

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 = ?"

您没有执行该SQL.您将SQL字符串分配给$sql变量，然后立即对exit()进行任何操作.

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

通过此处的操作，您可以通过执行"UPSERT"来简化代码，基本上不必运行三个查询(检查是否存在，如果不存在则插入，如果有则进行更新)，则可以运行一个将插入或更新的查询.在MySQL中，您可以使用 INSERT... ON DUPLICATE KEY UPDATE 语法，前提是day列定义为唯一.

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.

这篇关于尝试更新表中的信息时出现错误吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！