CODEWITHSUNDEEP
phpmysqlsqlcodeigniterrss
viperfx
viperfx

Reputation: 327

Need to insert SQL without duplicates

The table is currently this:

CREATE TABLE `feed_items` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `feed_id` int(11) NOT NULL,
  `remote_id` varchar(32) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `link` varchar(255) NOT NULL DEFAULT '',
  `updated_time` datetime NOT NULL,
  `created_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I need to find a way so that if i pull multiple RSS feeds into one table, and articles with the same Title have the same value of 'remote_id', how can i make sure I do not insert a duplicate value?

I am currently using 

$this->db->query('INSERT INTO feed_items(feed_id, remote_id, link, title, created_time, updated_time) VALUES (?, ?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE remote_id=remote_id', array($this->feed_id, $this->remote_id, $this->link, $this->title, $this->created_time, $this->remote_id));

I was wondering if there is a better way?

Upvotes: 2

Views: 1719

Answers (5)

viperfx
viperfx

Reputation: 327

Thanks for the replies!

I actually managed to solve it a few hours after posting this, I made the remote_id a unique column and then did the following for the SQL

INSERT INTO feed_items(feed_id, remote_id, link, title, created_time, updated_time) VALUES (?, ?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE remote_id=remote_id

Upvotes: 0

Jake Feasel
Jake Feasel

Reputation: 16955

Try this:

$this->db->query('INSERT INTO feed_items(feed_id, remote_id, link, title, created_time, updated_time) SELECT ?, ?, ?, ?, ?, NOW() WHERE not exists(SELECT 1 FROM feed_items f2 WHERE f2.title = ? and f2.remote_id = ?)', array($this->feed_id, $this->remote_id, $this->link, $this->title, $this->created_time, $this->title, $this->remote_id));

Just to clarify - this solution changes the insert into ... values statement to an insert into...select statement, with a not exists() clause attached. This not exists clause will prevent the insert from doing anything if it finds a record that matches one that is already present. It won't throw an error if there is a pre-existing record.

Upvotes: 0

Michael Leaney
Michael Leaney

Reputation: 751

ALTER TABLE `feed_items` ADD UNIQUE INDEX `constraint` (`link`, `remote_id`);

Upvotes: 4

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

You can use ON DUPLICATE for avoiding such conditions: Check: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Hope it helps

Upvotes: 1

alex
alex

Reputation: 490303

Add a UNIQUE constraint to those two columns.

Upvotes: 4

Related Questions