CODEWITHSUNDEEP
phpmysqlsqlsql-insert
Neeraj
Neeraj

Reputation: 58

mysql inserting different value

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

Upvotes: 0

Views: 69

Answers (1)

John Woo
John Woo

Reputation: 263703

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.

Upvotes: 1

Related Questions