March 14, 2012

Why php divides date instead of saving it as string?

Question by robert

I have a MySQL database table that has a VARCHAR field storing a date in dd/mm/yyyy format.

I have a situation where I need to extract that date to a variable and then save it in a different table (which has a different structure than the first one).

So I do this:

$result=mysql_query("select * from shares") or die(mysql_error());
$date_old=$row['last_update'];    //date in dd/mm/yyyy format

Then when I try to insert $date_old into the other table, instead of inserting the date, PHP actually performs a mathematical division operation on it! So instead of saving 14/03/2012, it divides 14 by 3 and then by 2012 and stores 0.0023 in the database.

Surely there is a way to stop this from happening using a built-in function (without removing the slashes from the string and then putting them back in before insertion)?

Answer by Karoly Horvath

When doing insert put the values in single quotes: " ... last_update = '$date' ... ", otherwise mysql will do the calculations.

Note: for storing dates use the date type.

Answer by Starx

That is because, it turns out into an expression 14/03/2012

Escape the values using quotes.

$query = "INSERT INTO anothertable (data) VALUES('$olddate');";

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+


Please fill the form - I will response as fast as I can!