Count Page views each separate

Question

I want to make a count views system ,I have one for the site count views , but that's in general for the main site , dunno how to do it individually but it seems like this one will not be easy ! So I will show you my old system and in what way does the new should work, so I got this Running so far

$guest_ip = $_SERVER['REMOTE_ADDR'];
$visits = mysql_query("INSERT INTO visitors(ip) VALUES('".$guest_ip."')");
$visitoronsite = mysql_query("SELECT * FROM visitors");
$onlinevisits = mysql_num_rows($visitoronsite);

?>
Visitors: <?PHP echo $onlinevisits; ?>

Of Course It is great to keep track of who and how many , but As my site is with videos and each video has link with php ID which is something like site/index.php?id=8 and that page , What I want to do is to put a counter on that page saying that many people watched using this link and I already have a teory but Has Few Issues So I created a table called pageviews with 2 columns one ip and the other one page , now here's my question , I cannot use unique because I want to show that that IP has also watched this link but it wount insert it because it will be unique so what do I do to keep one ip unique as for that page only?

Answer 1

On your pageviews table, you should create a unique index based on two columns (ip and video or link id). You add a unique on your table like this:

ALTER TABLE `pageviews` ADD UNIQUE INDEX `ip_video_unique` (`ip`, `video_id`);

Than you would be able to store unique video views per IP.

On the page, you can get the count of how many IPs have viewed that video by doing a query such as:

SELECT * FROM `pageviews` WHERE video_id = x

where x in this query is the id of the video. In case of site/index.php?id=8 it would be 8.

Its also better if you separate the IPs on a separate table where you store visitors with a unique id and their respective IP and on pageviews table you reference the unique visitor id instead of the IP. Later on, this would allow you to count video views on a table and something else's views on another table but have the visitors(and their IPs) aggregated on a single table.