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

mysql, sql

Solution

you need some UNIQUE KEY on your table, if user_url is tweer_url, then this should fit (every tweet has a unique url, id would be better).

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

and its 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 update existing row but you want to insert just once

Problem

I have a script which captures tweets and puts them into a database. I will be running the script on a cronjob and then displaying the tweets on my site from the database to prevent hitting the limit on the twitter API. So I don't want to have duplicate tweets in my database, I understand I can use 'INSERT...ON DUPLICATE KEY UPDATE' to achieve this, but I don't 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 to insert 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 would I correctly use 'INSERT...ON DUPLICATE KEY UPDATE' to insert only if it doesn't exist, and update if it does? Thanks

Original source