insert error in mysql using PDO

Question

I am experiencing problems while inserting date type value in mysql using PDO

code snippet :

 $HOST = 'localhost';
 $DATABASE = 'db';
 $USERNAME = 'XXXXXX';
 $PASSWORD = 'XXXXXX';

 $DBH = new PDO("mysql:host=$HOST;dbname=$DATABASE;charset=utf8",$USERNAME,$PASSWORD);
 $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  

 $incomingdate  = date('Y-m-d', time());  // this echoes 2013-07-03
 $name = "XYZ";

 try {

  $stmt = $DBH->query('INSERT INTO tablename (date ,name ) VALUES (?, ?)');
  $stmt->execute(array($incomingdate, $name  ));
  $row_count = $stmt->rowCount();
  echo $row_count.' rows selected';

     }
 catch(PDOException $e ){
    echo 'Error in executing query ...';
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
    }

Datatype at the mysql database side of date column is : DATE

Error : Below is the contents of PDOErrors.txt SQLSTATE[HY000]: General error: 2031

What is wrong with this query....earlier query ran smooth when i used mysql_* functions ... so what is wrong with prepared statements ??? And also .... what should be done if datatype is of Timestamp type and / or datetime type at the mysql end

possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) — Strawberry, Jul 03 '13 at 09:34

score 3 · Answer 1 · answered Jul 03 '13 at 09:34

3

You need to use prepare() for a prepared statement, not query():

$stmt = $DBH->prepare('INSERT INTO tablename (date ,name ) VALUES (?, ?)');

PDO::query() can't have bound parameters, only a statement created through PDO::prepare() can.

answered Jul 03 '13 at 09:34

MrCode

63,975
10
90
112

score 1 · Accepted Answer · answered Jul 03 '13 at 09:35

1

Use backticks around date column name and use ->prepare instead of ->query

$stmt = $DBH->prepare('INSERT INTO tablename (`date` ,`name` ) VALUES (?, ?)');

answered Jul 03 '13 at 09:35

Yogesh Suthar

30,424
18
72
100

2

Atleast say reason for downvoting? – Yogesh Suthar Jul 03 '13 at 09:39
1

Not my downvote but probably because `date` is not a reserved word in MySQL. Refer to the docs for a list of reserved words `MySQL permits some keywords [e.g date] to be used as unquoted identifiers because many people previously used them. ` http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html – MrCode Jul 03 '13 at 09:47
@MrCode Thanks, I got it, previously most of people get error because of that. – Yogesh Suthar Jul 03 '13 at 09:51
Nobody never get an error from that. Your answer is wrong as usual. – Your Common Sense Jul 03 '13 at 15:21

insert error in mysql using PDO

2 Answers2