Change date format for array to MySQL format using PDO

Question

My array date format ($fanniemaeDate) that I am passing to the database table column (date) is 11/19/2013 and I am trying to convert it to the MySQL format 2013-11-19 in my PDO prepare statement, however it is giving me an error "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

I'm just learning PDO so not sure what is wrong with my STR_TO_DATE format within the prepare statement, or if this is even the correct way to prepare the statement.

If I remove the STR_TO_DATE from the prepare statement it will pass the time and rate correctly with the date stored as 0000-00-00

I need to know the correct way to write the prepare statement.

//PDO insert prepare statement for fannie_90dayrate table
$insertIrate = $pdo->prepare("INSERT INTO fannie_90dayrate (date, time, 90dayrate) 
VALUES (STR_TO_DATE(':fanniemaeDate', '%m/%d/%Y'), :fanniemaeTime, :fanniemaeRate)");  

//PDO execute statement
$insertIrate->execute(array(
':fanniemaeDate' => $fanniemaeDate['Fannie Mae Date'],
':fanniemaeTime' => $fanniemaeTime['Fannie Mae Time'],
':fanniemaeRate' => $fanniemaeRate['Fannie Mae Rate']));

Phil · Accepted Answer · 2013-11-27 22:16:42Z

1

Remove the quotes around :fanniemaeDate in

... STR_TO_DATE(':fanniemaeDate', '%m/%d/%Y') ...

Placing quotes around a parameter placeholder will cause it to be ignored, thus unbalancing your number of placeholders / number of parameters.

Side note...

I would recommend working with actual DateTime objects in PHP instead of date strings. That way, you can switch formats around quite easily, for example

$fanniemaeDate['Fannie Mae Date'] = DateTime::createFromFormat('m/d/Y', '11/19/2013');

// snip

$insertIrate->execute(array(
    ':fanniemaeDate' => $fanniemaeDate['Fannie Mae Date']->format('Y-m-d'),
    // etc

This way, you avoid having to convert the date string in the query.

edited Nov 27, 2013 at 22:16

answered Nov 27, 2013 at 22:11

Phil

166k25 gold badges265 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Change date format for array to MySQL format using PDO

1 Answer 1

Side note...

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Side note...

Comments

Your Answer

Sign up or log in

Post as a guest

Related