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
– user3316501, Commented Aug 21, 2014 at 15:38

amphetamachine · Accepted Answer · 2014-08-21 15:52:16Z

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, 2014 at 15:52

amphetamachine

31k12 gold badges68 silver badges74 bronze badges

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

5 Comments

Rufinus Over a year ago

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)

amphetamachine Over a year ago

@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.

Rufinus Over a year ago

@amphetamachine right. this is why i mentioned the speed gain. if he prepare the statement once and execute only with the different values.

amphetamachine Over a year ago

@Rufinus Reusing prepared statements won't increase speed over straight querying; the same amount of SQL gets sent over the wire regardless.

Rufinus Over a year ago

@amphetamachine not really, you spare the mysql server to parse the same query over and over again. see stackoverflow.com/questions/11389449/…

Collectives™ on Stack Overflow

PHP strtotime returns empty value

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related