php上的重复更新不起作用

b09cbbtk  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(402)

我正在使用c#、php和mysql开发超市应用程序。本地和联机都有相同模式的数据库(超过25个表)。下面的php脚本用于从json导入记录( app_sample.json )将本地数据和联机数据同步到联机数据库。json文件包含经过筛选的记录 added_on 以及 last_updated 按字段 DATETIME 价值观。这个 id 是关键领域。json文件包含新的id记录和旧的id记录(它们是更新的字段记录)。当我运行这个php脚本来插入在线数据库时,我得到了以下错误,并且记录没有更新或插入。

php脚本

<?php
    try
    {
        $connect = mysqli_connect("localhost", "fine", "password", "fine_dbsync"); 
        $query = '';
        $table_data = '';
        $filename = "app_sample.json";

        $data = file_get_contents($filename);
        $array = json_decode($data, true); 

        foreach($array as $set) 
        {
            if(sizeof($set['rows']) > 0) 
            {
                $query = '';
                $tblName = $set['tableName'];
                $colList = array();
                $valList = array();
                //  Get list of column names
                foreach($set['rows'][0] as $colName => $dataval) 
                {
                    $colList[] = "`".$colName."`";
                }
                $query .= "INSERT INTO `".$tblName."` ";
                $query .= "(".implode(",",$colList).") VALUES ";
                //  Go through the rows for this table.
                foreach($set['rows'] as $idx => $row) 
                {
                    $colDataA = array();
                    //  Get the data values for this row.
                    foreach($row as $colName => $colData) 
                    {
                        $colDataA[] = "'".$colData."'";
                    }
                    $valList[] = "(".implode(",",$colDataA).")";
                }
                //  Add values to the query.
                $query .= implode(",",$valList);

                //  If id column present, add ON DUPLICATE KEY UPDATE clause
                if(in_array("id", $colList)) 
                {
                    $query .= " ON DUPLICATE KEY UPDATE ";
                    $tmp = array();
                    foreach($colList as $idx => $colName) 
                    {
                        $tmp[] = $colName." = new.".$colName." ";
                    }
                    $query .= implode(",",$tmp);
                }
                echo "<p>Insert query:<pre>$query</pre></p>";
                mysqli_query($connect, $query);  
                echo "<h1>Rows appended in $tblName</h1>";
            } 
            else 
            {
                echo "<p>No rows to insert for $tblName</p>";
            }
        }
    } 

    catch(Exception $e)
    {   
        echo $e->getMessage();  
    }
?>

包含表记录的json文件的一部分

[
  {
    "tableName": "cashdrawer_open_time",
    "rows": []
  },
  {
    "tableName": "counters",
    "rows": [
      {
        "id": "2",
        "name": "B",
        "description": "SAKTHY",
        "added_on": "2018-06-21T12:49:30",
        "last_updated": "2018-02-18T12:49:40",
        "department": "1"
      },
      {
        "id": "5",
        "name": "E",
        "description": "SAKTHY2",
        "added_on": "2018-06-21T12:50:21",
        "last_updated": "2018-06-21T14:52:18",
        "department": "1"
      }
    ]
  }
]

完整json文件
表创建架构

nbewdwxp

nbewdwxp1#

使用new.column name引用insert语句中提供的值是不正确的。改用value()函数:

<?php
    try
    {
        $connect = mysqli_connect("localhost", "fine", "password", "fine_dbsync"); 
        $query = '';
        $table_data = '';
        $filename = "app_sample.json";

        $data = file_get_contents($filename);
        $array = json_decode($data, true); 

        foreach($array as $set) 
        {
            if(sizeof($set['rows']) > 0) 
            {
                $query = '';
                $tblName = $set['tableName'];
                $colList = array();
                $valList = array();
                //  Get list of column names
                foreach($set['rows'][0] as $colName => $dataval) 
                {
                    $colList[] = "`".$colName."`";
                }
                $query .= "INSERT INTO `".$tblName."` ";
                $query .= "(".implode(",",$colList).") VALUES ";
                //  Go through the rows for this table.
                foreach($set['rows'] as $idx => $row) 
                {
                    $colDataA = array();
                    //  Get the data values for this row.
                    foreach($row as $colName => $colData) 
                    {
                        $colDataA[] = "'".$colData."'";
                    }
                    $valList[] = "(".implode(",",$colDataA).")";
                }
                //  Add values to the query.
                $query .= implode(",",$valList);

                //  If id column present, add ON DUPLICATE KEY UPDATE clause
                if(in_array("id", $colList)) 
                {
                    $query .= " ON DUPLICATE KEY UPDATE ";
                    $tmp = array();
                    foreach($colList as $idx => $colName) 
                    {
                        $tmp[] = $colName." = VALUE(".$colName.") ";    //  Changed this line to get value from current insert row data
                    }
                    $query .= implode(",",$tmp);
                }
                echo "<p>Insert query:<pre>".$query."</pre></p>";
                mysqli_query($connect, $query);  
                echo "<h1>Rows appended in  ".$tblName."</h1>";
            } 
            else 
            {
                echo "<p>No rows to insert for ".$tblName."</p>";
            }
        }
    } 

    catch(Exception $e)
    {   
        echo $e->getMessage();  
    }
?>

相关问题