php ON DUPLICATE KEY UPDATE also says 1062: Duplicate entry for key '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."` \n";
                $query .= "(".implode(",",$colList).")\nVALUES\n";
                //  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(",\n",$valList)."\n";

                //  If id column present, add ON DUPLICATE KEY UPDATE clause
                if(in_array("id", $colList)) 
                {
                    $query .= "ON DUPLICATE KEY UPDATE\n\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)."\n";
                } 
                else 
                {
                    echo "<p><b>`id`</b> column not found. <i>ON DUPLICATE KEY UPDATE</i> clause <b>NOT</b> added.</p>\n";
                    echo "<p>Columns Found:<pre>".print_r($colList, true)."</pre></p>\n";
                }
                echo "<p>Insert query:<pre>$query</pre></p>";
                $r = mysqli_query($connect, $query);  

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

                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.

Possible duplicate of [Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'](https://stackoverflow.com/questions/14628269/error-code-1062-duplicate-entry-1-for-key-primary) — svet, Jun 22 '18 at 09:31
The insert query you've posted does not contain any `ON DUPLICATE KEY` parts, and the output also tells you about that.... — Nico Haase, Jun 22 '18 at 09:37
Bobby Tables will have a a field day with this code! http://bobby-tables.com/ — GordonM, Jun 22 '18 at 09:43

Pierre Grimaud · Answer 1 · 2018-06-22T09:57:47.697

1

Try replacing this part of your script :

if(in_array("id", $colList))
{
    $query .= "ON DUPLICATE KEY UPDATE\n\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)."\n";
}
else
{
    echo "<p><b>`id`</b> column not found. <i>ON DUPLICATE KEY UPDATE</i> clause <b>NOT</b> added.</p>\n";
    echo "<p>Columns Found:<pre>".print_r($colList, true)."</pre></p>\n";
}

with :

$query .= "ON DUPLICATE KEY UPDATE\n\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)."\n";

edited Jun 22 '18 at 09:57

answered Jun 22 '18 at 09:41

Pierre Grimaud

56
5

It is hard coding, and I have more than 30 tables, each may has 6 or more fields. Some of them has more than 15 fields. – S.Sakthybaalan Jun 22 '18 at 09:43
Wow!, It added and updated the fields,Please see this echo's : https://pastebin.com/3ne5MvB9 – S.Sakthybaalan Jun 22 '18 at 10:05
I have the following records for `counters` table. It is not inserted. **INSERT INTO `counters` (`id`, `name`, `description`, `added_on`, `last_updated`, `department`) VALUES ('CHUA_4b2bce45152d410', 'D', '', '2018-02-19 07:36:45', '2018-02-19 07:36:45', 'CHU');** Why? – S.Sakthybaalan Jun 22 '18 at 11:21
What will happens when removing the `if` condition? – S.Sakthybaalan Jun 23 '18 at 14:01

score 1 · Answer 2 · answered Jun 22 '18 at 09:42

1

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))

answered Jun 22 '18 at 09:42

gabe3886

4,235
3
27
31

So, changing in either one place is ok? – S.Sakthybaalan Jun 22 '18 at 09:46
Again also same problem. But it removes the ticks from the field names as : _INSERT INTO `counters` (id,name,description,added_on,last_updated,department) VALUES_ – S.Sakthybaalan Jun 22 '18 at 09:51
@SAKTHY if changing it in colList breaks it, change the in_array part to include the ticks – gabe3886 Jun 22 '18 at 10:40
Sorry! how to change, please? – S.Sakthybaalan Jun 22 '18 at 10:47
Please see this echos for your suggestion : https://pastebin.com/Dx1zvUDH – S.Sakthybaalan Jun 22 '18 at 10:53
@gabe3886 sorry for editing your answer by mistake. please ignore the edit. – svet Jun 22 '18 at 11:44

svet · Accepted Answer · 2018-06-25T11:33:00.130

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) . "\n";

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

edited Jun 25 '18 at 11:33

answered Jun 22 '18 at 10:21

svet

11,078
1
15
26

@gabe3886, also said this. See above – S.Sakthybaalan Jun 22 '18 at 10:23
@SAKTHY please let me know if the code works as expected – svet Jun 22 '18 at 11:45
It shows the error in `foreach($dataSets as $dataSet)` as `Notice: Undefined variable: dataSets in /home/finemart/public_html/newSync/newThadasar.php on line 12` `Warning: Invalid argument supplied for foreach() in /home/finemart/public_html/newSync/newThadasar.php on line 12` – S.Sakthybaalan Jun 22 '18 at 17:52
If the structure of the json file is the same as in the example there should be no errors. If every object in the json array has "tableName" and "rows" keys and also "rows" values are always arrays even an empty one. – svet Jun 25 '18 at 09:38
The json file is exactly as in the example. And each array has `tableName` and `rows`. No one is not there without of them, because both of them are put by the iteration. So why causes that error? – S.Sakthybaalan Jun 25 '18 at 09:57
Here is the example online: https://www.tehplayground.com/7eyOEdQqHXSvDB3S The json array is embedded in the code. Since online we cannot query a database only the queries are outputted. The queries are fine - you can test them at your environment. The othe part of the code you have to adjust yourself locally for your setup. – svet Jun 25 '18 at 10:55
I got an error as `Warning: Invalid argument supplied for foreach() in Standard input code on line 190` And the `Line 190` is `foreach($dataSets as $dataSet)` – S.Sakthybaalan Jun 25 '18 at 11:04
This is the full json : https://raw.githubusercontent.com/vbsaba1992/sample/master/newSample.json – S.Sakthybaalan Jun 25 '18 at 11:15
Your JSON file is malformed. At line 181 and 182 there are two closing square brackets **]**. Replace these two closing square brackets **]** with a single comma **,** i.e. delete line 181 and 182 and put there a comma. The code above was updated and now it throws Exception when the json file cannot be parsed. – svet Jun 25 '18 at 11:29
Oh! Is this the cause to prone the error? But I made this JSON using C# JSON writer. I am usiing this code : https://pastebin.com/J6XQV09J – S.Sakthybaalan Jun 25 '18 at 11:37
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/173764/discussion-between-user1878906-and-sakthy). – svet Jun 25 '18 at 17:06

php ON DUPLICATE KEY UPDATE also says 1062: Duplicate entry for key 'PRIMARY'

3 Answers3