PHP如何从关联数组中提取键名称和值以进行mysql查询 [英] PHP how to extract keys names and values from associative array for mysql query
问题描述
您好，我已经尝试了这两个问题的解决方案（最终目标添加在底部）
但是我没有在$ fields和$ newdata
所以我请在这里打印一些var_dump，并强制要求您的支持。
我的数组是从html表派生的
为简化学习实验，我使用的是只有5个字段的伪表，如您所见，它们是：已选择， user_id ， user_name ， user_company 和 user_email 。
最后我只插入了两行值。
表内容发布为JSON.stringify。
这里是我的结果
使用常规
print_r（$ Arr）;
我可以看到此输出
Array（
[0] => Array（[selected] => [user_id] => 3 [user_name] => nome3 [user_company] => azien3 [ user_email] => email3）
[1] =>阵列（[selected] => 1 [user_id] => 6 [user_name] => nome6 [user_company] => azien6 [user_email] => email6）
）
接下来，我尝试应用两者的代码上面的问题
24 $ fields = implode（，，array_keys（$ Arr））;
25 $ newdata =’。 implode（’，’，$ Arr）。 ’；
26
27 var_dump（$ fields）;
28 echo< br>< br>;
29 var_dump（$ newdata）;
但是在我的解释或代码中有些错误，因为输出是
注意：第25行的D：\xampp\htdocs\ajax-json\post.php中的数组到字符串的转换
注意：在第25行的D：xampp\htdocs\ajax-json\post.php中将数组转换为字符串
string（3） 0,1
string（ 15）'Array'，'Array'
您能指出什么错吗？
例如我的数组格式正确吗？
最终目标是建立一个查询，在其中将绑定数组中的键名和键值直接绑定到列的值插入到mysql表中。
换句话说，由于数组的键名与数据库表的列名相同，所以我想知道如何使自动查询生成两个查询中的查询在此问题的开头。
具有自动性的意思是要具有变量，并且可能循环以建立查询INSTEAD而不是编写单列名称，并且对于
编辑：从接受的答案中，这是工作代码。
$ my_keys = array_keys（$ Arr [0]）;
// ----这样可以防止PDO SQL注入
$ 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）; //构建列列表
/ ** @JBH需要此foreach，否则$ q_markers不会像PDO占位符那样产生。
如果缺少此查询，则查询将插入所有值，无论您采用bindValue还是bindParam ** /
foreach（$ my_keys as& $ key）{
$ key =：。$ key;
}
$ q_markers = implode（，，$ my_keys）; //建立PDO值标记
$ stmt = $ pdo-> prepare（ INSERT INTO my_table（。$ field_names。）VALUES（。$ q_markers。））;;
foreach（$ Arr作为$ key => gt; $ val）{
foreach（$ val作为$ bind_marker =>& $ bind_val）{/ ** @ JBH不带&在这里，它将只使用
bindValue。而是使用&，它们都可以使用bindParam和bindValue ** /
$ stmt-> bindParam（$ bind_marker，$ bind_val）;
}
$ stmt-> execute（）;
}
您可以内嵌关联数组，但是您无法内插多维数组。这就是错误告诉您的内容。例如...
$ 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;
结果是...
a，b，c
1,2,3
但是...
$ 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;
导致...
0,1
PHP注意：/test.php在第9行
$ b进行数组到字符串的转换
$ b
修复代码意味着处理单个数据元素。回声他们看起来像这样：
selected，user_id，user_name，user_company，user_email
，3 ，nome3，azien3，email3
1,6，nome6，azien6，email6
基本代码看起来像是...
$ fields = implode（，，array_keys（$ Arr））;
echo $ fields。 \n;
foreach（$ Arr as $ key => $ val）{
$ newdata =’。 implode（’，’，$ Arr [$ key]）。 ’；
echo $ newdata。 \n;
}
这样会生成PDO INSERT语句...
$ 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）; //构建列列表
$ q_markers = implode（，，$ my_keys）; //建立PDO值标记
$ 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（）;
}
请注意带有白名单的代码部分变量。该代码的目的是防止由于使用未绑定列引用创建查询而导致SQL注入。 PDO不允许您像处理单元格数据一样来绑定列名。为了保护自己，您必须证明输入的数据与表中的列匹配。如果他们不这样做，请对此做一些处理（ echo ERROR; ）。通常，您希望完全停止该INSERT操作并将问题记录在某个地方。
$ 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）; //构建列列表
$ 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）;
}
上面的代码是使用不带的PDO的示例bindParam 或 bindValue 。它带有价格，尽管通常没有实际成本。 bindParam 和 bindValue 允许您专门标识数据类型。例如， bindParam（‘myval’，$ myval，PDO :: PARAM_INT）。如上传递变量后，您将无法执行此操作。在大多数情况下，这不是问题，因为PHP可以正确识别数据类型。当PHP确实变得混乱时（或者如果您只是想检查数据是否符合您的期望），则必须使用 bindParam 或 bindValue 。
Hello I've tried both these questions solutions (final goal added at bottom)
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();
}
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.
这篇关于PHP如何从关联数组中提取键名称和值以进行mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
