How to use SQL-INSERT ... ON DUPLICATE KEY UPDATE?

Question

I have a script that captures tweets and puts them in a database. I will run the script on cronjob, and then display the tweets on my site from the database, in order to prevent restrictions on the twitter API.

So, I do not want to have duplicate tweets in my database, I understand that for this I can use "INSERT ... ON DUPLICATE KEY UPDATE", but I do not quite understand how to use it.

My database structure is as follows.

Table - Hash id (auto_increment) tweet user user_url

And currently my SQL for insertion is as follows:

$tweet = $clean_content[0]; $user_url = $clean_uri[0]; $user = $clean_name[0]; $query='INSERT INTO hash (tweet, user, user_url) VALUES ("'.$tweet.'", "'.$user.'", "'.$user_url.'")'; mysql_query($query); 

How to properly use "INSERT ... ON DUPLICATE KEY UPDATE" to insert only if it does not exist and is updated if it does?

thanks

Answer 1

you will need a UNIQUE KEY on your table, if user_url is tweer_url then this should match (each tweet has a unique URL, id will be better).

 CREATE TABLE `hash` ( `user_url` ..., ..., UNIQUE KEY `user_url` (`user_url`) ); 

and it’s better to use INSERT IGNORE on your case

 $query='INSERT IGNORE INTO hash (tweet, user, user_url) VALUES ("'.$tweet.'", "'.$user.'", "'.$user_url.'")'; 

ON DUPLICATE KEY is useful when you need to update an existing row, but you want to insert only once

Answer 2

Try using:

 $query='INSERT INTO hash (tweet, user, user_url) VALUES ("'.$tweet.'", "'.$user.'", "'.$user_url.'") ON DUPLICATE KEY UPDATE tweet = VALUES(tweet)'; 

Answer 3

ON DUPLICATE KEY UPDATE does not seem to be the right solution, since you do not want to update if the value is already in the table.

I would use my own unique Twitter status identifier (which should be unique for each tweet) instead of your hash identifier. Add this as a field to your table and define it as a primary key (or as a unique index.) Then use REPLACE INTO, including the status identifier from Twitter.

This has the advantage that you can always track your recording to a unique twitter account on Twitter, so you can easily get additional information about Tweet later if you need to.