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

Because nobody has explicitly answered the question, 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 2

Presuming your PDO statement is correct you could do something like this:

$date = date('Y-m-d H:i:s');
$stmt->bindParam(':time_added', $date, PDO::PARAM_STR);

Answer 3

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 manually change the query, so the function call is part of the query string. :-(

I'm using function 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 4

This might be useful to some of you, maybe not. I was confronted with the same problem as Ollie Saunders was. I'm pretty new to php/mysql, and most of all PDO. I was able to solve the problem with 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, or if you have any additions.

Answer 5

To answer Elmo's question, you can create a PDO wrapper that allows for SQL functions like NOW(). You just need to pass an additional argument with the columns that you want to use SQL functions for. Here's mine:

function pInsertFunc($action, $table, $values, $sqlfunctions)
{

    global $pdb;

    // There's no way to pass an SQL function like "NOW()" as a PDO parameter,
    // so this function builds the query string with those functions.  $values
    // and $sqlfunctions should be key => value arrays, with column names
    // as keys.  The $values values will be passed in as parameters, and the
    // $sqlfunction values will be made part of the query string.

    $value_columns = array_keys($values);
    $sqlfunc_columns = array_keys($sqlfunctions);
    $columns = array_merge($value_columns, $sqlfunc_columns);

    // Only $values become ':paramname' PDO parameters.
    $value_parameters = array_map(function($col) {return (':' . $col);}, $value_columns);
    // SQL functions go straight in as strings.
    $sqlfunc_parameters = array_values($sqlfunctions);
    $parameters = array_merge($value_parameters, $sqlfunc_parameters);

    $column_list = join(', ', $columns);
    $parameter_list = join(', ', $parameters);

    $query = "$action $table ($column_list) VALUES ($parameter_list)";

    $stmt = $pdb->prepare($query);
    $stmt->execute($values);

}

Use it like this:

$values = array(
    'ID' => NULL,
    'name' => $username,
    'address' => $address,
);

$sqlfuncs = array(
    'date' => 'NOW()',
);

pInsertFunc("INSERT INTO", "addresses", $values, $sqlfuncs);

The query string that results looks like this:

INSERT INTO addresses (ID, name, address, date) VALUES (:ID, :name, :address, NOW())