PDA

View Full Version : How do I make two fields primary, yet act individually?


Apothem
05-10-2009, 12:55 AM
Here's what I want to do:
I want to insert into a table values with an unique ID and an unique URL.
At the same time, if I'm trying to insert a row into a table, I want to check if the URL exists and if it does, replace values... with the absence of the ID.

The problem with this is that if I REPLACE a duplicate URL into that table, it auto-increases the ID by 1 - which I wanted to be left alone.

Here's the table:
CREATE TABLE IF NOT EXISTS `data` (
`id` int(20) unsigned NOT NULL auto_increment,
`url` varchar(255) NOT NULL default '', # True URL to the site
`cache` text, # Other data saved
`views` int(11) unsigned NOT NULL default '0',
`time` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY (`url`),
INDEX (`url`),
INDEX (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now here's what it will have:
1 | http://google.com/ | 1 | 1 | 122954032

Query ran:
REPLACE INTO `data` (`url`, `cache`, `views`, `time`) VALUES ('http://google.com/', '5', '2', '122958035')

Result:
2 | http://google.com/ | 5 | 2 | 122958035

Wanted result:
1 | http://google.com/ | 5 | 2 | 122958035

BigRusky
05-10-2009, 04:21 AM
use UPDATE instead.

This shows how:
http://www.w3schools.com/Sql/sql_update.asp