PHP更新现有MySQL行或插入新行（混淆UPDATE ON DUPLICATE KEY）

Question

I'm trying to code a pretty straightforward updater which can update/create a number of rows in a MySQL table, but I've definitely gone wrong somewhere. Here's what the table, service, looks like:

+---------+----+------------+-------------+--------------------+
| auto_id | id | number     | enlisted    | poe                |
+---------+----+------------+-------------+--------------------+
|      27 | 20 |            | 18.01.1916  | Newcastle, NSW     |
|      28 | 21 | 3088       | 31.07.1915  | Liverpool, NSW     |
|      29 | 21 |            | 19.05.1919  |                    |
|      30 | 22 |            | 10.1916     | 2 MD               |
|      31 | 23 | 1703       | 18.02.1916  | Melbourne          |
|      32 | 24 | 7683       | 13.09.1917  | West Maitland, NSW |
+---------+----+------------+-------------+--------------------+

auto_id is the auto-incrementing primary key. id is the ID of a soldier in another table (all the IDs are unique). As you can see, soldier #21 has two service records. My problem is that in my (ugly, unwieldy) PHP code, when I try and edit a soldier to add a service record, it simply adds new rows, both for the old record (which is being updated) and the new one. Here's the PHP:

for ($i = 0; $i < count($serviceArray); $i++) {
    $currentIDQuery = mysql_query("SELECT id from servicemen WHERE linkname='$target'");
    $currentID = mysql_fetch_row($currentIDQuery);
    $sqlService = "INSERT INTO service ".
                  "(id,number,enlisted,poe,unit,rank,place,casualties,awards,discharged,final_fate,cemetery,memorial)".
                  "VALUES('".$currentID[0]."', '".$serviceArray[$i]['number']."', '".$serviceArray[$i]['enlisted']."', '".$serviceArray[$i]['poe']."', '".$serviceArray[$i]['unit']."', '".$serviceArray[$i]['rank']."', '".$serviceArray[$i]['place']."', '".$serviceArray[$i]['casualties']."', '".$serviceArray[$i]['awards']."', '".$serviceArray[$i]['dis']."', '".$serviceArray[$i]['final']."', '".$serviceArray[$i]['cem']."', '".$serviceArray[$i]['mem']."')".
                  "ON DUPLICATE KEY UPDATE number='".$serviceArray[$i]['number']."', enlisted='".$serviceArray[$i]['enlisted']."', poe='".$serviceArray[$i]['poe']."', unit='".$serviceArray[$i]['unit']."', rank='".$serviceArray[$i]['rank']."', place='".$serviceArray[$i]['place']."', casualties='".$serviceArray[$i]['casualties']."', awards='".$serviceArray[$i]['awards']."', discharged='".$serviceArray[$i]['dis']."', final_fate='".$serviceArray[$i]['final']."', cemetery='".$serviceArray[$i]['cem']."', memorial='".$serviceArray[$i]['mem']."'";
    $retservice = mysql_query( $sqlService, $conn );
    if (!$retservice){
        die('Could not enter service data: ' . mysql_error());
    }
}

As far as I can make out, my problem is that the code will give a new auto_id whatever else it does. The DUPLICATE KEY it needs to be looking at is id, but there can be multiple identical values in there.

Answer 1

For the ON DUPLICATE KEY action to be performed, there would need to be a UNIQUE KEY on the table (in addition to the auto_id column). The INSERT action has to cause a "duplicate key" error.

The UNIQUE KEY could be on a combination of columns, for example, in the sample data, (id,enlisted) appears to be unique. (I'm just guessing at what combination of columns uniquely identify a row.)