[SOLVED] Whos visited who code

[graham23s]

Hi Guys,

 

i have knoecked up a basic table with 4 fields

 

id(auto inc) / usersid / visitorsid / date

 

when someone visites another users profile i store it in mysql, i then have a section that display the "Last 10 users to visit your profile" my query is:

 

<?php
  $queryvisitors = "SELECT DISTINCT `visitorsid` FROM `usersvisitors` WHERE `usersid`='$var_loggedinuserid' ORDER BY `date` ASC LIMIT 10";
  $resultvisitors = mysql_query($queryvisitors);
?>

 

my question is how should i go about cleaning the table? there could be potentially thousands of entries being added or should i just empty the table every x amount of time?

 

thanks for any input guys

 

Graham

[pocobueno1388]

Setup a daily cron that will check for users that have had more then 10 views, and delete all the old ones.

[graham23s]

Hi Mate,

 

this will be the first cron i have done so:

 

just write a normal php script, then set up the cron to run every 24 hours?

 

also what would the query entail? not to sure how to write it and advice would be great

 

thanks mate

 

Graham

[Fehnris]

Yes just write a normal PHP script like one you would make for a webpage, but without any HTML or output, for example no echo statements.

 

It really depends on what limitations on keeping the profile view records, you want to impose on the database.  For example do you want to only limit the amount of stored views per profile to 10 views or do you want to remove profile views that are older than a certain amount of time? 

 

If you were to have a script that limits profile views to 10 per profile, you would need to have a while loop to get all your profiles seperately then get all views from that profile and select the newest 10 to keep and delete the rest. 

 

Alternatively if you wanted to only allow profile views to stay in your database until they reach a certain age.  You could simply get all the profile views that are older than a specified date and delete all these records.

 

All you would need to do is setup a Cron for this script to run when ever you wanted it to, every 24 hours if you prefer.

[graham23s]

thanks guys i think i know what im going to do now:)

 

Graham

[Fehnris]

Your Welcome