How to ignore a duplicate field and proceed with the insertion?

Navigation
3

I have a txt file that I am extracting information and in it the information is repeated 6 times, which inserts me in the database the same record also 6 times.

Sete UNIQUE to a unique number for each record in the mysql database but when trying to run the sql that inserts the data, it gives Entrada '120000001016' duplicada para a chave 'SQ_CANDIDATO' and the insertion process is finished.

How could I solve this problem by inserting only once each record in the database without having to treat txt ? 

foreach ($itens as $item) {

$dados      = explode(';', $item);

mysql_query("INSERT INTO VOT_CAND_MUN_ZONA VALUES ( 

                        '',
                        '".utf8_decode(trim($dados[0], '"'))."',
                        '".utf8_decode(trim($dados[1], '"'))."',
                        '".utf8_decode(trim($dados[2], '"'))."',
                        '".utf8_decode(trim($dados[3], '"'))."',
                        '".utf8_decode(trim($dados[4], '"'))."',
                        '".utf8_decode(trim($dados[5], '"'))."',
                        '".utf8_decode(trim($dados[6], '"'))."',
                        '".utf8_decode(trim($dados[7], '"'))."',
                        '".utf8_decode(trim($dados[8], '"'))."',
                        '".utf8_decode(trim($dados[9], '"'))."',
                        '".utf8_decode(trim($dados[10], '"'))."',
                        '".utf8_decode(trim($dados[11], '"'))."',
                        '".utf8_decode(trim($dados[12], '"'))."', <---- UNIQUE
                        '".utf8_decode(trim($dados[13], '"'))."',
                        '".utf8_decode(trim($dados[14], '"'))."',
                        '".utf8_decode(trim($dados[15], '"'))."',
                        '".utf8_decode(trim($dados[16], '"'))."',
                        '".utf8_decode(trim($dados[17], '"'))."',
                        '".utf8_decode(trim($dados[18], '"'))."',
                        '".utf8_decode(trim($dados[19], '"'))."',
                        '".utf8_decode(trim($dados[20], '"'))."',
                        '".utf8_decode(trim($dados[21], '"'))."',
                        '".utf8_decode(trim($dados[22], '"'))."',
                        '".utf8_decode(trim($dados[23], '"'))."',
                        '".utf8_decode(trim($dados[24], '"'))."',
                        '".utf8_decode(trim($dados[25], '"'))."',
                        '".utf8_decode(trim($dados[26], '"'))."',
                        '".utf8_decode(trim($dados[27], '"'))."',
                        '".utf8_decode(trim($dados[28], '"'))."'

)") or die (mysql_error());
    
asked by anonymous 23.02.2015 / 21:07

2 answers

2

The insertion process is because you are calling die() if mysql_query fails, this causes the script to finish in a similar fashion as a exit . Removing die() the error will still happen but the next array item will be evaluated in the query.

The ideal is to write the errors of mysql_error() in a log to part.

    
23.02.2015 / 21:21
3

Use the IGNORE keyword to ignore any errors (including duplicate lines) in your query: 

INSERT IGNORE INTO ...

From MySQL documentation :

  

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

     

IGNORE has a similar effect on inserts into partitioned tables where no partition matching to given value is found. Without IGNORE, such INSERT statements are aborted with an error; however, when INSERT IGNORE is used, the insert operation fails silently for the row containing the unmatched value, but any rows that are matched are inserted. For an example, see Section 19.2.2, "LIST Partitioning".

     

Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info () C API function how many rows were actually inserted into the table.

    
23.02.2015 / 21:20
Doubt or $ group mongodb Problems with link and query