I am building a click counter application using MySql because I want to record and display the IP addresses of the last 50 clicks from various links to external sites from my website .
Each click will be created as a record in a mysql table but I want to limit the total number of records per id to 50 (to save the database getting too full).
So far I have something similar to this:
Table Structure:
count (auto_increment)
id (id number of the link)
ip (ip address of the link)
country (visitor country)
timestamp
Click thru' script:
REMOTE_ADDR + Geo Targetting script etc. goes here and gives values for $ip and $country - All this works fine.
mysql_query("INSERT INTO click_log (count,id,date,time,ip,country) VALUES(\"\",\"$id\",\"$ip\",\"$ip_country\")",$conn);
$num_rows=mysql_query("SELECT * FROM click_log WHERE id='$id'",$conn);
if($num_rows>50){
mysql_query("DELETE FROM click_log where ID='$id' LIMIT 1",$conn);
}
My problem is that I cannot get the DELETE part to work as it should, ie. delete the earliest record when the number of rows reaches 50 - Nothing Seems to happen!
The count (auto_increment) field is not really necessary, although I found that if I didn't include it, the script worked except that it deleted the latest record instead of the earliest one.
I have also tried this (with no count column) to get it to delete the earliest record:
mysql_query("DELETE FROM click_log where ID='$id' ORDER BY timestamp LIMIT 1",$conn);
This doesn't work either and I've read that there may be a bug with using ORDER BY with the DELETE clause.
Any help or alternative approacehes would be greatly appreciated as I really need to get this working.
Thanks
Rob
