php ON DUPLICATE KEY UPDATE也说1062：关键'PRIMARY'的重复输入

Question

There is a database is online. The database has the same schema in local and online with more than 25 tables. I am sending the INSERT or UPDATE records by JSON file. The id is the key field for all tables. The JSON file may contains new id records which to be inserted and old id fields which to be updated the entire online table fields.

The following is the counters table in online.

+-------+---------+---------------+---------------------+---------------------+------------+
| id    | name    | description   | added_on            | last_updated        | department |
+-------+---------+---------------+---------------------+---------------------+------------+
| 1     | A       | Bill          | 2018-02-18 21:48:28 | 2018-02-18 15:08:34 | 1          |
| 2     | B       | SAKTHY        | 2018-06-21 12:49:30 | 2018-02-18 12:49:40 | 1          |
| 3     | C       |               | 2018-02-18 21:48:28 | 2018-02-18 21:48:28 | 1          |
+-------+---------+---------------+---------------------+---------------------+------------+

The following data is passed by JSON file to online database.

[
  {
    "tableName": "bank_accounts",
    "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"
      },
      {
        "id": "6",
        "name": "SAKTHY3",
        "description": "Sample Friday",
        "added_on": "2018-06-22T10:47:18",
        "last_updated": "2018-06-22T10:47:18",
        "department": "1"
      }
    ]
  },
  {
    "tableName": "customers",
    "rows": []
  }
]

To INSERT or UPDATE the records to the online database, this php script is used (thanks @Sloan Thrasher, @lovepreet-singh).

<?php
    try
    {
        $connect = mysqli_connect("localhost", "username", "password", "database"); 
        $query = '';
        $table_data = '';
        $filename = "sample.json";

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

        foreach($array as $set) 
        {
            $tblName = $set['tableName'];
            if(sizeof($set['rows']) > 0) 
            {
                $query = '';
                $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
\t SET ";
                    $tmp = array();
                    foreach($colList as $idx => $colName) 
                    {
                        //$tmp[] = $colName." = new.".$colName." ";
                        $tmp[] = $colName." = VALUE(".$colName.") ";    //  Changed this line to get value from current insert row data
                    }
                    $query .= implode(",",$tmp)."
";
                } 
                else 
                {
                    echo "<p><b>`id`</b> column not found. <i>ON DUPLICATE KEY UPDATE</i> clause <b>NOT</b> added.</p>
";
                    echo "<p>Columns Found:<pre>".print_r($colList, true)."</pre></p>
";
                }
                echo "<p>Insert query:<pre>$query</pre></p>";
                $r = mysqli_query($connect, $query);  

                echo mysqli_errno($connect) . ": " . mysqli_error($connect) . "
";

                echo "<h1>".mysqli_affected_rows($connect). " Rows appended in .$tblName.</h1>";
            } 
            else 
            {
                echo "<p>No rows to insert for .$tblName.</p>";
            }
        }
    } 

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

But I got the following SQL echo's in the browser. In this case the online database does not updated or inserted new records.

No rows to insert for .bank_accounts.

`id` column not found. ON DUPLICATE KEY UPDATE clause NOT added.

Columns Found:

Array
(
    [0] => `id`
    [1] => `name`
    [2] => `description`
    [3] => `added_on`
    [4] => `last_updated`
    [5] => `department`
)
Insert query:

INSERT INTO `counters` 
(`id`,`name`,`description`,`added_on`,`last_updated`,`department`)
VALUES
('2','B','SAKTHY','2018-06-21T12:49:30','2018-02-18T12:49:40','1'),
('5','E','SAKTHY2','2018-06-21T12:50:21','2018-06-21T14:52:18','1'),
('6','SAKTHY3','Sample Friday','2018-06-22T10:47:18','2018-06-22T10:47:18','1')

1062: Duplicate entry '2' for key 'PRIMARY'

-1 Rows appended in .counters.

No rows to insert for .customers.

Answer 1

Your problem is with the check

if(in_array("id", $colList))

change it to

if(in_array("`id`", $colList))

Also change

$tmp[] = $colName." = VALUE(".$colName.") ";

to

$tmp[] = "{$colName} = {$colName}";

EDIT: Below you will find my version of the code. Hope this helps:

<?php
try
{
    $connect  = mysqli_connect("localhost", "username", "password", "database"); 
    $filename = "sample.json";
    $dataSets = json_decode(file_get_contents($filename), true);

    if (is_null($dataSets)) {
        throw new Exception(json_last_error_msg());
    }

    foreach($dataSets as $dataSet)
    {
        $tblName     = $dataSet['tableName'];
        $dataSetRows = $dataSet['rows'];

        if (!$dataSetRows) {
            echo "<p>No rows to insert for . $tblName . </p>";
            continue;
        }

        foreach($dataSetRows as $dataSetRow){

            $colList = array_keys($dataSetRow);
            $valList = array_values($dataSetRow);

            $query = "INSERT INTO {$tblName} (" . implode(",", $colList) . ") VALUES (\"" . implode('","', $valList) . '")';

            if(in_array("id", $colList)) {

                $query .= " ON DUPLICATE KEY UPDATE ";

                array_walk($dataSetRow, function($val, $col) use (&$query){
                    if($col !=='id') {
                        $query .= "{$col} = \"{$val}\",";
                    };
                });
            }

            $query = rtrim($query, ',');

            echo "<p>Insert query:<pre>$query</pre></p>";

            mysqli_query($connect, $query);

            echo mysqli_errno($connect) . ": " . mysqli_error($connect) . "
";

            echo "<h1>".mysqli_affected_rows($connect). " Rows appended in .$tblName.</h1>";
        }
    }
} catch(Exception $e) {
    echo $e->getMessage();
}