PHP-MySQL准备插入语句以插入数组 [英] PHP - MySQL prepared statement to INSERT an array

问题描述

我正在编辑使用MySQLi的脚本.我需要使用准备好的语句在数据库中插入一些值.

I'm editing a script that is using MySQLi. I need to use prepared statement to insert some values into the db.

我的数组的形式为:

$insert = array('column1' => 'value1', 'column2' => 'value2', 'column3' => 'value3')

到目前为止，我已经有了这个，但是我需要bind_param部分的帮助.我在这里看到了使用call_user_func_array的文档，但不确定如何实现.

I have this so far but I need help with the bind_param portion. I've seen documentation on here where call_user_func_array is used but I'm not sure how to implement this.

$cols = array_keys($insert);
$query = "INSERT IGNORE INTO results (". implode(", ", $cols) .") VALUES (". implode(', ', array_fill(0, count($insert), '?')) .")";
$stmt = $mysqli->prepare($query);
$param = array_merge(array(str_repeat('s', count($insert))), array_values($insert)); 
call_user_func_array(array($stmt, 'bind_param'), $param); 
$stmt->execute();

PHP 5.4.17

PHP 5.4.17

推荐答案

否...绝对比使用任何数组的PDO难，因为

No... this was definitely harder than PDO with any array because of how mysqli_stmt_bind_param() works... and this works fine by changing $array to removing/adding data for other columns.

$mysqli = new mysqli('localhost', 'root', 'password', 'test');

$array  = array("name"=>"pineapple", "color"=>"purple"); 

$table_name = "fruit"; 



insert_data($mysqli, $array, $table_name);



function insert_data($mysqli, $array, $table_name) 
{
   $placeholders = array_fill(0, count($array), '?');

   $keys   = array(); 
   $values = array();
   foreach($array as $k => $v) {
      $keys[] = $k;
      $values[] = !empty($v) ? $v : null;
   }

   $query = "insert into $table_name ".
            '('.implode(', ', $keys).') values '.
            '('.implode(', ', $placeholders).'); '; 
   // insert into fruit (name, color) values (?, ?);    

   $stmt = $mysqli->prepare($query);

   // create a by reference array... 
   $params = array(); 
   foreach ($array as &$value) { 
      $params[] = &$value;
   }
   $types  = array(str_repeat('s', count($params))); 
   $values = array_merge($types, $params); 

   /*           
   $values = Array
      (
          [0] => ss
          [1] => pineapple
          [2] => purple
      ) 
   */

   call_user_func_array(array($stmt, 'bind_param'), $values); 

   $success = $stmt->execute();

   if ($success) { print "it worked..."; } 
           else { print "it did not work..."; }
}  

我从这些SO帖子中得到了一些帮助:
- https://stackoverflow.com/a/15933696/623952
- https://stackoverflow.com/a/6179049/623952

I got some help from these SO posts:
- https://stackoverflow.com/a/15933696/623952
- https://stackoverflow.com/a/6179049/623952

因此...在$stmt->bind_param()中，第一个参数是一个字符串，每个传入的参数都有一个字符.该char表示参数数据类型.在上面的示例中，两个参数都是字符串，因此它变为ss.在上面的示例中，始终也假定使用字符串.

So... in $stmt->bind_param() the first parameter is a string that has one char for each parameter passed in. And that char represents the parameter data type. In the example above, both of the two parameters are strings so it becomes ss. A string is always assumed in the example above, too.

我在bind_param()文档中找到了此图表:

I found this chart in the bind_param() documentation:

类型
一个包含一个或多个字符的字符串，这些字符指定相应绑定变量的类型:

types
A string that contains one or more characters which specify the types for the corresponding bind variables:

Type specification chars  

Character    Description  
i            corresponding variable has type integer
d            corresponding variable has type double
s            corresponding variable has type string
b            corresponding variable is a blob and will be sent in packets

这篇关于PHP-MySQL准备插入语句以插入数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！