user3116554 user3116554 - 5 months ago 13
MySQL Question

SQL DateTime query not executing

This query executes flawlessly:

public function alter_orders()
{

return $this->db->query("UPDATE `orders` SET `Ready_Date_Time` = '2016-06-02 00:00:00', `Delivery_Date_Time` = '2016-06-01 00:00:00', `Status` = $Status WHERE `orders`.`Order_ID` = $primary_key;");

}


But this doesn't:

public function alter_orders()
{

$primary_key = 1;
$Ready_Date_Time = "2016-06-02 00:00:00";
$Delivery_Date_Time = "2016-06-02 00:00:00";
$Status = 1;

return $this->db->query("UPDATE `orders` SET `Ready_Date_Time` = $Ready_Date_Time, `Delivery_Date_Time` = $Delivery_Date_Time, `Status` = $Status WHERE `orders`.`Order_ID` = $primary_key");
}


I never get returned from this function with the 2nd query. I don't know what is going wrong.

Answer

You still need to wrap the dates within your query in quotes. Being inside variables doesn't mean that SQL doesn't need them. The variables' values are interpolated into the query before the query is executed and is plain text text as far as MySQL is concerned.

public function alter_orders()
{

    $primary_key = 1;
    $Ready_Date_Time = "2016-06-02 00:00:00";
    $Delivery_Date_Time = "2016-06-02 00:00:00";
    $Status = 1;

    return $this->db->query("UPDATE `orders` SET `Ready_Date_Time` = '$Ready_Date_Time', `Delivery_Date_Time` = '$Delivery_Date_Time', `Status` = $Status WHERE `orders`.`Order_ID` = $primary_key");
}