php - 我的 PHP PDO 不会运行对数据库 PHPMYADMIN 的更新查询

Question

我有一个 manage-user.php 页面，其中列出了所有用户。当管理员选择编辑某个用户时，管理员将被定向到edit-user.php，管理员可以在其中更新表单中的所有用户数据。我可以将页面定向到 edit-user.php 并且它将加载用户数据，但是当我想更新用户数据但 sql 不允许我时。

这是我来自 PHPMYADMIN 的 tbluser:

CREATE TABLE `tbluser` (
  `U_ID` int(11) NOT NULL,
  `U_USERNAME` varchar(250) NOT NULL,
  `U_PASSWORD` varchar(255) NOT NULL,
  `U_FULLNAME` varchar(255) DEFAULT NULL,
  `U_DOB` date DEFAULT NULL,
  `U_ADDRESS` varchar(255) DEFAULT NULL,
  `U_GENDER` varchar(255) DEFAULT NULL
);

这是我在管理用户.php 中加载所有用户详细信息的表:

<table>
  <thead>
    <tr>
      <th>#</th>
      <th>Full Name</th>
      <th>Login ID</th>
      <th>DOB</th>
      <th>Address</th>
      <th>Gender</th>
      <th>Action</th>
    </tr>
  </thead>
  <tbody>
    <?php 
$sql = "SELECT U_ID, U_FULLNAME, U_USERNAME, U_DOB, U_ADDRESS, U_GENDER from tbluser";
$query = $dbh -> prepare($sql);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
$cnt=1;
if($query->rowCount() > 0)
{
foreach($results as $results)
{ ?>
    <tr>
      <td>
        <?php echo htmlentities($cnt);?>
      </td>
      <td>
        <?php echo htmlentities($results->U_FULLNAME);?>
      </td>
      <td>
        <?php echo htmlentities($results->U_USERNAME);?>
      </td>
      <td>
        <?php echo htmlentities($results->U_DOB);?>
      </td>
      <td>
        <?php echo htmlentities($results->U_ADDRESS);?>
      </td>
      <td>
        <?php echo htmlentities($results->U_GENDER);?>
      </td>

      <td>
        <a href="edit-user.php?U_ID=<?php echo htmlentities($results->U_ID);?>">
          <button class="btn btn-primary">
          <i class="fa fa-edit "></i> Edit
          </button>
      </td>
    </tr>
    <?php $cnt=$cnt+1;}} ?>
  </tbody>
</table>

这是我在 edit-user.php 中的更新查询。我将此查询放在 edit-user.php 页面的顶部:

if(strlen($_SESSION['slogin'])==0)
{ 
  header('location:login.php');
} else
{
  if(isset($_POST['return']))
    {
        $userid=$_POST['userid'];
        $username=$_POST['username'];
        $password=$_POST['password'];
        $fname=$_POST['fname'];
        $dob=$_POST['dob'];
        $address=$_POST['address'];
        $gender=$_POST['gender'];

        $sql="UPDATE tbluser SET 
                U_ID=:userid,
                U_FULLNAME=:fname, 
                U_USERNAME=:username, 
                U_DOB=:dob, 
                U_ADDRESS=:address, 
                U_GENDER=:gender 
                WHERE U_ID=:userid";

        $query = $dbh->prepare($sql);
        $query->bindParam(':userid',$userid,PDO::PARAM_STR);
        $query->bindParam(':username',$username,PDO::PARAM_STR);
        $query->bindParam(':password',$password,PDO::PARAM_STR);
        $query->bindParam(':fname',$fname,PDO::PARAM_STR);
        $query->bindParam(':dob',$dob,PDO::PARAM_STR);
        $query->bindParam(':address',$address,PDO::PARAM_STR);
        $query->bindParam(':gender',$gender,PDO::PARAM_STR);
        $query->execute();

        $lastInsertId = $dbh->lastInsertId();
        if($lastInsertId)
          {
            $_SESSION['msg']="User Updated successfully";
            header('location:manage-user.php');
          }
          else 
          {
            $_SESSION['error']="Something went wrong. Please try again";
            header('edit-user.php?U_ID=<?php echo htmlentities($results->U_ID);?>');
          }
    }

这是显示所有用户详细信息的 edit-user.php HTML 代码。

<form name="update" method="post">
  <?php 
                      $U_ID=$_GET['U_ID'];
                      $sql = "SELECT U_ID, U_FULLNAME, U_USERNAME, U_PASSWORD, U_DOB, U_ADDRESS, U_GENDER from tbluser where U_ID=:U_ID";
                      $query = $dbh -> prepare($sql);
                      $query->bindParam(':U_ID',$U_ID,PDO::PARAM_STR);
                      $query->execute();
                      $results=$query->fetchAll(PDO::FETCH_OBJ);
                      $cnt=1;
                      if($query->rowCount() > 0)
                      {
                        foreach($results as $results)
                          {   ?>
  <div>
    <label>USER ID</label>
    <input class="form-control" type="text" name="userid" autocomplete="off" value="<?php echo htmlentities($results->U_ID);?>" disabled />
  </div>

  <div>
    <label>Full Name</label>
    <input type="text" name="fname" value="<?php echo htmlentities($results->U_FULLNAME);?>" autocomplete="off" required />
  </div>

  <div>
    <label>Login ID</label>
    <input type="text" name="username" value="<?php echo htmlentities($results->U_USERNAME);?>" autocomplete="off" required />
  </div>

  <div>
    <label>Password</label>
    <input type="password" name="password" value="<?php echo htmlentities($results->U_PASSWORD);?>" autocomplete="off" required />
  </div>

  <div>
    <label>DOB</label>
    <input type="date" name="dob" value="<?php echo htmlentities($results->U_DOB);?>" autocomplete="off" />
  </div>

  <div>
    <label>Address</label>
    <input type="password" name="address" value="<?php echo htmlentities($results->U_ADDRESS);?>" autocomplete="off" />
  </div>

  <div>
    <label>Gender: </label>
    <select name="gender" autocomplete="off" required>
      <option selected hidden value="<?php echo htmlentities($results->U_GENDER);?>">
        <?php echo htmlentities($results->U_GENDER);?>
      </option>
      <option value="Male"> Male</option>
      <option value="Female"> Female</option>
    </select>
  </div>

  <button type="submit" name="return">Update</button>
  <?php $cnt=$cnt+1;}} ?>
</form>

Answer 1

由于您的 U_ID 不应更改，并且已经用它发出了请求，因此他应该存在。尝试像这样更新:

编辑用户.php

$sql="UPDATE tbluser SET 
                U_FULLNAME=:fname, 
                U_USERNAME=:username, 
                U_DOB=:dob, 
                U_ADDRESS=:address, 
                U_GENDER=:gender 
                WHERE U_ID=:userid";

删除这一行:

...
$query->bindParam(':password',$password,PDO::PARAM_STR);
...

要在执行时出现错误，请执行以下操作:

if (!$query->execute()) {
    print_r($query->errorInfo());
}
$lastInsertId = $userid;

此外，由于您正在更新而不是插入新记录，因此您不应检查以获取LAST Instert ID IT was not Inserted，它只是更新。