Preventing Duplicates In MYSQL

Question

I'm running a script to insert data in my table, but I also want to run the same one, but not duplicate the data that is inserted:

here is my code, for some reason it's not working when I run it, it still add more of the same data.

$query = 'INSERT IGNORE INTO tbl_scrape_main ( 
   `scrape_main_id`, 
   `tbl_sc_date`,
   `tbl_sc_date_added`, 
   `tbl_sc_url`, 
   `tbl_sc_url_title`,
   `tbl_sc_new`, 
   `tbl_sc_title`, 
   `tbl_sc_town`, 
   `tbl_sc_state` 
) VALUES ( 
    '.sql_val($scrape_main_id).', 
   '.sql_val($temp['date']).',
   '.sql_val($date_added).', 
   '.sql_val($temp['url']).', 
   '.sql_val($temp['url_title']).',
   '.sql_val($temp['new_entry']).',
   '.sql_val($temp['title']).', 
   '.sql_val($temp['town']).', 
   '.sql_val($temp['state']).' 
)
ON DUPLICATE KEY UPDATE 
     `scrape_main_id` = '.sql_val($scrape_main_id).', 
     `tbl_sc_date_added` = '.sql_val($temp['date']).', 
     `tbl_sc_date` = '.sql_val($date_added).', 
     `tbl_sc_url` = '.sql_val($temp['url']).', 
     `tbl_sc_url_title` = '.sql_val($temp['url_title']).', 
     `tbl_sc_new` = '.sql_val($temp['new_entry']).', 
     `tbl_sc_title` = '.sql_val($temp['title']).', 
     `tbl_sc_town` = '.sql_val($temp['town']).', 
     `tbl_sc_state` = '.sql_val($temp['state']).'
'; 
$result = mysql_query($query) or die("<b>A fatal MySQL error occured</b>.<br />Query: ".$query."<br />Error: (".mysql_errno().") ".mysql_error());

What could be the issue.

Define "duplicate". Are you inserting duplicate data with a different key? I doubt you're inserting duplicate keys, unless your primary key isn't actually a primary key. But there's no way for the database to know what "duplicate" means outside the context of a key or unique constraint. — David, Dec 12 '14 at 18:53
Ignore and on duplicate - which do you want? Surely it makes non sense to have both !?! — Strawberry, Dec 12 '14 at 18:54
DESCRIBE the table. What is your key and how is it updated? Is it autoincrementing? If so then "ON DUPLICATE KEY UPDATE" will never fire. — Eugene Scray, Dec 12 '14 at 18:56

score 1 · Answer 1 · edited May 23 '17 at 11:57

It would really help to know what the keys on your table are to fully answer the question, but one problem with your query is that you are using both INSERT IGNORE and ON DUPLICATE KEY UPDATE. You should not use both as the first one tells MySQL to ignore the new row if a duplicate is encountered (which sounds like the behavior you want) while the second one tells it which fields to update in the original if a duplicate is encountered.

From what you described ("it still add more of the same data") it sounds like the unique key on your table is not what you expect it to be.

Run this command to see the keys on your table and make sure that the UNIQUE key is what you expect it to be (ie: the one that shouldn't be duplicated): SHOW CREATE TABLE tbl_scrape_main;

For instance, if you consider a row a duplicate if 'scrape_main_id' is the same as another row, you need a unique key on that field. If instead you consider a row a duplicate if the combo of ( scrape_main_id, tbl_sc_date_added ) is the same, those need to be your unique key... and so on. Check out this answer for more.

score 0 · Answer 2 · answered Dec 13 '14 at 20:06

0

Thanks for everyone's help, it turns out I needed to set a unique ID and only use the INSERT IGNORE, works great.

Thanks again.

answered Dec 13 '14 at 20:06

TrevTech

49
6

Preventing Duplicates In MYSQL

2 Answers2