duplicate entry error. How to catch? - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5) +--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=11) +--- Thread: duplicate entry error. How to catch? (/showthread.php?tid=79598) |
duplicate entry error. How to catch? - richb201 - 07-06-2021 I have a table where I am inserting rows. I have created the table with this: $sql = "CREATE TABLE BC_PR_list_temp( taxyear VARCHAR(10), employee_email VARCHAR(80), employee VARCHAR(80), qualified_dollars int NOT NULL, title varchar(80), role varchar(80), num_BC int NOT NULL, num_PR int NOT NULL, type varchar(4), item varchar(80), dollarsPerBC int NOT NULL, dollarsPerPR int NOT NULL, business_component_string varchar(120), project_string varchar(120), CONSTRAINT OnlyOne UNIQUE (employee_email, item, qualified_dollars) )"; $this->db->query($sql); At some later time I am inserting rows but sometimes insert the same row twice. That is why I put in the Constraint. Now when I try to put in the same row twice, I get this error: Error Number: 1062 Duplicate entry '[email protected]' for key 'BC_PR_list_temp.OnlyOne' That is a correct call to constraint but I want to catch that error and just continue. How do I just ignore that error. Right now the entire program stops! RE: duplicate entry error. How to catch? - php_rocs - 07-06-2021 @richb201 , Try insert ignore (https://dev.mysql.com/doc/refman/5.7/en/insert.html). I'm also assuming that you have a more recent or current version of MySQL. RE: duplicate entry error. How to catch? - richb201 - 07-06-2021 (07-06-2021, 12:55 PM)php_rocs Wrote: @richb201 , RE: duplicate entry error. How to catch? - richb201 - 07-06-2021 I think I will let the code fill in all the duplicates and then when it is done remove the duplicates with DELETE t1 FROM contacts t1 INNER JOIN contacts t2 WHERE t1.id < t2.id AND t1.email = t2.email; But this requires an autoincrment id. I can use "id int NOT NULL PRIMARY KEY AUTO_INCREMENT" in my Create table. But when I try to insert I get a duplicate key error. I have tried INSERT INTO BC_PR_list_temp SELECT NULL,srat.taxyear , srat.employee_email , etc But I get this: Error Number: 1062 Duplicate entry '1' for key 'BC_PR_list_temp.PRIMARY' Any ideas how to get the insert to work? I need the id in order to use the code above. RE: duplicate entry error. How to catch? - richb201 - 07-06-2021 SOLVED! I needed to use the keyword IGNORE. "INSERT IGNORE INTO BC_PR_list_temp SELECT srat.taxyear , srat.employee_email Thank you! RE: duplicate entry error. How to catch? - ikesela - 07-07-2021 bad table design, u need primary key (with auto_increment) to get it work well in future. RE: duplicate entry error. How to catch? - richb201 - 07-07-2021 (07-07-2021, 12:03 AM)ikesela Wrote: bad table design, u need primary key (with auto_increment) to get it work well in future. This is a temporary table. It gets used once to run koolreport on, for rendering. But please tell me how I insert a select into a autoincrement field for the future? |