dongnaoben4456
dongnaoben4456
2018-06-22 09:19

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

已采纳

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.
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dou9022 dou9022 3年前

    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();
    }
    
    点赞 评论 复制链接分享
  • dongxue7306 dongxue7306 3年前

    The problem you have is that in your code, you've got the following:

    $colList[] = "`".$colName."`";
    

    You are then checking for:

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

    When adding to the array, you're putting ticks around the column name, which you aren't searching for during the in_array part.

    Either change the $colList addition to be:

    $colList[] = $colName;
    

    or the array search to be:

    if(in_array("`id`", $colList))
    
    点赞 评论 复制链接分享
  • doushou8299 doushou8299 3年前

    Try replacing this part of your script :

    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>
    ";
    }
    

    with :

    $query .= "ON DUPLICATE KEY UPDATE
    \t ";
    $tmp = array();
    foreach($colList as $idx => $colName)
    {
        //$tmp[] = $colName." = new.".$colName." ";
        $tmp[] = $colName." = VALUES(".$colName.") ";    //  Changed this line to get value from current insert row data
    }
    $query .= implode(",",$tmp)."
    ";
    
    点赞 评论 复制链接分享