Ensuring uniqueness of additions to MySQL table using PHP

I'm trying to create a page that tracks some basic user statistics in a database. I'm starting small, by trying to keep track of how many people come using what Us开发者_开发知识库er Agent, but I've come across a stumbling block. How can I check the User Agent being added to the table to see if it is already there or not?

You can make the column that stores the User Agent string unique, and do INSERT ... ON DUPLICATE KEY UPDATE for your stats insertions

For the table:

  CREATE TABLE IF NOT EXISTS `user_agent_stats` (
  `user_agent` varchar(255) collate utf8_bin NOT NULL,
  `hits` int(21) NOT NULL default '1',
  UNIQUE KEY `user_agent` (`user_agent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user_agent | varchar(255) | NO   | PRI | NULL    |       | 
| hits       | int(21)      | NO   |     | NULL    |       | 
+------------+--------------+------+-----+---------+-------+

You could use the following query to insert user agents:

INSERT INTO user_agent_stats( user_agent ) VALUES('user agent string') ON DUPLICATE KEY UPDATE hits = hits+1;

Executing the above query multiple times gives:

+-------------------+------+
| user_agent        | hits |
+-------------------+------+
| user agent string |    6 | 
+-------------------+------+

Before adding it to the database, SELECT from the table where you're inserting the User Agent string. If mysql_num_rows is greater than 0, the User Agent you're trying to add already exists. If mysql_num_rows is less than or equal to 0, the User Agent you're adding is new.