Date passed as NULL to the database

Asked

Viewed 49 times

1

I have this structure:

//Dia atual + i meses 
$data_pagamento = "DATE_ADD(CURRENT_TIMESTAMP,INTERVAL ".$i." MONTH)";
$sql = "INSERT INTO pagamento(fk_1, fk_2, fk_3, fk_4, fk_5, preco, data_do_pagamento) VALUES (?,?,?,?,?,?,?)";
$stmt = $db->prepare($sql);
$stmt->execute(array(1, 2, 3, 4, 5, $preco, $data_pagamento));

If I execute SELECT DATE_ADD(CURRENT_TIMESTAMP,INTERVAL N MONTH) the function returns me the current date plus N months, but no insert is not working.

After the run I ran the script var_dump($stmt->debugDumpParams());

Where the string of $data_pagamento appears:

Key: Position #6:
paramno=6
name=[0] ""
is_param=1
param_type=2
NULL

By the way, even if I run right into the bank:

INSERT INTO pagamento(fk_1, fk_2, fk_3, fk_4, fk_5, preco, data_do_pagamento) 
VALUES 
(1,2,3,4,5,preco,DATE_ADD(CURRENT_TIMESTAMP,INTERVAL N MONTH)

It works as it should!

php mysql

  • what is the type of the payment data_do_field, this date, datetime? apparently it is all right Marco

  • You are trying to put a string in the field. It makes no sense to try to insert "DATE_ADD(CURRENT_TIMESTAMP,INTERVAL ".$i." MONTH)"; in the field. Either you do this in SQL, or you calculate in PHP.

2 answers

3


On this line you’re trying to put $data_pagamento on DB:

$stmt->execute(array(1, 2, 3, 4, 5, $preco, $data_pagamento));

If it is a date field, the string has to be in this format:

0000-00-00 00:00:00

But you’re trying to insert this string:

DATE_ADD(CURRENT_TIMESTAMP,INTERVAL 1 MONTH)

This is not a string in the right format. And it makes no sense this value for SQL. If you want to do the calculation by SQL, you should inform query:

$sql = "INSERT INTO pagamento(fk_1, fk_2, fk_3, fk_4, fk_5, preco, data_do_pagamento) VALUES (?,?,?,?,?,?,DATE_ADD(CURRENT_TIMESTAMP,INTERVAL ? MONTH))";
$stmt = $db->prepare($sql);
$stmt->execute(array(1, 2, 3, 4, 5, $preco, $i));

  • To better understand, exchange the date field for varchar, and you will better understand what happens. It will be written DATE_ADD(CURRENT_TIMESTAMP,INTERVAL 1 MONTH) literally in the field.

  • Yeah, I came to that conclusion and that answer a few minutes after posting the question, picked that question by the simple fact that it was before :)

1

Probably the prepare is doing the "escape" of the string you sent. With this, I believe the database is receiving the query as follows:

INSERT INTO pagamento(fk_1, fk_2, fk_3, fk_4, fk_5, preco, data_do_pagamento) 
VALUES 
("1","2","3","4","5","preco","DATE_ADD(CURRENT_TIMESTAMP,INTERVAL N MONTH)")

And then when he tries to put the string DATE_ADD(CURRENT_TIMESTAMP,INTERVAL N MONTH) on date, she turns NULL.

I don’t know a way to do this properly using the prepared statement, but the tip given by @Bacco is good. You tried this?

Abs.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.