Help with updating my database!

I have a script where users can submit a site and then the top 15 sites with the most clicks out are displayed on the left of my page. For each link it had it's own id, so www.site.com/id/1/ would redirect to their site and also update the database.

However, currently it could basically be clickbombed, if you visited site.com/id/1/ loads of times it'll appear at the top of the top 15 list..

What I want to do is change the script so a user can visit a site as many times as he wants in 24 hours, but it'll only record 1 hit. So I limit by IP.

The code below is my redirect code..

<?
// We need the database stuff
require 'config.php';
if ($_GET['id']) {
$id = mysql_real_escape_string($_GET['id']);
// Check ID exists
$q = "SELECT * from links where link_id = '$id'";
$res = mysql_query($q) or die("Error in query " . mysql_error());
$nr = mysql_num_rows($res);
if ($nr > 0) {
$data = mysql_fetch_assoc($res);
mysql_query("UPDATE links set link_visitors = (link_visitors + 1) where link_id = '$id'");
?>

PHP:

I assume I need to update the query:

mysql_query("UPDATE links set link_visitors = (link_visitors + 1) where link_id = '$id'");

Can anyone help me, rep to all that helps.

 

You'd need a separate table to store hits (ip,time and site) - before updating link_visitors, query the hits table to see if the IP has already visited in the last 24 hours. Then only execute the update links table if its a new visitor.

You'd also want to clear out the hits table regularly.

 

and how would I do that? I don't know much

 

You can do it cookie based, check to see if cookie exists, if not update database.

If you do it the IP way, you would need to have an insert into a new table.
Then once the user comes to the page, you would do a lookup check like you do for the link existing counting the amount of rows.

But theres nothing stopping them using a proxy using this method. You need to devise a way that is unique to your script, or look around at free toplists at how they combat this.

 

*) cookie based: easy to circumvent - it's enough to disable cookies or clean them up - easier to code

*) IP based: more difficult to circumvent (you need to use a proxy or logout/login from your connection every time), could lead to "false positives" (people using the same proxy or sharing a unique IP address would be marked as one user), it's a (little bit) trickier to code

If you want the IP based check, you need to:

1. create a new table called (for example) 'link_hits'
2. record in this table every hit for a link and the IP that generated it:

    $query = "INSERT INTO link_hits ( link_id, ip, time ) VALUES ( '$id', '" . $_SERVER["REMOTE_ADDR"] . "', " . time() . " )";

   PHP:
3. check if there's already a record with same IP/link id newer than 1 day:

   $query = "SELECT ip FROM link_hits WHERE link_id = '$id' AND ip = '" . $_SERVER["REMOTE_ADDR"] . "' AND time > " . (time() - 86400);

   PHP:
   before inserting a new hit

Obviously you need to do the escaping/input validation that I didn't do here...

HTH, cheers!

 

<?
require('config.php');
if(is_int($_GET['id'])) {
	$query	= @mysql_query("SELECT * FROM links WHERE link_id='$id' LIMIT 1");
	$count	= @mysql_num_rows($query);
	if($count > 0) {
		$update	= @mysql_query("UPDATE links SET link_visitors=(link_visitors+1) WHERE link_id='$id'");
			if($update) {
				echo 'Verify Update.';
			} else {
				echo 'Update Error.';
			}
	}
}
?>

PHP: