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

Question

Hello I've tried both these questions solutions (final goal added at bottom)

INSERT array - PDO

Binding values from arrays?

but I don't get the expected variables content in $fields and $newdata

so I kindly print here some var_dump and cast to kindly ask your support.

My array derivate from an html table

For simplicity in my learning experiment I'm working with a dummy table of just 5 fields, as you see they are: selected, user_id, user_name, user_company and user_email. Finally I have inserted just 2 rows of values.

The table content is posted as JSON.stringify.

Here you my results

Using the usual

print_r ( $Arr );

I can see this 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 I try to apply the code of from the two 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 something is wrong in my interpretation or in my code , because the output is

Notice: Array to string conversion in D:\xampp\htdocs\ajax-json\post.php on line 25

Notice: Array to string conversion in D:\xampp\htdocs\ajax-json\post.php on line 25
string(3) "0,1"

string(15) "'Array','Array'"

can you kindly point out what's wrong? e.g. is my array properly formed?

the final goal is to build a query where they are bind the keys names and key values taken from the associative array directly to columns and values for an INSERT into a mysql table.

In other words since the array's keys names are identical to the database table's columns names, I'm wondering how to make an automatism that creates the query like in the two questions in the opening of this question.

With "automatism" is meant to HAVE variables and maybe cycles to build a query INSTEAD than writing the single columns names and the same for the columns values to be inserted

---

Edit: from the accepted answer, this is the working code.

$my_keys = array_keys($Arr[0]);

// ---- This prevents PDO SQL Injection
$stmt=$pdo->prepare("DESC my_table");
$stmt->execute();
$whitelist_columns=$stmt->fetchAll(PDO::FETCH_COLUMN);
foreach($my_keys as $key){
    if(!array_search($key,$whitelist_columns)){ echo "ERROR!"; }
}
// ---- End of prevention

$field_names = implode(",", $my_keys);   // build column list

/** @JBH this foreach is needed otherwise the $q_markers will result not PDO placeholders like.
If this is missing, the query inserts all "" values, no matter if you'll adopt bindValue or bindParam**/
foreach($my_keys as &$key){
    $key = ":".$key; 
}
$q_markers = implode(",", $my_keys);     // build PDO value markers

$stmt = $pdo->prepare("INSERT INTO my_table (".$field_names.") VALUES (".$q_markers.")");
foreach($Arr as $key => $val){
    foreach($val as $bind_marker => &$bind_val){   /** @ JBH Without "&" here, it will work 
only bindValue. Instead with "&", they work both bindParam and bindValue **/
        $stmt->bindParam($bind_marker, $bind_val);  
    }
    $stmt->execute();
}

Answer 1

You can implode an associative array, but you cannot implode a multi-dimensional array. That's what the error is telling you. For 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 to string conversion in /test.php on line 9

Fixing your code means dealing with the individual data elements. Echo'd out they'd look like this:

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

So, the basic code would look something like...

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

And a PDO INSERT statement would be built like 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 as $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 INTO my_table (".$field_names.") VALUES (".$q_markers.")");
foreach($Arr as $key => $val){
    foreach($val as $bind_marker => $bind_val){
        $stmt->bindParam($bind_marker, $bind_val);
    }
    $stmt->execute();
}

Note the section of code with the whitelist variables. The purpose of that code is to protect against SQL injection due to creating the query with unbound column references. PDO does not allow you to bind column names in the same way it does cell data. To protect yourself you must prove that the incoming data matches the columns in the table. If they don't, do something about it (echo "ERROR";). Usually you want to stop that INSERT completely and log the 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 as $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 INTO my_table (".$field_names.") VALUES (".$field_markers.")");

foreach($Arr as $key => $val){
    $stmt->execute($val);
}

The above code is an example of using PDO without bindParam or bindValue. It comes with a price, though usually there's no actual cost. bindParam and bindValue allow you to specifically identify the data type. E.G., bindParam('myval', $myval, PDO::PARAM_INT). When variables are passed as above, you can't do this. Most of the time this is a non-issue as PHP correctly identifies the data type. When PHP does become confused (or if you simply want to impose a check that the data is what you were expecting), then you must use bindParam or bindValue.