CODEWITHSUNDEEP
phpsqlmysql
Andy
Andy

Reputation: 3170

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 User 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?

Upvotes: 1

Views: 99

Answers (2)

Michael Robinson
Michael Robinson

Reputation: 29498

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 | 
+-------------------+------+

Upvotes: 3

Raphael Caixeta
Raphael Caixeta

Reputation: 7846

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.

Upvotes: 1

Related Questions