使用PHP数组和for循环合并Insert Into表

Question

我有一个php数组，来自一个数据库。这个数组我想插入到不同的db/table中。两个字段(WEBINV_ID、主机名)是唯一的。我会每天更新这些表的每个脚本。如果该条目存在，则必须跳过插入并使用update。这工作得很好。但是如果我在php for循环中运行它，它只会将数组的第一个条目插入或更新到我使用的新表中。

MERGE INTO TABLE ...

就为了这个。这是我的输入数组：

[0] => Array
    (
        [CI_ID] => 39778
        [NODEALIAS] => rt-2
        [NODE] => 10.1.2.3
        [SERIALNUMBER] => 8374378584
        [VENDORNAME] => Cisco
        [STATUS] => active
    )

[1] => Array
    (
        [CI_ID] => 72909
        [NODEALIAS] => rt-1
        [NODE] => 10.1.1.3
        [SERIALNUMBER] => 1276731237
        [VENDORNAME] => Cisco
        [STATUS] => active
    )...

这是我的for循环，我尝试在其中插入或更新我所有的设备。

for ($i = 0; $i < count($router); $i++) {

    $sql = "MERGE INTO DEVICES USING ( SELECT "
            . "'" . $router[$i]['CI_ID'] . "' AS WEBINV_ID,"
            . "'" . $router[$i]['NODEALIAS'] . "' AS DEVICE_NAME,"
            . "'" . $router[$i]['NODE'] . "' AS NODE,"
            . "'" . $router[$i]['SERIALNUMBER'] . "' AS SERIALNUMBER,"
            . "'" . $router[$i]['VENDORNAME'] . "' AS VENDORNAME,"
            . "'" . $router[$i]['STATUS'] . "' AS STATUS,"
            . "CURRENT_TIMESTAMP AS DEVICE_INSERT FROM DUAL
    ) S ON ('" . $router[$i]['CI_ID'] . "' = S.WEBINV_ID
         OR '" . $router[$i]['NODEALIAS'] . "' = S.DEVICE_NAME)
    WHEN MATCHED THEN UPDATE SET STATUS = '" . $router[$i]['STATUS'] . "', DEVICE_UPDATE = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN INSERT(WEBINV_ID,DEVICE_NAME,NODE,SERIALNUMBER,VENDORNAME,STATUS,DEVICE_INSERT)
    VALUES ('" . $router[$i]['CI_ID'] . "',"
            . "'" . $router[$i]['NODEALIAS'] . "',"
            . "'" . $router[$i]['NODE'] . "',"
            . "'" . $router[$i]['SERIALNUMBER'] . "',"
            . "'" . $router[$i]['VENDORNAME'] . "',"
            . "'" . $router[$i]['STATUS'] . "',"
            . "CURRENT_TIMESTAMP)";

    $stid = oci_parse($gnedb, $sql);
    oci_execute($stid);  // executes and commits
}

我用"oci_bind_by_name“尝试了相同的代码，但结果是相同的，只使用了数组中的第一个条目。有什么想法吗？

Answer 1

如果你用的是oci_bind_by_name，我知道你用的是Oracle数据库。

也许你应该这样做：

$insert_query = "insert all ";
foreach($router as $record) {
  $fields = "";
  $values = "";
  foreach($record as $field => $value) {
    $fields .= $field . ($field != 'STATUS' ? ',' : ""); // this is only if in the array the last field is STATUS - if it is not you might try to figure out last column diferently or remove last ',' differently
    $values .= "'" . $value . "'" . ($field != 'STATUS' ? ',' : ""); // this is only if in the array the last field is STATUS - if it is not you might try to figure out last column diferently or remove last ',' differently
  }
  $insert_query .= " into devices (" . $fields . ") values (" . $values . ") ";
}

上面的代码会在$insert_query中创建一个查询，你只需要把它发送到数据库中。您需要弄清楚哪些字段应该加引号，哪些不应该加引号。我引用了所有字段，因为Oracle应该能够正确地转换值-但您需要仔细检查(我没有可用的Oracle来检查:)

我使用的SQL构造可以在here中找到。

Answer 2

我已经找到了一个解决方案，我只会添加我的工作代码，这篇文章已经完成了，我希望这对其他人有帮助。代码如下：

for ($i = 0; $i < count($router); $i++) {
    $query = "MERGE INTO CORE_INTERFACES USING dual ON "
      // The next row is the match criteria, like If "HOSTNAME" AND "IFINDEX" exist, Then Update else INSERT NEW HOSTNAMES!   
      . "( HOSTNAME = '" . $hostname . "' AND IFINDEX = '" .    t trim($router_interface[0]) . "')" 
      . "WHEN MATCHED THEN UPDATE SET "
      . "IFNAME = '" . trim($router_interface[1]) . "',IFTYPE = '" . trim(clean_iftype($router_interface[2])) . "',IFADMINSTATUS = '" . trim(clean_output($router_interface[3])) . "',"
      . "LAST_UPDATE = CURRENT_TIMESTAMP "
. "WHEN NOT MATCHED THEN INSERT ("
      . "IF_ID,HOSTNAME,IFINDEX,IFNAME,IFTYPE,IFADMINSTATUS,"
      . "FIRST_SEEN "
      . ") VALUES ("
      . " core_interfaces_seq.nextval, "
      . "'" . $hostname . "',"        //$HOSTNAME
      . "'" . trim($router_interface[0]) . "',"       //ifIndex
      . "'" . trim($router_interface[1]) . "',"       //ifName
      . "'" . trim(clean_iftype($router_interface[2])) . "',"         //ifType
      . "'" . trim(clean_output($router_interface[3])) . "',"         //ifAdminState
      . " CURRENT_TIMESTAMP )";
   $stid = oci_parse($gnedb, $sql);
}