PHP strtotime returns empty value

Question

Well i built my website to write dates to database column in the following format: 2014/04/01

Now i've come to realize this is not helpful when i want to sort by date or manipulate date so i decided to change all date to to timestamps.

i applied the following fix :

    $sql = mysql_query("SELECT * FROM TABLE")or(die(mysql_Error()));
while($info = mysql_fetch_array($sql)){
        $newdate = strtotime($info['date']);
        mysql_query("UPDATE TABLE SET date = '$newdate' WHERE id = $info[id]")or(die(mysql_error()));
}

the problem is all the date columns are empty now, What am i doing wrong ?

In the UPDATE statement im replacing the old date with the new timestamp. — user3316501, Aug 21 '14 at 15:38
@amphetamachine originally the $info['date'] contains a date like this 2014/04/01 after i run the script date column is empty. — user3316501, Aug 21 '14 at 15:39
You do know that by default when MySQL has a null in a datetime column it returns the positively unhelpful "0000-00-00 00:00" value, which `strtotime` can't parse (returns false). — amphetamachine, Aug 21 '14 at 15:41
@amphetamachine the date filed is set to text, i know this is a poor implementation but it accepts any value. — user3316501, Aug 21 '14 at 15:46

score 0 · Accepted Answer · answered Aug 21 '14 at 15:52

0

There's what looks like a syntax error on this line:

mysql_query("UPDATE TABLE SET date = '$newdate' WHERE id = $info[id]")
    or(die(mysql_error()));

Try changing it to:

mysql_query("UPDATE TABLE SET date = '$newdate' WHERE id = {$info['id']}")
    or (die(mysql_error()));

The reason is that when interpolating array indices into a string, you must surround them with {} or PHP will just try to convert $info to a string and insert [id] after it, which I'm guessing you didn't intend.

I would also suggest checking the return value for strtotime. If it can't parse a date it returns false which I'm guessing you don't want inserted back into your database.

answered Aug 21 '14 at 15:52

amphetamachine

27,620
12
60
72

1

and think about using `pdo` or `mysqli_*` function instead of the deprecated mysql_query. and then you should use prepare statements to prevent from sql injections besides other nice improvments (e.g. speed - prepare once, update many) – Rufinus Aug 21 '14 at 16:17
@Rufinus I don't think prepared statements will do anything to improve security here. It seems he's running a one-off script to "fix" the data in the date column using PHP. Still, I agree, it's a good habit to get into. – amphetamachine Aug 21 '14 at 16:22
@amphetamachine right. this is why i mentioned the speed gain. if he prepare the statement once and execute only with the different values. – Rufinus Aug 21 '14 at 16:25
@Rufinus Reusing prepared statements won't increase speed over straight querying; the same amount of SQL gets sent over the wire regardless. – amphetamachine Aug 21 '14 at 18:35
@amphetamachine not really, you spare the mysql server to parse the same query over and over again. see http://stackoverflow.com/questions/11389449/performance-of-mysql-insert-statements-in-java-batch-mode-prepared-statements-v – Rufinus Aug 22 '14 at 07:43

PHP strtotime returns empty value

1 Answers1