mysql - PDO PHP insert into DB from an associative array

Question

I have an array like this

  $a = array( 'phone' => 111111111, 'image' => "sadasdasd43eadasdad" );

When I do a var-dump I get this ->

 { ["phone"]=> int(111111111) ["image"]=> string(19) "sadasdasd43eadasdad" }

Now I am trying to add this to the DB using the IN statement -

 $q = $DBH->prepare("INSERT INTO user :column_string VALUES :value_string");
 $q->bindParam(':column_string',implode(',',array_keys($a)));
 $q->bindParam(':value_string',implode(',',array_values($a)));
 $q->execute();

The problem I am having is that implode return a string. But the 'phone' column is an integer in the database and also the array is storing it as an integer. Hence I am getting the SQL error as my final query look like this --

INSERT INTO user 'phone,image' values '111111111,sadasdasd43eadasdad';

Which is a wrong query. Is there any way around it.

My column names are dynamic based what the user wants to insert. So I cannot use the placeholders like :phone and :image as I may not always get a values for those two columns. Please let me know if there is a way around this. otherwise I will have to define multiple functions each type of update.

Thanks.

Answer 1

Last time I checked, it was not possible to prepare a statement where the affected columns were unknown at preparation time - but that thing seems to work - maybe your database system is more forgiving than those I am using (mainly postgres)

What is clearly wrong is the implode() statement, as each variable should be handled by it self, you also need parenthesis around the field list in the insert statement.

To insert user defined fields, I think you have to do something like this (at least that how I do it);

$fields=array_keys($a); // here you have to trust your field names! 
$values=array_values($a);
$fieldlist=implode(',',$fields); 
$qs=str_repeat("?,",count($fields)-1);
$sql="insert into user($fieldlist) values(${qs}?)";
$q=$DBH->prepare($sql);
$q->execute($values);

If you cannot trust the field names in $a, you have to do something like

foreach($a as $f=>$v){
   if(validfield($f)){
      $fields[]=$f;
      $values[]=$v;
   }
}

Where validfields is a function that you write that tests each fieldname and checks if it is valid (quick and dirty by making an associative array $valfields=array('name'=>1,'email'=>1, 'phone'=>1 ... and then checking for the value of $valfields[$f], or (as I would prefer) by fetching the field names from the server)