PHP how to extract keys names and values from associative array for mysql query -

hello i've tried both these questions solutions (final goal added @ bottom)

insert array - pdo

binding values arrays?

but don't expected variables content in $fields , $newdata

so kindly print here var_dump , cast kindly ask support.

my array derivate html table

for simplicity in learning experiment i'm working dummy table of 5 fields, see are: selected, user_id, user_name, user_company , user_email. have inserted 2 rows of values.

the table content posted json.stringify.

here results

using usual

print_r ( $arr ); 

i can see output

array (  [0] => array ( [selected] => [user_id] => 3 [user_name] => nome3 [user_company] => azien3 [user_email] => email3 )  [1] => array ( [selected] => 1 [user_id] => 6 [user_name] => nome6 [user_company] => azien6 [user_email] => email6 )  ) 

next try apply code of 2 above questions

24  $fields = implode(",", array_keys($arr)); 25  $newdata = "'" . implode("','", $arr) . "'"; 26   27  var_dump($fields); 28  echo "<br><br>"; 29  var_dump($newdata); 

but wrong in interpretation or in code , because output is

notice: array string conversion in d:\xampp\htdocs\ajax-json\post.php on line 25  notice: array string conversion in d:\xampp\htdocs\ajax-json\post.php on line 25 string(3) "0,1"  string(15) "'array','array'"

can kindly point out what's wrong? e.g. array formed?

the final goal build query bind keys names , key values taken associative array directly columns , values insert mysql table.

in other words since array's keys names identical database table's columns names, i'm wondering how make automatism creates query in 2 questions in opening of question.

with "automatism" meant have variables , maybe cycles build query instead writing single columns names , same columns values inserted

---

edit: accepted answer, working code.

$my_keys = array_keys($arr[0]);  // ---- prevents pdo sql injection $stmt=$pdo->prepare("desc my_table"); $stmt->execute(); $whitelist_columns=$stmt->fetchall(pdo::fetch_column); foreach($my_keys $key){     if(!array_search($key,$whitelist_columns)){ echo "error!"; } } // ---- end of prevention  $field_names = implode(",", $my_keys);   // build column list  /** @jbh foreach needed otherwise $q_markers result not pdo placeholders like. if missing, query inserts "" values, no matter if you'll adopt bindvalue or bindparam**/ foreach($my_keys &$key){     $key = ":".$key;  } $q_markers = implode(",", $my_keys);     // build pdo value markers  $stmt = $pdo->prepare("insert my_table (".$field_names.") values (".$q_markers.")"); foreach($arr $key => $val){     foreach($val $bind_marker => &$bind_val){   /** @ jbh without "&" here, work  bindvalue. instead "&", work both bindparam , bindvalue **/         $stmt->bindparam($bind_marker, $bind_val);       }     $stmt->execute(); } 

you can implode associative array, cannot implode multi-dimensional array. that's error telling you. example...

$my_array = array('a'=>'1', 'b'=>'2', 'c'=>'3'); echo "\n\n".implode(',',array_keys($my_array)); echo "\n\n".implode(',',$my_array)."\n\n"; 

results in...

a,b,c  1,2,3 

but...

$my_array = array(     array('a'=>'1', 'b'=>'2', 'c'=>'3'),     array('d'=>'4', 'e'=>'5', 'f'=>'6') ); echo "\n\n".implode(',',array_keys($my_array)); echo "\n\n".implode(',',$my_array)."\n\n"; 

results in...

0,1 php notice:  array string conversion in /test.php on line 9 

fixing code means dealing individual data elements. echo'd out they'd this:

selected, user_id, user_name, user_company, user_email ,3,nome3,azien3,email3 1,6,nome6,azien6,email6 

so, basic code like...

$fields = implode(",", array_keys($arr)); echo $fields."\n"; foreach($arr $key=>$val){     $newdata = "'" . implode("','", $arr[$key]) . "'";     echo $newdata."\n"; } 

and pdo insert statement built this...

$my_keys = array_keys($arr[0]);  $stmt=$pdo->prepare("desc my_table"); $stmt->execute(); $whitelist_columns=$stmt->fetchall(pdo::fetch_column); foreach($my_keys $key){     if(!array_search($key,$whitelist_columns)){ echo "error!"; } }  $field_names = implode(",", $my_keys);       // build column list $q_markers = implode(",", $my_keys);     // build pdo value markers  $stmt = $pdo->prepare("insert my_table (".$field_names.") values (".$q_markers.")"); foreach($arr $key => $val){     foreach($val $bind_marker => $bind_val){         $stmt->bindparam($bind_marker, $bind_val);     }     $stmt->execute(); } 

note section of code whitelist variables. purpose of code protect against sql injection due creating query unbound column references. pdo not allow bind column names in same way cell data. protect must prove incoming data matches columns in table. if don't, (echo "error";). want stop insert , log issue somewhere.

$my_keys = array_keys($arr[0]); $q_marks = array();  $stmt=$pdo->prepare("desc my_table"); $stmt->execute(); $whitelist_columns=$stmt->fetchall(pdo::fetch_column); foreach($my_keys $key){     if(!array_search($key,$whitelist_columns)){ echo "error!"; }     array_push($q_marks, "?"); }  $field_names = implode(",", $my_keys);       // build column list $field_markers = implode(",", $q_marks);  $stmt = $pdo->prepare("insert my_table (".$field_names.") values (".$field_markers.")");  foreach($arr $key => $val){     $stmt->execute($val); } 

the above code example of using pdo without bindparam or bindvalue. comes price, though there's no actual cost. bindparam , bindvalue allow identify data type. e.g., bindparam('myval', $myval, pdo::param_int). when variables passed above, can't this. of time non-issue php correctly identifies data type. when php become confused (or if want impose check data expecting), must use bindparam or bindvalue.