Counting existing records in php and mysql

I have a simple php/mysql script that stores in mysql database id, link, and date fields. It stores various links and some of them appear several times. I would like to count the number of times each link was accessed. I was thinking of adding new field counter and updating it every time new record is inserted, but this needs to check if the link already exists and update counter wh开发者_JAVA百科en necessary.

This means I would have to:

1. Search the entire table to look if the link already inserted.

2. If no - insert it and set counter to value of 1.

3. If yes - find the correct record and update its counter by 1.

I would like to have something like:

1. www.link1.com 11:20

2. www.link2.com 11:30

3. www.link1.com 11:40

www.link1.com counter: 2 times

www.link2.com counter: 1 time

I am wondering what is the best way to do that? Should I rather create a new table that stores the counter for each link?

It depends on what you need to track. Do you only need to track total number of visits? If so, add a column to your table. You don't need to check wheter the link exists if you want to update the counter this way.

If you need to track who visisted when etc, create a new table with a foreign key and neccesary columns to your table. With this, you need to have the id of your link, but when you update the number of visits, you should already have it.

creating a new field and incrementing it is probably the simplest approach and should be easy to do.

If your site has users logging in, then you might want to create a table like users_links and store a record each time a user clicks a link, this way you can get the number of clicks, as well as more useful things like distinct users clicking, a history of what each user clicks on, correlations between link popularity and more!!!

Sorry if I didn't understand, but if you have this table structure:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `link` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

And these data:

INSERT INTO `test` (`link`, `id`) VALUES
(1, 'link1'),
(2, 'link2'),
(3, 'link1'),
(4, 'link2'),
(5, 'link1'),
(6, 'link1'),
(7, 'link3');

If I want to see how many occurrences of each link, I can use the COUNT() and GROUP BY from MySQL:

SELECT COUNT(link), link FROM `test` WHERE 1 GROUP BY link

And I get:

4 link1
2 link2
1 link3

Is it this you are looking for?