最受欢迎

sql UPDATES更新行中更改列之后的每一列

sql UPDATES updates every column in the row that comes after the change column

本文关键字：一列 之后 sql 更新 UPDATES | 更新日期: 2023-09-27

我正在使用UPDATE查询对数据库进行更改。我对一行中某个特定单元格所做的更新将插入数据库。然而，发生的情况是，该单元格后面的任何空白日期空间都被输入为0000-00-00。

例如，当我更新最终审查日期时，这就是我应该得到的

Before Update
Date Received   Final Review Date       Date Delivered         Date Accepted  
2015-03-03  
After Update
Date Received   Final Review Date       Date Delivered         Date Accepted  
2015-03-03        2015-08-05
Instead I get this:
Date Received   Final Review Date       Date Delivered         Date Accepted  
2015-03-03        2015-08-05              0000-00-00              0000-00-00

我试过解决这个问题，但我对mysqli/php还很陌生，所以我知道我可能错过了一些简单的东西。如有任何帮助，我们将不胜感激。

<?php
$servername = "localhost";
$username = "xxx";
$password = "xxx";
$dbname = "oldga740_SeniorProject";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
}
if (isset($_POST['update'])){
$UpdateQuery = "UPDATE Projects SET Project='$_POST[project]', Client='$_POST[client]', LastName='$_POST[lastname]', DateReceived='$_POST[datereceived]', FinalReviewDate='$_POST[finalreviewdate]', DateDelivered='$_POST[datedelivered]', DateAccepted='$_POST[dateaccepted]' WHERE Project='$_POST[hidden]'";
mysqli_query($conn, $UpdateQuery);
};
$sql = "SELECT * FROM Projects";
$result = $conn->query($sql);

echo "<table>
<tr>
<th>Project</th>
<th>Client</th>
<th>Last Name</th>
<th>Date Received</th>
<th>Final Review Date</th>
<th>Date Delivered</th>
<th>Date Accepted</th>
</tr>";
while($record = mysqli_fetch_array($result))
{
if ($result->num_rows > 0){
echo "<form action='mynewform.php' method='post'>";
echo "<tr>"; 
echo "<td>" . "<input type='text' name='project' value='" . $record['Project'] . "' /></td>";
echo "<td>" . "<input type='text' name='client' value='" . $record['Client'] . "'/></td>";
echo "<td>" . "<input type='text' name='lastname' value='" . $record['LastName'] . "' /></td>";
echo "<td>" . "<input type='text' name='datereceived' value='" . $record['DateReceived'] . "' /></td>";
echo "<td>" . "<input type='text' name='finalreviewdate' value='" . $record['FinalReviewDate'] . "' /></td>";
echo "<td>" . "<input type='text' name='datedelivered' value='" . $record['DateDelivered'] . "' /></td>";
echo "<td>" . "<input type='text' name='dateaccepted' value='" . $record['DateAccepted'] . "' /></td>";
echo "<td>" . "<input type='hidden' name='hidden' value='" . $record['Project'] . "' /></td>";
echo "<td>" . "<input type='submit' name='update' value='update' /></td>";
echo "<td>" . "<input type='submit' name='delete' value='delete' /></td>";
echo "</tr>";
echo "</form>";
}
}
echo "</table>";
?>

<?php
    $conn->close();
?>

</body>
</html>

问题：

我对一行中某个特定单元格所做的更新将插入数据库。然而，发生的情况是，该单元格后面的任何空白日期空间都被输入为0000-00-00。

解决方案：

根据您的评论，

数据库已经将这些列设置为默认NULL。唯一没有的是DateReceived列。

我假设已经设置了Project、Client、LastName和DateReceived。因此，如果用户没有指定任何要更新的数据，解决方案是插入NULL值，如下所示：

// your code
if (isset($_POST['update'])){
    $project = trim($_POST['project']);
    $client = trim($_POST['client']);
    $lastname = trim($_POST['lastname']);
    $datereceived = $_POST['datereceived'];
    $UpdateQuery = "UPDATE Projects SET Project='{$project}', Client='{$client}', LastName='{$lastname}', DateReceived='{$datereceived}'";
    if(empty($_POST['finalreviewdate'])){
        $UpdateQuery .= ", FinalReviewDate = NULL";
    }else{
        $UpdateQuery .= ", FinalReviewDate = '{$_POST['finalreviewdate']}'";
    }
    if(empty($_POST['datedelivered'])){
        $UpdateQuery .= ", DateDelivered = NULL";
    }else{
        $UpdateQuery .= ", DateDelivered = '{$_POST['datedelivered']}'";
    }
    if(empty($_POST['dateaccepted'])){
        $UpdateQuery .= ", DateAccepted = NULL";
    }else{
        $UpdateQuery .= ", DateAccepted = '{$_POST['dateaccepted']}'";
    }
    $UpdateQuery .= " WHERE Project='{$_POST['hidden']}'";
    mysqli_query($conn, $UpdateQuery);
};
// your code

这是完整的代码：（tested）

<?php
$servername = "localhost";
$username = "xxx";
$password = "xxx";
$dbname = "oldga740_SeniorProject";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
}
if (isset($_POST['update'])){
    $project = trim($_POST['project']);
    $client = trim($_POST['client']);
    $lastname = trim($_POST['lastname']);
    $datereceived = $_POST['datereceived'];
    $UpdateQuery = "UPDATE Projects SET Project='{$project}', Client='{$client}', LastName='{$lastname}', DateReceived='{$datereceived}'";
    if(empty($_POST['finalreviewdate'])){
        $UpdateQuery .= ", FinalReviewDate = NULL";
    }else{
        $UpdateQuery .= ", FinalReviewDate = '{$_POST['finalreviewdate']}'";
    }
    if(empty($_POST['datedelivered'])){
        $UpdateQuery .= ", DateDelivered = NULL";
    }else{
        $UpdateQuery .= ", DateDelivered = '{$_POST['datedelivered']}'";
    }
    if(empty($_POST['dateaccepted'])){
        $UpdateQuery .= ", DateAccepted = NULL";
    }else{
        $UpdateQuery .= ", DateAccepted = '{$_POST['dateaccepted']}'";
    }
    $UpdateQuery .= " WHERE Project='{$_POST['hidden']}'";
    mysqli_query($conn, $UpdateQuery);
};
$sql = "SELECT * FROM Projects";
$result = $conn->query($sql);

echo "<table>
<tr>
<th>Project</th>
<th>Client</th>
<th>Last Name</th>
<th>Date Received</th>
<th>Final Review Date</th>
<th>Date Delivered</th>
<th>Date Accepted</th>
</tr>";
while($record = mysqli_fetch_array($result))
{
if ($result->num_rows > 0){
echo "<form action='process.php' method='post'>";
echo "<tr>"; 
echo "<td>" . "<input type='text' name='project' value='" . $record['Project'] . "' /></td>";
echo "<td>" . "<input type='text' name='client' value='" . $record['Client'] . "'/></td>";
echo "<td>" . "<input type='text' name='lastname' value='" . $record['LastName'] . "' /></td>";
echo "<td>" . "<input type='text' name='datereceived' value='" . $record['DateReceived'] . "' /></td>";
echo "<td>" . "<input type='text' name='finalreviewdate' value='" . $record['FinalReviewDate'] . "' /></td>";
echo "<td>" . "<input type='text' name='datedelivered' value='" . $record['DateDelivered'] . "' /></td>";
echo "<td>" . "<input type='text' name='dateaccepted' value='" . $record['DateAccepted'] . "' /></td>";
echo "<td>" . "<input type='hidden' name='hidden' value='" . $record['Project'] . "' /></td>";
echo "<td>" . "<input type='submit' name='update' value='update' /></td>";
echo "<td>" . "<input type='submit' name='delete' value='delete' /></td>";
echo "</tr>";
echo "</form>";
}
}
echo "</table>";
?>

<?php
    $conn->close();
?>
</body>
</html>

这可能是因为在创建表时，您将列类型指定为DATE，并将值作为空字符串插入没有值的列。因此，在更新一个值时，其他列的值为0000-00-00。DATE列不能将空字符串作为默认值。您可以在此处根据数据类型检查默认值。

您可以做的最好的事情是更改列，使其默认值为NULL，并在第一次将值插入表时，为没有任何值的列指定值为NULL。因此，在更新时，这些列的值不会为0000-00-00，它们的值将为NULL。

例如，考虑表名为test，列名为date1、data2和date4

CREATE TABLE测试（日期1日期默认为NULL，日期2日期默认为NULL，日期3日期默认为NULL，日期4日期默认为空）

您也可以使用以下查询修改现有列

ALTER TABLE test MODIFY COLUMN date1 DATE DEFAULT NULL

当第一次插入值时，当您只有第一列的值时，像这样插入

插入测试值（'2015-12-07'，NULL，NULL）

因此，稍后当您更新任何其他列时，只有该值会被更新，而其他列不会将值作为0000-00-00。它们的值将为NULL。

我测试了这个，它对我有效。

更改包含日期的字段的数据类型。然后，当您在更新时保留空字段时，如果您的数据库字段也是空的，则它将分配给"0000-00-00"，否则它将保持不变。现在更改数据库数据类型->顶部的oto"结构"选项卡->找到需要更改数据类型的行，然后单击该字段的changeaction。->在下拉菜单中选择数据类型。

这是代码中的一个小片段，但它是负责输入数据的部分。如果日期字段为空，则它们将获得'' ( empty string )的赋值，这有望防止它们被0000-00-00更新。

if( isset( $_POST['update'] ) ){
    /* For convenience, shorthand object notation for $_POST */
    $pd=(object)$_POST;
    /* Assign each parameter as a variable - using false or null as appropriate. There is some rudimentary filtering at least */
    $project            = isset( $pd->project ) && !empty( $pd->project ) ? strip_tags( filter_input( INPUT_POST, 'project', FILTER_SANITIZE_STRING ) ) : false;
    $client             = isset( $pd->client ) && !empty( $pd->client ) ? strip_tags( filter_input( INPUT_POST, 'client', FILTER_SANITIZE_STRING ) ) : false;
    $lastname           = isset( $pd->lastname ) && !empty( $pd->lastname ) ? strip_tags( filter_input( INPUT_POST, 'lastname', FILTER_SANITIZE_STRING ) ) : false;
    $datereceived       = isset( $pd->datereceived ) && !empty( $pd->datereceived ) ? strip_tags( filter_input( INPUT_POST, 'datereceived', FILTER_SANITIZE_STRING ) ) : false;
    $finalreviewdate    = isset( $pd->finalreviewdate ) && !empty( $pd->finalreviewdate ) ? strip_tags( filter_input( INPUT_POST, 'finalreviewdate', FILTER_SANITIZE_STRING ) ) : '';
    $datedelivered      = isset( $pd->datedelivered ) && !empty( $pd->datedelivered ) ? strip_tags( filter_input( INPUT_POST, 'datedelivered', FILTER_SANITIZE_STRING ) ) : '';
    $dateaccepted       = isset( $pd->dateaccepted ) && !empty( $pd->dateaccepted ) ? strip_tags( filter_input( INPUT_POST, 'dateaccepted', FILTER_SANITIZE_STRING ) ) : '';
    $hidden             = isset( $pd->hidden ) && !empty( $pd->hidden ) ? strip_tags( filter_input( INPUT_POST, 'hidden', FILTER_SANITIZE_STRING ) ) : false;
    /* The sql MUST have these to be processed */
    if( $project && $client && $hidden && $lastname && $datereceived ){
        /* The various date fields that were being updated with 0000-00-00 have a default value of empty string in the above vars */
        /* use the defined variables */
        $sql = "update `projects` set 
                `project`='{$project}', `client`='{$client}', `lastname`='{$lastname}', `datereceived`='{$datereceived}',
                `finalreviewdate`='{$finalreviewdate}', `datedelivered`='{$datedelivered}', `dateaccepted`='{$dateaccepted}' 
                where `project`='{$hidden}';";
        /* run the query */
        mysqli_query( $conn, $sql );
    }
};

如果您没有在datatime字段中输入任何数据，它将默认为0000-00-00

如果要更改默认值，可以在表中进行设置。

或者，您可以通过进行标准验证检查来确保数据始终进入数据库

要回答您的问题，请检查表和字段，检查它们的类型。我相信那些为空的不是日期时间，因为默认设置为0000-00-00的是日期时间。

只需检查并确保它们都在同一设置上。

相关文章：
最新更新
www.56WenDa.com 2012-2023 | php问答网 | php学习