无法通过php更新sql表

Question

这是我用来尝试更新记录的代码。我没有看到任何问题，但是它没有引发错误并指出它已成功运行。我尝试了几种其他方法，它们只是不更新MSSQL表中的行。数据库/表在运行Windows 10和IIS的我的机器上是本地的（我知道！）。

<?php

/**

 * Use an HTML form to edit an entry in the

 * users table.

 *

 */

 require "C:\inetpub\wwwroot\Remediation\config.php";

 require "C:\inetpub\wwwroot\Remediation\common.php";

 if (isset($_POST['submit'])) {

   if (!hash_equals($_SESSION['csrf'], $_POST['csrf'])) die();

   try {

     $connection = new PDO($dsn, $username, $password);

     $user =[

       "id"        => $_POST['id'],

       "firstname" => $_POST['firstname'],

       "lastname"  => $_POST['lastname'],

       "email"     => $_POST['email'],

       "age"       => $_POST['age'],

       "location"  => $_POST['location'],

       "date"      => $_POST['date']

     ];

     $sql = "UPDATE users

             SET id = :id,

               firstname = :firstname,

               lastname = :lastname,

               email = :email,

               age = :age,

               location = :location,

               date = :date

             WHERE id = :id";

   $statement = $connection->prepare($sql);

   $statement->execute($user);

   } catch(PDOException $error) {

       echo $sql . "<br>" . $error->getMessage();

   }

 }

 if (isset($_GET['id'])) {

   try {

     $connection = new PDO($dsn, $username, $password);

     $id = $_GET['id'];

     $sql = "SELECT * FROM users WHERE id = :id";

     $statement = $connection->prepare($sql);

     $statement->bindValue(':id', $id);

     $statement->execute();

     $user = $statement->fetch(PDO::FETCH_ASSOC);

   } catch(PDOException $error) {

       echo $sql . "<br>" . $error->getMessage();

   }

 } else {

     echo "Something went wrong!";

     exit;

 }

 ?>    

 <?php if (isset($_POST['submit']) && $statement) : ?>

    <blockquote><?php echo escape($_POST['firstname']); ?> successfully 

 updated.</blockquote>

 <?php endif; ?>

 <h2>Edit a user</h2>

我到底在哪里错。我可以连接，修改和创建就好了。我是否需要调整$ USER或查询？

Answer 1

我犯了一个菜鸟错误，我试图在MSSQL中更新ID和时间戳列，这是不允许的。我将自己的代码更改为此，并且效果很好：&nbsp; &nbsp;$user =[&nbsp; &nbsp; &nbsp;"id"&nbsp; &nbsp; &nbsp; &nbsp; => $_POST['id'],&nbsp; &nbsp; &nbsp;"firstname" => $_POST['firstname'],&nbsp; &nbsp; &nbsp;"lastname"&nbsp; => $_POST['lastname'],&nbsp; &nbsp; &nbsp;"email"&nbsp; &nbsp; &nbsp;=> $_POST['email'],&nbsp; &nbsp; &nbsp;"age"&nbsp; &nbsp; &nbsp; &nbsp;=> $_POST['age'],&nbsp; &nbsp; &nbsp;"location"&nbsp; => $_POST['location'],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;];&nbsp; &nbsp;$sql = "UPDATE users&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET firstname = :firstname,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;lastname = :lastname,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;email = :email,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;age = :age,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; location = :location&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE id = :id";

Answer 2

您是否直接在MSSQL Management Studio中尝试了该查询？之后，您会看到它工作正常，请尝试“回显” POST变量以查看是否正确获取了它们。

Answer 3

我没看到INSERT。相反，您仅使用UPDATE意味着仅修改现有记录。这是你想要的吗？&nbsp;$sql = "UPDATE users&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET id = :id,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;firstname = :firstname,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;lastname = :lastname,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;email = :email,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;age = :age,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;location = :location,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;date = :date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE id = :id";在这里，您通过选择一个（所有）记录id = :id，并在此处SET id = :id将其设置id为一个已设置的值。Is不会破坏某些内容，但这暗示您逻辑中的某些内容是错误的。顺便说一句，您允许每个客户更改记录。不是SQL注入，但很可能非常糟糕。