PDO array INSERT

Question

So the arrays are coming out how I want them Any example of the arrays, which may vary in size and have things like ' < \ / in them below

$TablesNames
Array 
( 
   [0] => SampleDate 
   [1] => LAB 
) 

$LineResults
Array 
( 
  [0] => 4/08/2014 
  [1] => Micro - Water 
)

Abstract of the code where I'm trying to insert the values

$sqlTableNames = (implode(',',$TableNames));

for ($x=0; $x<$Xsize; $x++) 
{
  for($y=0;$y<$MapSize;$y++)
  {     
        $LineResults[$x][$y] = $results[$x][$map[$y]];          
  }
$sqlLineResults = (implode("','",$LineResults[$x]));

$ResultsInsert = $db->prepare("INSERT INTO samples (:TableValues) VALUES (:LineValues)");   
$ResultsInsert->bindParam(':TableValues', $sqlTableNames, PDO::PARAM_STR);
$ResultsInsert->bindParam(':LineValues', $sqlLineResults, PDO::PARAM_INT);  
$ResultsInsert->execute();

}

Throws the error "...the right syntax to use near '?) VALUES (?)' at line 1' .."

score 1 · Accepted Answer · answered Oct 09 '14 at 22:21

1

There are two problems here:

You cannot bind table- or column names, only values;
You can only bind single values, not a string of multiple values.

So both sides of the VALUES clause are wrong.

You will need to build your sql statement dynamically, adding the key - value pairs individually (or the strings on both sides). As you cannot bind table- and column names, you should use a white-list in case the input comes from the visitor.

answered Oct 09 '14 at 22:21

jeroen

91,079
21
114
132

$sqlquery = "INSERT INTO samples ($sqlTableName) VALUES ('$sqlLineResults')"; mysql_query($sqlquery); Why does PDO have to be so much harder – hounded Oct 09 '14 at 22:44
@swifty It is basically the same, but you have to bind the individual variables instead of `$sqlquery` at once. That doesn't mean `$sqlquery` cannot contain a list of fields like `:var1, :var2, etc.`. As long as you bind them individually afterwards. – jeroen Oct 09 '14 at 22:51
my problem is that the length of my array is not defined and I as i'm new to PDO I can't think of how I would dynamical bind them – hounded Oct 09 '14 at 23:10
@swifty You can send an array with key - value pairs to `execute()` instead of binding each value in a separate line. That array you can fill / build in your loop. See http://php.net/manual/en/pdostatement.execute.php – jeroen Oct 10 '14 at 03:00
1

Yeah that has worked well thanks ! PDO getting easier – hounded Oct 14 '14 at 03:16

PDO array INSERT

1 Answers1