INSERT statement with 'now() + INTERVAL' inserts 000's in PDO with named placeholders

Question

I'm having trouble converting this now() + INTERVAL INSERT statement to a PDO prepared statement with named placeholders.

When I bind the value using either 'now() + INTERVAL 3 DAY' or 'DATE_ADD(now(), INTERVAL 3 DAY)', it inserts 000's instead of the correct datetime (0000-00-00 00:00:00)

This is what I was previously using:

$qry = "INSERT INTO password_reset(user_id, temp_password, expiry_date) 
VALUES('$member_user_id','$temp_password', now() + INTERVAL 3 DAY)";

New PDO Statement:

$stmt = $conn->prepare('INSERT INTO password_reset (user_id, temp_password, expiry_date) 
VALUES(:user_id, :temp_password, :expiry_date)');          
$stmt->bindValue(':user_id', $member_user_id); 
$stmt->bindValue(':temp_password', $random_password); 
$stmt->bindValue(':expiry_date', 'now() + INTERVAL 3 DAY');  
$insertResult = $stmt->execute();

I've also tried this:

$stmt->bindValue(':expiry_date', 'DATE_ADD(now(), INTERVAL 3 DAY)');

Alternate method proposed in several SO postings

Several SO postings (including this link) suggested putting the now() statement in the VALUES instead of binding it, but that causes an error message 'Invalid parameter number: number of bound variables does not match number of tokens'

$stmt = $conn->prepare('INSERT INTO password_reset (user_id, temp_password, expiry_date) 
VALUES(:user_id, :temp_password, :now() + INTERVAL 3 DAY)');
$stmt->bindValue(':user_id', $member_user_id); 
$stmt->bindValue(':temp_password', $random_password); 
$insertResult = $stmt->execute();

Take the `:` off of `:now() + INTERVAL 3 DAY`. You should also be using `bindParam` rather than `bindValue`. — crush, Jan 29 '13 at 16:48
If it clearly says `'Invalid parameter number: number of bound variables does not match number of tokens'` - why not to count number of colons? — Your Common Sense, Jan 29 '13 at 16:51
@crush - That generated the error message "Parse error: syntax error, unexpected ':', expecting ')' in /home4/clickfi4/public_html/demo/forgotpass-exec.php on line 42" — Chaya Cooper, Jan 29 '13 at 16:52
@YourCommonSense - Can you elaborate? I've been under the impression that the number of variables in the INSERT, VALUE, and bindValue statements should be the same, so I was assuming that the problem with this method is that they're mismatched in number. — Chaya Cooper, Jan 29 '13 at 16:56
There is a difference between a bound variable, and a variable. A bound variable is bound with the `bindValue` or `bindParam` methods. — crush, Jan 29 '13 at 16:58
@crush - My apologies for not using the proper terminology :-) — Chaya Cooper, Jan 29 '13 at 17:09
@ChayaCooper It's not about using the proper terminology. There should be three variables in the `VALUES()` section of the `INSERT` because that is how many columns there are. However, only two of those columns are `bound` columns with the `bindValue` method. Since you initially had 3 bound columns, but only were assigning values to two of those bound columns, you were receiving the error Your Common Sense showed. — crush, Jan 29 '13 at 17:12
Thanks for clarifying :-) That's what I had been trying to express, but you explained it far clearer than I know how to — Chaya Cooper, Jan 29 '13 at 17:27
@crush - The part that I hadn't known is that the number of variables and bound variables don't have to match (is that the correct way to express it? Or should I be referring to the number of columns?) Out of curiosity, are there specific types/instances that don't need to be bound? — Chaya Cooper, Jan 29 '13 at 17:33
I'll append my answer with some more information to hopefully explain a little better what is going on =] — crush, Jan 29 '13 at 17:57

Your Common Sense · Answer 1 · 2013-01-29T17:15:24.623

2

You have a colon in front of :now()
That's it. Just a typo.

Your second error message clearly says that you didn't actually deleted the colon but removed something else - most likely a quote. You need to be more attentive in writing. And pay more attention to the error messages, they are quite informative.

edited Jan 29 '13 at 17:15

answered Jan 29 '13 at 16:49

Your Common Sense

156,878
40
214
345

Removing the colon generated this error "Parse error: syntax error, unexpected ':', expecting ')'" – Chaya Cooper Jan 29 '13 at 17:02
It was very weird - I'd definitely removed the colon (I triple checked) and was getting that error message, but when I copied and pasted @crush's complete code (instead of just the line that needed to be changed) it worked! I wish I understood why that happens sometimes, but I'm glad that it's working now and that logic prevailed once again :-) – Chaya Cooper Jan 29 '13 at 17:23

crush · Accepted Answer · 2013-01-29T18:16:40.973

Remove the colon from :now() + INTERVAL 3 DAY.

$stmt = $conn->prepare('INSERT INTO password_reset (user_id, temp_password, expiry_date) 
VALUES(:user_id, :temp_password, now() + INTERVAL 3 DAY)');
$stmt->bindValue(':user_id', $member_user_id); 
$stmt->bindValue(':temp_password', $random_password); 
$insertResult = $stmt->execute();

Alternatively, you could do this:

$stmt = $conn->prepare('
    INSERT INTO
        password_reset (user_id, temp_password, expiry_date) 
    VALUES
        (:user_id, :temp_password, now() + INTERVAL 3 DAY)
');
$insertResult = $stmt->execute(array(
    ":user_id" => $member_user_id,
    ":temp_password" => $random_password)
);

When working with Prepared Statements, you should think of the statement as being a template to plug data into.

If you have a database table with the structure:

user_id           INT
temp_password     VARCHAR(128)
expiry_date       DATE

You have three columns in your table. Two of the values to insert into these columns will come from PHP, and the other will be evaluated by SQL. Since SQL will be doing all the work on the expiry_date column, it doesn't need to be bound to anything in PHP.

So, examining the INSERT statement:

INSERT INTO password_reset (user_id, temp_password, expiry_date) VALUES (:user_id, :temp_password, now() + INTERVAL 3 DAY)

Each parameter of the VALUES() clause needs to match up to a declared column name in the INSERT INTO table (columns...) clause.

In prepared statements, since we are creating a template, we use placeholders to show where we will be inserting values into the statement as it is executed. Placeholders can be either the :name version that you have used, or simply a question mark ?.

PDO::prepare($statement) tells the SQL server to prepare the statement. At that point, SQL has the template for your query, and is ready to receive the values for the placeholders in that query.

PDOStatement::execute($placeholderValues) executes a single instance of that prepared statement, substituting the placeholders with the values you have either bound with bindParam, bindValue, or passed as the argument to execute().

So, basically, all that is being sent to the SQL server on each execute() are the values to plug into the placeholders, instead of an entire query string.

Now comes the part that explains why you weren't able to bindValue(":expiry_date", "now() + INTERVAL 3 DAY").

When the values get to the SQL server, SQL server sanitizes them and replaces the respective placeholder with their value.

When you bind "now() + INTERVAL 3 DAY", you are actually binding a string. Since it is a string, it doesn't get executed as SQL code.

No one should use `bindParam` instead of `bindValue`. You got something mixed up there. If you're suggesting to use one function instead of another, please provide reasons why you believe it's better to do so. — N.B., Jan 29 '13 at 16:54
You're right. I confused bindValue with bindColumn at first. bindParam(): `Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.` — crush, Jan 29 '13 at 16:55
Removing the colon generated this error "Parse error: syntax error, unexpected ':', expecting ')'". I'll try your 2nd suggestion now... — Chaya Cooper, Jan 29 '13 at 17:02
I just created a table mimicking yours, and ran a test, and it worked properly for me. Please double check that you have the same code as I have above. — crush, Jan 29 '13 at 17:08
It's so weird - My code was identical to yours (I'd copied the line of code from your posting) and wasn't working, but when I copied and pasted your complete code it worked! Thank you so much for being so patient and helping me solve that odd challenge :-) — Chaya Cooper, Jan 29 '13 at 17:18
Hope this helps explain a bit better about what is going on. I'm also curious why your initial attempt didn't work - it could've been some weird non-printable characters or something that got transferred on your first copy/paste. — crush, Jan 29 '13 at 18:22

INSERT statement with 'now() + INTERVAL' inserts 000's in PDO with named placeholders

2 Answers2