Date being passed as NULL to the database

Question

Date being passed as NULL to the database

Navigation

#1 by (3 votes)
#2 by (1 votes)

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 run SELECT DATE_ADD(CURRENT_TIMESTAMP,INTERVAL N MONTH) the function returns me the current date plus N months, but insert is not working.

After executing I circled the script var_dump($stmt->debugDumpParams());

Where should appear the string of $data_pagamento appears:

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

Aliases, even if I run directly in the database:

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

asked by anonymous 17.10.2016 / 21:54

2 answers

1

prepare is probably escaping 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 it tries to put the string DATE_ADD(CURRENT_TIMESTAMP,INTERVAL N MONTH) on the date, it will NULL .

I do not know a way to do this correctly using prepared statement , but the tip given by @Bacco is good. Have you tried this?

Abs.

18.10.2016 / 01:41

Add 3 forms to 1 submit FileWriter and FileOutputStream: When should I work with each of them?

score 3 · Accepted Answer

In this line you are trying to put $data_pagamento in the DB:

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

If it is a date field, the string must 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 this value does not make sense for SQL. If you want to do the calculation by SQL, you must enter it in 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));