通过PDO将数据插入MySQL，尝试使用数组包含时间戳和数据 - 获取错误？

Question

I have a set of data stored in variables, and I want a page to write that data to a MySQL database, I'd like to include the time of insertion, here's my method:

$username="username"; $password="password";


try {
  $pdo = new PDO('mysql:host=localhost; dbname=db01', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $stmt = $pdo->prepare('INSERT INTO table01
  (
   Time,
   variable1,
   variable2,
  )

VALUES
  (
    :Time,
    :variable1,
    :variable2,
  )');


  $stmt->execute(array(
        ':Time' => NOW(),
        ':variable1' => $var1,
        ':variable2' => $var2,
  ));

  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

On loading this page, the css is stripped from the page, I get a sort of garbled output of what the page should look like (white screen, plain text some images), additionally on checking the database nothing has been written to it.

Without the Time variable in there, all works perfectly.

Any advice? Thanks in advance

Answer 1

Sorry, took me moment to re-read that. You are using the nysql function now() to do the work, and as such you don't need to set it as a param and therefore don't need to bind it at all. Just write it into your query.

  $stmt = $pdo->prepare('INSERT INTO table01
  (
   Time,
   variable1,
   variable2,
  )

VALUES
  (
    now(),
    :variable1,
    :variable2,
  )');

  $stmt->execute(array(
    ':variable1' => $var1,
    ':variable2' => $var2,
  ));

Edit Re Comment

The : in a query denotes it as a parameter in a prepared query. That means you must then bind it via the bind() command. However if you are inserting the data from within the database (such as using a built in function, or pulling the data in from another row) you don't need to declare it in the initial query with : and therefore you can't pass it to the query in the bound params array.