Insert DATETIME into mysql databse using JSON/PHP

Question

I have a database that contains an DATETIME column, I want to insert current date into it using JSON/PHP, but I kept having this error :

Object of class DateTime could not be converted to string in /storage/h3/744/754744/public_html/SendBookingReq.php on line 13 And this is my php file

<?php
require("password.php");
$connect = mysqli_connect("localhost", XXXX, XXXX, XXXX);
$driver_id = $_POST["driver_id"];
$email = $_POST["email"];
$duree = $_POST["duree"];
$distance = $_POST["distance"];
$response = array();
$dt_obj = new DateTime($response['DateTime'], new 
DateTimeZone('America/Chicago')); 
$dt_obj->setTimezone(new DateTimeZone('Europe/Paris')); 
$dt_obj->format('d-m-Y H:i:s');
echo $dt_obj; 
 function AddRequest() {
    global $connect, $driver_id, $email, $duree, $distance, $dt_obj ;
    $statement = mysqli_prepare($connect, "INSERT INTO demande (driver_id, pass_id, duree, distance, send_moment) VALUES (?, (SELECT user_id FROM passager WHERE email = ?), ?, ?, '$dt_obj')");
    mysqli_stmt_bind_param($statement, "isdis", $driver_id, $email, $duree, $distance,$dt_obj);
    mysqli_stmt_execute($statement);
    mysqli_stmt_close($statement);     
}

    $response["success"] = false;  

    AddRequest();
    $response["success"] = true;  

echo json_encode($response);
 ?>

And honestly ,I looked a lot for what letter should I use with datetime ex: "s" for string but I didn't find any.

you have 4 placeholders and 5 binds. That alone would have thrown you an error. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 16, 2017 at 16:49
I meant, you (still) have 4 ?'s and 5x isdis parameters. ?, ?, '$dt_obj')") should be ?, ?, ?)") — Funk Forty Niner
– Funk Forty Niner, Commented Apr 16, 2017 at 16:51
The error occures on line 13, which is echo $dt_obj;. The error message is crystal clear (IMHO). echo $dt_obj->format('Y-m-d H:i:s'); would work. But other mistakes, mentioned by @Fred-ii- need to be fixed too. — Paul Spiegel
– Paul Spiegel, Commented Apr 16, 2017 at 17:22

Gordon Linoff · Accepted Answer · 2017-04-16 17:09:08Z

2

Don't insert the datetime value from the application. Application level timestamps are troublesome, because of clock skew, time zones, latency, and so on. Using the database server date time avoids these problems.

Set it based on the database datetime. The simplest way is to default send_moment to have the current date time when a new row is inserted. This is handled in the CREATE TABLE statement (see here).

If you are going to do it in a statement, I would recommend using now():

INSERT INTO demande (driver_id, pass_id, duree, distance, send_moment)  
   SELECT ?, user_id, ?, ?, NOW()
   FROM passager
   WHERE email = ?;

Note that this slightly changes the ordering of the parameters. The email is now the last parameter instead of the second.

I think the bind statement is:

mysqli_stmt_bind_param($statement, "isis", $driver_id, $duree, $distance, $email);

You'll have to double check on the types.

edited Apr 16, 2017 at 17:09

answered Apr 16, 2017 at 16:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Funk Forty Niner Over a year ago

I was going to recommend NOW() also but their syntax was wrong and is the reason why it failed.

Al Ma Over a year ago

I keep having the same error, I think it's because of the last "s" in "isdis" I think because it refers to string, I s there another way to solve it !!

Funk Forty Niner Over a year ago

SELECT ?, user_id, ?, ?, I don't think the OP knows what to do with that; they'll think they can bind columns which can't be done, least that's what I get from this (pseudo) and they probably think so also. Unless that is some guru binding that I don't know about.

Al Ma Over a year ago

I think this too :/

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Error

Object of class DateTime could not be converted to string in /storage/h3/744/754744/public_html/SendBookingReq.php on line 13

This error occurs because of echo $dt_obj on line 13, but echo outputs one or more strings. You may want to use var_dump($dt_obj) or print_r($dt_obj) instead.

EDITED AFTERWARDS

<?php
require("password.php");
$connect = mysqli_connect("localhost", XXXX, XXXX, XXXX);
$driver_id = $_POST["driver_id"];
$email = $_POST["email"];
$duree = $_POST["duree"];
$distance = $_POST["distance"];
$response = array();
$dt_obj = new DateTime($response['DateTime'], new 
DateTimeZone('America/Chicago')); 
$dt_obj->setTimezone(new DateTimeZone('Europe/Paris'));

/* create a DATETIME string to use in mysqli_stmt_bind_param() in line 24
*  https://dev.mysql.com/doc/refman/5.5/en/date-and-time-literals.html */ 
$send_time = $dt_obj->format('Y-d-m H:i:s');

function AddRequest() {
    global $connect, $driver_id, $email, $duree, $distance, $send_time ;
    echo($send_time);
    /* proper number of parametres (?) according to the db table's number of columns (5) */
    $statement = mysqli_prepare($connect, "INSERT INTO demande (driver_id, pass_id, duree, distance, send_moment) 
                                           VALUES (?, (SELECT user_id FROM passager WHERE email = ?), ?, ?, ?)");
                                        
    /* http://php.net/manual/en/mysqli-stmt.bind-param.php */
    mysqli_stmt_bind_param($statement, "isdis", $driver_id, $email, $duree, $distance,$send_time);
    mysqli_stmt_execute($statement);
    mysqli_stmt_close($statement);     
}

$response["success"] = false;  

AddRequest();
$response["success"] = true;  

echo json_encode($response);
?>

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 16, 2017 at 17:20

Gfour

837 bronze badges

6 Comments

Al Ma Over a year ago

I replace it with : print_r($dt_obj) But I had this lines :DateTime Object ( [date] => 2017-04-16 19:23:35.647985 [timezone_type] => 3 [timezone] => Europe/Paris ) Recoverable fatal error: Object of class DateTime could not be converted to string in /storage/h3/744/754744/public_html/SendBookingReq.php on line 17

Gfour Over a year ago

@AlMa Would you like to try to change your code like this? $send_time = $dt_obj->format('d-m-Y H:i:s'); function AddRequest() { global $connect, $driver_id, $email, $duree, $distance, $send_time ; $statement = mysqli_prepare($connect, "INSERT INTO demande (driver_id, pass_id, duree, distance, send_moment) VALUES (?, (SELECT user_id FROM passager WHERE email = ?), ?, ?, ?)"); mysqli_stmt_bind_param($statement, "isdis", $driver_id, $email, $duree, $distance,$send_time);

Al Ma Over a year ago

Sir, I've tried your suggestion , well I got : Notice: Undefined index: DateTime in /storage/h3/744/754744/public_html/SendBookingReq.php on line 10 (To be honest I just copied these few lines of $dt_obj so I don't know really what is it) , as far for the insertion I got this 0000-00-00 00:00:00

Al Ma Over a year ago

Noticing that the column send_mement is of DATETIME type and $send_time is a string

Gfour Over a year ago

Ok, try to change date format $send_time = $dt_obj->format('Y-d-m H:i:s')

|

Collectives™ on Stack Overflow

Insert DATETIME into mysql databse using JSON/PHP

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related