Mysql如果重复UNIQUE KEY更新命中

Question

My simple php script for inset keyword. If keyword is already exit i need update hits counter.. How can i do it? if keyword is "sample" if already is there in db i need update "hits" = 2 , if again same keyword "hits" =3

CREATE TABLE IF NOT EXISTS `search` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `keyword` varchar(255) NOT NULL,
      `hits` varchar(255) NOT NULL,
      `date` datetime DEFAULT NULL,
      PRIMARY KEY (`ID`),
      UNIQUE KEY `keyword` (`keyword`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

my php:

$raw_results = mysql_query("INSERT INTO search (keyword, date) VALUES    ('$keyword', '$date' )");

Answer 1

Have you tried using ON DUPLICATE KEY?

INSERT INTO table (columns) 
    VALUES (values)
ON DUPLICATE KEY UPDATE hits = hits + 1

because you have a unique key on keyword it will increment the hits by 1

SIDE NOTE:

dont use PHP's mysql_* deprecated API it has a lot of security issues. You should instead use parameterized queries with mysqli_* or PDO