CODEWITHSUNDEEP
phpmysql
TrevTech
TrevTech

Reputation: 49

Preventing Duplicates In MYSQL

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.

Upvotes: 0

Views: 94

Answers (2)

TrevTech
TrevTech

Reputation: 49

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.

Upvotes: 0

djs
djs

Reputation: 56

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.

Upvotes: 1

Related Questions