I was doing a simple INSERT during my development, but I'm going back and putting in SQL protection and prepared statements. The error I receive is:
Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
Here is the code that I'm using from help from others on SO.
// foreach to set up variables
foreach ($json as $text) {
$uoid = mysql_real_escape_string($uoid);
$filename = mysql_real_escape_string($uoid.".jpg");
$filedate = mysql_real_escape_string($datetime);
$imagedesc = mysql_real_escape_string($desc);
// array of values
$insert[] = array($uoid,$filename,$filedate,$imagedesc);
}
function placeholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}
return implode($separator, $result);
}
foreach($insert as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
}
$pdo = new PDO('mysql:dbname=photo_gallery;host=127.0.0.1', 'myuser', 'mypass');
$pdo->beginTransaction();
$sql = "INSERT INTO wp_gallery (" . implode(',', array_values($insert) ) . ")
VALUES " . implode(',', $question_marks);
// reading output
echo $sql;
$stmt = $pdo->prepare($sql);
try {
$stmt->execute($insert[0]);
} catch (PDOException $e){
echo $e->getMessage();
}
$pdo->commit();
When I look at the SQL output:
INSERT INTO wp_gallery (10219776,10219776.jpg,my image description,2012-08-01 15:36:29)
VALUES (?,?,?,?),(?,?,?,?),(?,?,?,?),(?,?,?,?)
Everything matches just by the look of it, but I'm still baffled as to how to fix this. Can someone point out what I'm doing wrong?
You're passing values as column names, and you're not passing the correct parameter to execute;
Something like this would be more correct;
// Replace the $insert buildup. Array of values, you don't want an array of arrays here.
$insert = array_merge($insert, array($uoid,$filename,$filedate,$imagedesc));
...
$column_names = array("column1", "column2", "column3", "column4");
$sql = "INSERT INTO wp_gallery (" . implode(',', $column_names ) . ")
VALUES " . implode(',', $question_marks);
$stmt->execute($insert);
The INSERT statement looks like this:
INSERT INTO name_of_the_table (name_of_column_1, name_of_column_2,...) VALUES
(value_of_column_1, value_of_column_2), (value_of_column_1, value_of_column_2);
If you set the values of all columns in the order they are defined, you can omit the names of columns.
You however put the values where column names should be and question marks, where values should go.