Datetime NOW PHP mysql (+ PDO variant)

Question

Thanks for looking. All helpful answers/comments are up voted.

In php, you can use NOW() like this:

mysql_query("INSERT INTO tablename (id,      value,      time_created) 
                            VALUES ('{$id}', '{$value}', NOW())");

How can I do the same thing in PDO. When I bind like this, I get an error:

$stmt->bindParam(':time_added', NOW(), PDO::PARAM_STR);

Is it the PDO:PARAM_STR?

Answer 1

Just do this:

mysql_query("INSERT INTO tablename (id, value, time_created)  
             VALUES ('$id', '$value', NOW())");

Although you have to have the right column-type. date or time alone, won't work if I recall correctly; it has to be datetime or maybe timestamp?

Answer 2

other than NOW() i also utilize the "timestamp" type column and set its default to CURRENT_TIMESTAMP .. so i just pass nothing for that field and time is automatically set. maybe not exactly what ur looking for.

Answer 3

Because nobody has explicitly answered the question (although the correct answer can be extracted from the comments to Ollie Saunders's answer), I'll add the correct answer for the sake of completeness.

$stmt = $pdoDb->prepare('INSERT INTO tablename (id, value, time_created) VALUES (:id, :value, NOW())');
// either bind each parameter explicitly 
$stmt->bindParam(':id', $id); // PDOStatement::bindValue() is also possibly
$stmt->bindParam(':value', $value);
$stmt->execute();
// or bind when executing the statement
$stmt->execute(array(
    ':id'    => $id,
    ':value' => $value
));

Answer 4

WTF? none of the answers solve the question as I see it!

So there are some of my findings: there is NO WAY how to force PDO to pass MySQL function call as a query value - so there is no way to do simple wrapper that will be able to use NOW() or any other function as passed values. Every time you need something like that, you need manulay change the query, so the function call is part of the query string. :-(

I'm using funcion that tests given values for MySQL function I am using and modifies the query itself, but it is not a good solution to my opinion... :-}

Answer 5

this maybe be usefull to some of you maybe not but I was confronted with the same problem as Ollie Saunders was. I'm pretty new to php/mysql an most of all PDO. What I found working for me is the following:

$active = 0;      
$id = NULL;
$query = "INSERT 
        INTO tbl_user(ID_user, firstname, lastname, email, password, active, create_date)
        VALUES (?,?,?,?,?,?,NOW())";

if($stmt=$this->conn->prepare($query)) {
$stmt->bind_param('issssi', $id, $firstname, $lastname, $email, $password, $active);
$stmt->execute();
}

and guess what it works! Hope to have helped here. Any comments are welcome. try it and tell me if it worked for you and if you have any additions.