从表中选择一个值，并使用它来更新同一表的字段 - Select a value from a table and use it to update a field of the same table

Select a value from a table and use it to update a field of the same table

本文关键字：更新字段选择一个 | 更新日期: 2023-09-27

我正在获取一些 12 小时格式的时间数据，我想从 12 小时转换为 24 小时。这是我写的使用 mysqli 的脚本。

<?php
$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "qplat";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "select the_time from r_data where transaction_type = 'send'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // output data of each row
    $id = 8980;
    while($row = $result->fetch_assoc()) {
    $new_id = $id++;
        $new_time = $row["the_time"];
        echo "the time is: " . $row["the_time"].'<br/>';
        $time = date("Hi", strtotime("$new_time"));
        $sql2 = "update r_data set 24_hour_time = '$time' where transaction_type = 'send' and id = $new_id";
        $conn->query($sql2);
    }
} else {
    echo "0 results";
}
$conn->close();
?>

该脚本更新列24_hour_time一次，仅省略所有其他行。

这可以使用一个表来完成，还是我必须插入到另一个表中然后移回数据？

你可以像这样使用代码点火器来解决你遇到的问题

public function up(){
    $query = $this->db->query("select id,the_time from r_data where transaction_type = 'send'");
    foreach ($query->result() as $row)
    {
    $id = $row->id;
    $new_time = $row->the_time;
    $time = date("Hi", strtotime("$new_time"));
    $data = array(
               '24_hour_time' => $time
            );
    $this->db->where('id', $id);
    $this->db->update('r_data', $data); 
    }
    }