pdo mysql bindparam,如何使用PDO和bindParam将数组插入mysql？

I'm using the following code. The code works, but I want to change it so that it uses bindparam

try {

$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);

$stqid=array();

for ($i=0; $i

$stqid[$i][0]=$lastInsertValue;

$stqid[$i][1]=$qid[$i][0];

$stqid[$i][2]=$qid[$i][1];

}

$values = array();

foreach ($stqid as $rowValues) {

foreach ($rowValues as $key => $rowValue) {

$rowValues[$key] = $rowValues[$key];

}

$values[] = "(" . implode(', ', $rowValues) . ")";

}

$count = $dbh->exec("INSERT INTO qresults(instance, qid, result) VALUES ".implode (', ', $values));

$dbh = null;

}

catch(PDOException $e){

echo $e->getMessage();

}

I replaced the following

$count = $dbh->exec("INSERT INTO qresults(instance, qid, result) VALUES ".implode (', ', $values));

with

$sql = "INSERT INTO qresults (instance, qid, result) VALUES (:an_array)";

$stmt = $dbh->prepare($sql);

$stmt->bindParam(':an_array', implode(',', $values),PDO::PARAM_STR);

$stmt->execute();

but the insert doesn't work anymore (I didn't get any error messages though).

QUESTION: What am I doing wrong? How can I rewrite the code to use bindParam?

解决方案

You're trying to create a statement and bind a param.

Statement are great because it potentially nullify any kind of SQL injection. And it does it by removing the concept of a query being only seen as a string. The SQL query is seen as a string with a parameter list and an the associated data as binded variables.

So the query is not only text, but text + data.

I mean:

This simple query:

SELECT * FROM A WHERE val="$param"

It is not safe because the query is only viewed as a string. And if $param is not checked, it is a SQLi hole.

But when create a statement, your query becomes:

SELECT * FROM A WHERE val=:param

Then you use bindparam to specify the value a :param. Which mean the value is not appended to the query string, but the query is already parsed and the data is provided.

In your case, you bind to the param :array an imploded array (I assume "data1", "data2", etc..). Which is only one parameter with the value as a string ( "data1, data2, data3..." ), so it will only result in one insert and not multiple insertions.

You can change your statement generation by generating a query with enough parameters to handle your array

$sql = "INSERT INTO qresults (instance, qid, result) VALUES ( :val0, :val1, :val2, ...)";

Then loop on your array and call the bindparam method for each parameters.

$count = 0;

foreach($values as $val)

{

$stmt->bindParam(":val$count", $val,PDO::PARAM_STR);

$count++;

}

This will work.

Edit: This solution show how it works for a one dimensional array, but can be easily extended to your problem by tweaking the statement query generation and modify the bindparam loop.

Your statement should looks like:

$sql = "INSERT INTO qresults (instance, qid, result) VALUES (:val0, :val1, :val2) , (:val3, :val4, :val5), ...";

You just have to count the number of element in your base array.