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

问题描述

我有一个这样的数组

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

当我做了变种转储我得到这个 - >

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

我遇到的问题是，破灭返回一个字符串。但'电话'列是在数据库中的整数，还阵列被存储为一个整数。因此，我收到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.

我的列名是动态的，基于用户要插入的东西。所以我不能使用像占位符：电话和：图片，因为我并不总是得到一个值的两列。请让我知道是否有办法解决。否则我将不得不定义多种功能，每种类型的更新。

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.

感谢。

推荐答案

我最后一次检查，这是不可能的，以prepare其中受影响的列都在preparation时间未知声明 - 但事情似乎工作 - 也许你的数据库系统是比我使用（主要是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)

什么是明显错误是破灭（）语句，每个变量应该由它自身来处理，还需要围绕在INSERT语句中的字段列表括号。

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

在哪里validfields是一个函数，你写的测试每个字段名并检查它是否是有效的（快速和肮脏通过关联数组$ valfields =阵列（'名'=> 1，电子邮件=> 1手机'=> 1 ...然后检查的$ valfields [$ F]，或值（我想preFER）通过从服务器获取的字段名）

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屋！