对行进行 UPDATE 查询，使原始时间保持不变 MySQL

Question

我在此链接下有一个脚本 Order list/ while loop php issues 其中从以下字段检索订单数据行：

order_id | users_id | total | order_date(CURRENT_TIMESTAMP) | shipped

此后，我添加了一个单选按钮，管理员用户可以单击该单选按钮来显示该商品是否已发货。 （它通过提交按钮向发货字段添加“是”或“否”）SQL 查询如下：

UPDATE orders SET shipped='$shipped' WHERE order_id='$id'

该脚本工作正常，但它用时间替换了最初下订单的时间（在“order_date”下）发货按钮已提交，我想保留原来的时间不变。

我可以更改 SQL 查询还是必须使用 php 来实现此目的？如果您需要查看完整的 php 代码，请告诉我。

<?php # edit_user.php

$page_title = 'View Individual Order';
include ('includes/header_admin_user.html');

// If no dealer_code variable exists, redirect the user.
if (!isset($_SESSION['admin_int_id'])) {

   // Start defining the URL.
   $url = 'http://' . $_SERVER['HTTP_HOST']
    . dirname($_SERVER['PHP_SELF']);
   // Check for a trailing slash.
   if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
        $url = substr ($url, 0, -1); // Chop off the slash.
   }
   // Add the page.
   $url .= '/login.php'; 

ob_end_clean(); // Delete the buffer.
header("Location: $url"); 
exit(); // Quit the script.
}

// Check for a valid user ID, through GET or POST.
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) )
   { // Accessed through view_users.php   
    $id = $_GET['id'];

} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) )
   { // Form has been submitted.   
    $id = $_POST['id'];
} else { // No valid ID, kill the script.
    echo '<h1 id="mainhead">Page Error</h1>
    <p class="error">This page has been accessed in error.</p><p><br /><br /></p>';
    include ('./includes/header.html'); 
    exit();
}

?>

<h1>Order Details</h1>

<?php

require_once ('mydatabase.php'); // Connect to the db.

$shipped = $_POST["shipped"];

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

          // Make the query.
          $query = "UPDATE orders SET shipped='$shipped' WHERE order_id=$id";   
          $result = @mysql_query ($query); // Run the query.
          if (mysql_affected_rows() == 1) { // If it ran OK.

          // Print a message.
          echo '<p style="color:#5a8e22;"><strong>The order has been sent.</strong></p>
          <br />';

          } else { // If it did not run OK.
            echo '<p style="color:#be0f34; font-size:120%;"><strong>Error</strong></p>
            <p style="color:#be0f34;">This update request could not be made for one of the following reasons:<br>
            <br>
            <a href="view-all-orders-test.php"><< Go back to order list</a>';   
           echo '<p>' . mysql_error() . '<br /><br />
            Query: ' . $query . '</p>
            </p>
            <br class="clearboth" />
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            </div>
            </div>'
            ; // Debugging message.   
          include ('./includes/footer_admin_user.html'); 
          exit();
          ; // Public message.

          }

} // End of submit conditional.


// Retrieve the user's, order and product information.
$query = "SELECT us.users_id, us.users_sales_guild_id, us.users_first_name, us.users_surname, us.users_dealer_name, us.users_type, 
         us.users_address_street, us.users_address_suburb, us.users_address_state, us.users_address_postcode, 
         us.users_address_phone, us.registration_date, 
         ord.order_id, ord.users_id, ord.total, ord.order_date,  
         oc.oc_id, oc.order_id, oc.products_id, oc.quantity, oc.price,
         prd.products_id, prd.products_name, prd.price      
         FROM users AS us, orders AS ord, order_contents AS oc, products AS prd  
         WHERE ord.order_id=$id
         AND us.users_id = ord.users_id
         AND ord.order_id = oc.order_id
         AND oc.products_id = prd.products_id    
         ";

$result = mysql_query ($query) or die(mysql_error());

if (mysql_num_rows($result)) { // Valid user ID, show the form.

    $row = mysql_fetch_array($result, MYSQL_NUM);

   echo '<table width="400" border="0" cellspacing="0" cellpadding="0">
        <tr valign="top">
        <td width="65%"><p><strong>Deliver to:</strong><br />
        ' . $row[2] . ' ' . $row[3] . ' <br />
        ' . $row[5] . ', ' . $row[1] . ' <br />
        </p>

        <p><strong>Dealership:</strong><br />
        ' . $row[4] . ' <br />
        ' . $row[6] . ' <br />
        ' . $row[7] . ', ' . $row[8] . ', ' . $row[9] . ' <br />
        </p>

        </td>
        <td width="35%">
        <p><strong>Order Total:</strong><br />
        ' . $row[14] . ' pts <br />
        </p>
        <p><strong>Date:</strong><br />
        ' . $row[11] . ' <br />
        </p>
        </td>
        </tr>
        </table>

        <form method="post" action="view-ind-order-test.php">
        Has this order been shipped?<br />
        Yes:<input type="radio" value="YES" name="shipped">  No:<input type="radio" value="NO" name="shipped"><br />
        <input type="submit" name="submit" value="Submit" />
        <input type="hidden" name="submitted" value="TRUE" />
        <input type="hidden" name="id" value="' . $id . '" />
        </form><br />

        <p></p> 

        <table border="0" width="400" cellspacing="1" cellpadding="5">
        <tr class="top">
        <td align="left" ><b>Product</b></td>
        <td align="center"><b>Qty</b></td>
        <td align="center"><b>Price</b></td>

        </tr>';

    $bg = '#dddddd'; // Set the background color.

    do { // DO WHILE loop start

    $bg = ($bg=='#eaeced' ? '#dddddd' : '#eaeced');

    echo    '<tr bgcolor="' . $bg . '">';

    echo    '<td align="left">' . $row[22] . '</td>
            <td align="center">' . $row[19] . '</td>
            <td align="center">' . $row[20] . '</td>
            </tr>';

            } while($row = mysql_fetch_array($result, MYSQL_NUM));// end of WHILE loop

        echo '</table>
            <br><br>
            <p><a href="view-all-orders-test.php"> << Back to Orders</a></p>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            ';

} else { // Not a valid user ID.
    echo '<h1 id="mainhead">Page Error</h1>
    <p class="error">This page has been accessed in error.</p><p><br /><br /></p>';   
}

mysql_close(); // Close the database connection.


?>

<p>footer</p>

<?php
include ('./includes/footer_admin_user.html'); // Include the HTML footer.
?>

Answer 1

@gview 是对的，您应该更改表并将其设置为 DEFAULT CURRENT_TIMESTAMP。如果无法更改表，可以更改更新查询以设置 order_date = order_date，这将阻止其更新：

UPDATE orders SET shipped='$shipped', order_date = order_date WHERE order_id='$id'

Answer 2

这是 mysql 时间戳的一个众所周知的问题。您可以阅读有关时间戳的详细信息 此处

时间戳的默认行为是在插入和更新时更新。您可以通过更改表并向时间戳定义添加默认值来更改此设置：

DEFAULT CURRENT_TIMESTAMP

Answer 3

我会在数据库中创建另一个名为“order_time”的字段或类似的字段。最好知道原始日期和更新日期。除非您修改设置，否则每次更改某些内容时时间戳都会更新。

使用 PHP 的 date() 函数作为 order_time 列的变量，并为其提供所需的确切时间布局。