PHP PDO尝试。处理重复

I have several data files like the following:

- table 1        - table 2        - table 3
 id     val        id    val        id   val
 ============      ==========       ===========
  1    one         1    uno         1    un
  2    two         4    dos         6    deux
  3    three       5    tres        7    trois

I'm trying to insert them in a database expecting:

= result table
   id   val
   ============ 
    1    one
    2    two
    3    three
    d1   uno
    4    dos
    5    tres
    dd1  un
    6    deux
    7    trois

The database structure is:

= sqlite database file: data.db
  CREATE TABLE register (
      id VARCHAR(3) PRIMARY KEY,
      val VARCHAR(16)
  );

I'm came up with this kind of solution:

<?php
$table1 = array("1"=>"one","2"=>"two","3"=>"three");
$table2 = array("1"=>"uno","4"=>"dos","5"=>"tres");
$table3 = array("1"=>"un","6"=>"deux","7"=>"trois");

$fileHandle = new PDO("sqlite:data.db");
$fileHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

foreach($table1 as $key => $value){
    try{
    $fileHandle->exec("INSERT INTO `register` (id,val) VALUES ('".$key."', '".$value."');");

    }catch(PDOException $e){
    if($e->getCode()=='23000'){   // if key exists, add a "d" to differentiate
        $fileHandle->exec("INSERT INTO `register` (id,val) VALUES ('d".$key."', '".$value."');");
    }
  }
}
// keys: 1, 2, 3

foreach($table2 as $key => $value){
    try{
    $fileHandle->exec("INSERT INTO `register` (id,val) VALUES ('".$key."','".$value."');");
    }catch(PDOException $e){
    if($e->getCode()=='23000'){   // if key exists, add a "d" to differentiate
        $fileHandle->exec("INSERT INTO `register` (id,val) VALUES ('d".$key."', '".$value."');");
    }
  }
}
// keys: 1, 2, 3, d1, 4, 5

foreach($table3 as $key => $value){
    try{
    $fileHandle->exec("INSERT INTO `register` (id,val) VALUES ('".$key."', '".$value."');");
    }catch(PDOException $e){
    echo "    getCode         : ".$e->getMessage()."
";
    if($e->getCode()=='23000'){   // if key exists, add a "d" to differentiate
        $fileHandle->exec("INSERT INTO `register` (id,val) VALUES ('d".$key."', '".$value."');");
    }
  }
}
// Signals error on the INSERT in the CATCH body

?>

But getting stuck on the second appearance of a key. Generating a duplicate key (d1) in the example.

SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: register.id

I can see the repeating pattern, but I don't see how to condense it. Or else reTRYing the modified key.

Thanks in advance for your input.

You get an error because you setup register.id field as primary key which includes unique constraint and id in your tables with data are not unique. If you need id values from tables you get data from, a typical solution is to make a separate id field of integer type with autoincrement as your primary key and make another field - called for example data_tables_id where you store id value from tables you get data from. So, your SQL for register table will be:

CREATE TABLE register (
    id Integer PRIMARY KEY AUTOINCREMENT,
    data_table_id VARCHAR(3) PRIMARY KEY,
    val VARCHAR(16)

);

and insert your data like so:

"INSERT INTO `register` (data_table_id,val) VALUES ('$key','$value');

I think that You need to change your arrays. You can't add the same value of id when it need to be unique. In result You want a values of id like 'dd1' and 'd1', but in arrays You have values set to '1'.

PHP PDO尝试。 处理重复

PHP PDO尝试。处理重复