Null when update Date field - MySQL PHP

Question

I'm trying to update date field in my db. Query like this:

$q = "update news set data = STR_TO_DATE('2011-03-05','%Y-%m-%d'), title = '".$title."', content='".$content."',....";

works great, but:

$q = "update news set data = STR_TO_DATE('".$data."','%Y-%m-%d'), title = '".$title."', content='"...";

it is not working :(

I got date:

$data = $_POST["data"];

and it has a value "2013-04-13". I trimmed date and show in popup window and value is correct; Plz help :)

UPDATE It is strange to me, but if i'm using:

$q = "insert into news set data = CAST('".$data."' AS DATE), title = '".$title."', content='".$content."'...";

it works perfectly fine. Only in insert not in update

Script for table:

CREATE TABLE IF NOT EXISTS `news` (`id` int(11) NOT NULL AUTO_INCREMENT, `data` date NOT NULL, `title` text NOT NULL, `content` text NOT NULL, `img` text NOT NULL, `n_img` text NOT NULL, PRIMARY KEY (`id`), KEY `id` (`id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=36 ;

You'd better do PHP var_dump() on your $_POST['data'] - instead of a popup window (I suppose it's javascript alert()?) it can show some "tricky" symbols in variables. — barbashov
– barbashov, Commented Apr 8, 2013 at 22:10
You should escape the post data first. Maybe have a read here: php.net/manual/en/security.database.sql-injection.php — user1655741
– user1655741, Commented Apr 8, 2013 at 22:17
after doing var_dump($data); it returns good data "2013-04-13" — Marcin Szubert
– Marcin Szubert, Commented Apr 9, 2013 at 5:36

I wrestled a bear once. · Accepted Answer · 2013-04-08 22:13:34Z

1

why are you using STR_TO_DATE? looks like your date is already in the correct format. try removing it and just inserting as is?

might want to escape it first though.

$data = mysql_real_escape_string($_POST["data"]);

answered Apr 8, 2013 at 22:13

I wrestled a bear once.

23.5k20 gold badges74 silver badges122 bronze badges

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

8 Comments

Marcin Szubert Over a year ago

mysql_real_escape_string doesn't help. I'm using STR_TO_DATE because i have tried many options and with STR_TO_DATE it start working in phpMyAdmin

I wrestled a bear once. Over a year ago

@MarcinSzubert no, mysql_real_escape_string isn't going to fix your problem, it's just giong to protect your website from sql injection. There may be an issue with your table structure, the PHP looks fine. Can you export the table and post it in your question?

Marcin Szubert Over a year ago

Done. Thank you for your commitment

I wrestled a bear once. Over a year ago

@MarcinSzubert I've never seen that syntax before on an insert query. first, stop trying to get all fancy with the date. it's not nessecary, if you're doing everything else correctly, you should be able to insert the date as it is, without any cast or str_to_date, etc.

Marcin Szubert Over a year ago

You were right. Something was bad with the date even if it was displayed in var_dump properly it has 4 more chars (string length should be 10, was 14). I remove all cast, str_to_date, and leave only mysql_real_escape_string. Thanks very much!

|

Collectives™ on Stack Overflow

Null when update Date field - MySQL PHP

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related