PDO查询更新不在查询中的datetime列 [英] PDO query updating a datetime column not in query

问题描述

PDO准备的更新语句以某种方式更新表中所选记录的datetime列，即使该特定的datetime列甚至不在查询中.

A PDO prepared update statement is somehow updating the datetime column for the selected record in the table, even though that particular datetime column is not even in the query.

if(isset($_POST['editCriteria']))
{
    $value = $_POST['editCriteria'];

    $editusername = $value['editusername'];
    $hiddenUsername = $value['hiddenUsername'];
    $editfullname = $value['editfullname'];
    $editemail = $value['editemail'];
    $edituserlevel = $value['edituserlevel'];
    $editdivision = $value['editdivision'];
    $editdept = $value['editdept'];
    $editphone = $value['editphone'];

    try
    {
        $dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $update = $dbc->prepare("UPDATE users_edi SET username = :uname, 
        fullname = :fname, userlevel = :ulevel, email = :uemail, 
        division = :udivision, dept = :udept, phone = :uphone WHERE username = :hname");

        $update->execute([
            'uname' => $editusername,
            'fname' => $editfullname,
            'ulevel' => $edituserlevel,
            'uemail' => $editemail,
            'udivision' => $editdivision,
            'udept' => $editdept,
            'uphone' => $editphone,
            'hname' => $hiddenUsername
        ]);    

        if($update)
        {
            echo "Success: User has been updated.";
        }
    }
    catch(PDOException $e)
    {
        echo "Error: " . $e->getMessage();
    }
}

在数据库表中，有一列名为lastLoginDate的列正在更新为当前日期时间.

In the database table, there is a column called lastLoginDate that is being updated to the current datetime.

如果您在上面的更新语句中注意到，则该查询不包含lastLoginDate.

If you'll notice in the update statement above, the query does not include lastLoginDate.

在查询中甚至没有更新lastLoginDate时如何更新?

How is lastLoginDate being updated when it's not even in the query?

推荐答案

使用SHOW CREATE TABLE命令后，lastLoginDate列确实存在触发器.

Upon using the SHOW CREATE TABLE command, there was indeed a trigger on the lastLoginDate column.

CREATE TABLE `users_edi` (
`username` varchar(30) NOT NULL DEFAULT '',
`fullname` varchar(50) DEFAULT NULL,
`userlevel` tinyint(1) unsigned NOT NULL,
`ipaddress` varchar(30) DEFAULT NULL,
`email` varchar(150) DEFAULT NULL,
`entrydate` datetime DEFAULT NULL,
`division` varchar(35) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL,
`userid` varchar(32) DEFAULT NULL,
`timestamp` int(11) unsigned NOT NULL,
`job_title` varchar(30) DEFAULT NULL,
`dept` varchar(50) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`lastLoginDate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, // <-- here
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我将不得不问另一个有关如何删除此触发器的问题.

I will have to ask another question on how to remove this trigger.

这篇关于PDO查询更新不在查询中的datetime列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！