mysql inserting different value

Question

$insert = mysql_query("
    INSERT into analytics.pagesummary (Date, avg_load_time, min_load_time, max_load_time) 
    VALUES ($date,$string_avg, $string_min,$string_max)" ) 
    or die('PROBLEM'.mysql_error());

Actual date values = '2013-02-07', '2013-02-08', '2013-02-09' Date values in the database after the query is fired = '2004', '2003', '2002'

Other values being displayed of avg_load_time, min_load_time & max_load_time are correct.

Where are we going wrong. Any help would be appreciated.

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — j0k
– j0k, Commented Feb 9, 2013 at 8:18

Community · Accepted Answer · 2017-05-23 12:27:02Z

1

because the values were not wrap in a single quote and mysql implicitly cast the values silently, it should be

INSERT INTO analytics.pagesummary (Date, avg_load_time, min_load_time, max_load_time) 
VALUES ('$date', '$string_avg', '$string_min', '$string_max')

The reason why 2013-02-07 when inserted becomes 2004 is because it performs arithmethic operation such that

2013 - 02 = 2011 - 07 = 2004
2013 - 02 = 2011 - 08 = 2003
2013 - 02 = 2011 - 09 = 2002

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Feb 9, 2013 at 7:25

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Collectives™ on Stack Overflow

mysql inserting different value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related