PHP + MYSQL：插入一个PHP数组到mysql [英] php+mysql: insert a php array into mysql

问题描述

我有30000加条目需要进入一个MySQL表的数组。

什么是最好的做法？从这里？比方说，[0]，[1] [2]在数据库中是'称号，类型和客户

时将其添加匹配表和呼叫索姆神奇的功能列名键名？或手动构建查询...

 阵列
（
    [0] =＆GT;排列
        （
            [0] =＆GT; 2140395946
            [1] =＆GT; 1SAP
            [2] =＆GT; 0041451463
        ）    [1] =＆GT;排列
        （
            [0] =＆GT; 2140411607
            [1] =＆GT; 2SAP
            [2] =＆GT; 0041411940
        ）    [2] =＆GT;排列
        （
            [0] =＆GT; 2140706194
            [1] =＆GT; 4SAP
            [2] =＆GT; 0041411943
        ）
等等，等等
 

更新 - 基于答案

感谢您的答案。

解决方案通常会手动创建SQL字符串和所有行可插入为一个：

  INSERT INTO`tx_opengate_table`（`machine`，`customer`，`type`）
VALUES
  （'M123'，'dfkj45'，'A'），
  （'M137'，'kfkj49'，'A'），重复此线为阵列中的每个条目
  ... ... ...
  （m654321'，'34dgf456'，'C4'）结束与出逗号或删除最后一个逗号
;
 

特供TYPO3

我刚好做了CMS TYPO3，只是遇到了一个新的功能添加不是很久以前：

  //插入新行
$表='tx_opengate_stuff';
$栏=阵列（'机'，'型'，'顾客'）;
$线=数组如上所述
$ GLOBALS ['TYPO3_DB']  - ＆GT; exec_INSERTmultipleRows（$表，$领域，$线）;
 

解决方案

我想说的只是建立它自己。您可以设置它是这样的：

  $查询=INSERT INTO X（A，B，C）值;
的foreach（$改编为$项目）{
  。$查询=（'。$项目[0]'，'。$项目[1]'，'。$项目[2]），;
}
$查询= RTRIM（$查询，）; //删除多余的逗号
//执行查询
 

不要忘了逃脱的报价，如果有必要。

此外，要小心，有没有被发送一次数据太多。你可能有块，而不是一次全部执行它。

I have an array with 30000 plus entries that need to go into a MySQL table.

What is the best practice? From here? Lets say [0], [1] and [2] in the database would be 'title', 'type' and 'customer'

Is it add key names matching the column names in the table and call som "magic" function? Or build the query manually...

Array
(
    [0] => Array
        (
            [0] => 2140395946
            [1] => 1SAP
            [2] => 0041451463
        )

    [1] => Array
        (
            [0] => 2140411607
            [1] => 2SAP
            [2] => 0041411940
        )

    [2] => Array
        (
            [0] => 2140706194
            [1] => 4SAP
            [2] => 0041411943
        )
etc. etc.

UPDATE - based on answers

Thanks for the answers.

The solution would normally be to manually create the SQL-string and all rows can be inserted as one:

INSERT INTO `tx_opengate_table` (`machine` ,`customer` ,`type`)
VALUES
  ('m123', 'dfkj45', 'A'),
  ('m137', 'kfkj49', 'A'), "repeat this line for each entry in the array"
  ... ... ...
  ('m654321', '34dgf456', 'C4') "end with out comma, or remove last comma"
;

Special for TYPO3

I happen to do this in the CMS TYPO3 and just came across a new function added not that long ago:

//Insert new rows
$table = 'tx_opengate_stuff';
$fields = array('machine','type','customer');
$lines = "array as given above"
$GLOBALS['TYPO3_DB']->exec_INSERTmultipleRows($table,$fields,$lines);

解决方案

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

这篇关于PHP + MYSQL：插入一个PHP数组到mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！