0

I want to insert in mysql database a string value as a datetime. I'm using myqli in php.

<?php
class DBConnection {
    private $conn;
    function __construct() {
    }
    function connect() {
        include_once dirname(__FILE__).'/ConstEmployee.php';
        $this->conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
        if(mysqli_connect_errno()) {
            echo "Failed to connect to database".mysqli_connect_err();
        }
        return $this->conn;
    }
}

Here is my function for inserting the value:

public function createCustomerOrder($id, $pickup_address, $delivery_address, $weight, $volume, $order_date) {
        $op = $this->conn->prepare("INSERT INTO orders (ID_customer, pickup_address, delivery_address, weight, volume, order_date) VALUES (?, ?, ?, ?, ?, STR_TO_DATE(?,'%d-%m-%Y %H:%i:%s'))");
        
        $op->bind_param("issdds", $id, $pickup_address, $delivery_address, $weight, $volume, $order_date);
        
        if($op->execute()) {
            return 1;
        } else {
            return 0;
        }
    }

My function returns 1 and the row is inserted in database but order_date column is null.

Exemple of given date value: 22-08-2020 10:10:10

Dharman
  • 30,962
  • 25
  • 85
  • 135
Alexandra B
  • 1
  • 1
  • If i insert 2020-08-22 10:10:10 the column is still null – Alexandra B Aug 23 '20 at 00:02
  • I created it in phpmyadmin. If i enter in cosole the following query it works fine. INSERT INTO orders (ID_customer, pickup_address, delivery_address, weight, volume, order_date) VALUES (1, 'A', 'B', 40.02, 50.5, STR_TO_DATE('22-08-2020 10:10:10','%d-%m-%Y %H:%i:%s')) – Alexandra B Aug 23 '20 at 00:08
  • What is your table schema for `order_date`? – IncredibleHat Aug 23 '20 at 01:41
  • In orders table i have a datetime type column order_date. – Alexandra B Aug 23 '20 at 01:50
  • Why not fix the date first !?!?! – Strawberry Aug 23 '20 at 09:29
  • I've tried this: $date = DateTime::createFromFormat('d-m-Y H:i:s', $_POST['order_date'])->format('Y-m-d H:i:s'); and changed mysql : "INSERT INTO orders (ID_customer, pickup_address, delivery_address, weight, volume, order_date) VALUES (?, ?, ?, ?, ?, ?)" but still doesn't work – Alexandra B Aug 23 '20 at 12:09

0 Answers0