How can PDO do this query?

Question

$what = "(999,'string','string2'),(21,'aaaa','bbbbb'),(22,'ccccc','ddddd')";
include("../connect/pdoconnect.php");
try {

$sql = $db->prepare("INSERT INTO `bd_fortest` (`user_ID`, `picturename`, `picturepath`) VALUE :what");
$sql->execute(array(":what"=>$what));


}
catch(PDOException $e) {  
    echo "\nPDO::errorInfo():\n";
    print_r($db->errorInfo());
}

$what is value from get-data-from-csv-function so we could not know how many it is. I try by myself, PDO can't do this query.What query should I do ?

Place-holders are used to insert *values*, so they can be properly separated from code. If you use them to insert *code* PDO will *not* handle it as code because that's exactly what it's supposed to do. — Álvaro González, Aug 22 '13 at 10:28
If you are generating $what from array somewhere, then PDO prepared statements should be the target of that array loop. — Cups, Aug 22 '13 at 10:30

Barmar · Accepted Answer · 2013-08-22T10:49:51.073

You can't do this with a single placeholder. A placeholder is only allowed where an ordinary expression is allowed, and the list of values is not a single expression.

So you need to use either string interpolation/concatenation, as in Gautam3164's answer, or use a loop where you insert a single row at a time, e.g.

$whats = array(array('userid' => 999, 'picturename' => 'string', 'picturepath' => 'string2'),
               array('userid' => 21, 'picturename' => 'aaaa', 'picturepath' => 'bbbbb'),
               array('userid' => 22, 'picturename' => 'ccccc', 'picturepath' => 'ddddd')
              );
$sql = $db->prepare("INSERT INTO `bd_fortest` (`user_ID`, `picturename`, `picturepath`) VALUES (:userid, :picturename, :picturepath)");
foreach ($whats as $what) {
    $sql->execute($what);
}

If you use string concatenation, you lose the protection of parametrized statements. You need to ensure that all the strings are properly sanitized or escaped.

Hmmm I try and It shows Fatal error: Call to a member function prepare() on a non-object. — crazyoxygen, Aug 22 '13 at 10:49
That means you didn't set `$db` properly, and is a totally different problem. — Barmar, Aug 22 '13 at 10:50

How can PDO do this query?

1 Answers1