PDO PHP 从关联数组插入数据库 [英] PDO PHP insert into DB from an associative array

问题描述

我有一个这样的数组

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

当我执行 var-dump 时，我得到了这个 ->

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

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

现在我正在尝试使用 IN 语句将其添加到数据库中 -

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();

我遇到的问题是内爆返回一个字符串.但是 'phone' 列是数据库中的一个整数，数组也将它存储为一个整数.因此，我收到 SQL 错误，因为我的最终查询看起来像这样 --

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.

我的列名是动态的，基于用户想要插入的内容.所以我不能使用像 :phone 和 :image 这样的占位符，因为我可能并不总是得到这两列的值.请让我知道是否有办法解决这个问题.否则我将不得不为每种类型的更新定义多个函数.

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.

谢谢.

推荐答案

上次我检查时，在准备时无法准备受影响的列未知的语句 - 但那个东西似乎有效 - 也许你的数据库系统比我使用的系统更宽容(主要是 postgres)

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)

明显错误的是 implode() 语句，因为每个变量都应该由它自己处理，您还需要在插入语句中的字段列表周围加上括号.

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);

如果您不能信任 $a 中的字段名称，则必须执行类似操作

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;
   }
}

其中validfields 是您编写的一个函数，用于测试每个字段名并检查它是否有效(通过创建关联数组快速而肮脏地 $valf​​ields=array('name'=>1,'email'=>1, 'phone'=>1 ... 然后检查 $valf​​ields[$f] 的值，或者(如我所愿)通过从服务器获取字段名称)

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)

这篇关于PDO PHP 从关联数组插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！