Link list help

dvd871

I'm working on a simple link list type of script where users login and then click on links of others each day. I'm not quite sure of the best way to handle the list of links that have been clicked. What I do is generate a list of links from the database, then display them according to who has clicked the most links of others. Then what I need to do is re-generate a new list to click not including the links that have already been clicked for a 24-hour period. What would be the best way to accomplish this? Any advice would be greatly appreciated.

Pig

In your table of links, have a column called 'clicked' that tracks the last time it was clicked. Store a unix timestamp as a 10 character varchar.

When you query the database to find the links, filter out the ones that are older than 24 hours, and sort them by last click date. The query should look like this:
$result = mysql_query("SELECT * FROM links WHERE links_clicked < " . strtotime("now - 24 hours") . " ORDER BY links_clicked LIMIT 0, 10");

That will give you 10 links that have been clicked the longest time in the past, and are at least 24 hours old.

Then when they click the link, don't take it to the link's page immediately. From the query above, print the links like this:
while( $row = mysql_fetch_array($result) )
{
echo("<a href='redirect.php?id=$row[link_id]' target='_blank'>$row[links_title]</a><br>");
}

This way the links go to 'redirect.php'. Redirect will do 2 things. First it updates the link with the current time, and then it redirects to the page they wanted. Something like this:

if( !empty($_GET['id']) && is_numeric($_GET['id']) )
{
     mysql_query("UPDATE links SET links_time = " . time() . " WHERE links_id = " . $_GET['id']);
     $result = mysql_query("SELECT links_url FROM links WHERE links_id = " . $_GET['id']);
     $row = mysql_fetch_row($result);
     header("Location:" . $row[0]);
}
or die("you have accessed this page incorrectly");

You can read more about using unix timestamps here:
http://www.webhead.cc/main.php?inv=59

dvd871

We need to track the links that each user clicks every 24 hours. Here's a little bit better explaination:

user logs in and a list of all links is generated sorted by most clicks out.
user clicks a link from the list
link is recorded in the database as being clicked with a timestamp
a new list is generated minus the links that have already been clicked that day

My thoughts were to create an array of all the link ids when the user first logs in. Then create an array of links as they are clicked. I could then do an array_diff on the two arrays and then the final array that is created could be used to pull the links from the database after the inital link id array was made. Is there an easier or better way to go about this? Maybe an approach that I haven't considered? 😕

Weedpacket

If the count is by both user and link, then the appropriate thing to do in the database is a new table. Call it user_clicks, say. The first column is the usual unique ID number (probably not necessary, but it doesn't hurt); the second will contain user IDs; the third will contain URLs (maybe you already have a table of the URLs you want to track, in which case you'd store the ID here - if not, the URL itself will do); and the fourth will contain the count of the number of times that user requested that URL.

If the user has never requested that URL before, then when they do so for the first time, you'll insert a row into the clicked_links table, saying that this user requested this URL, and set the count to 1. After that, it's just a matter of increasing the counter by one each time. You can select all the rows for a particular user - to see which URLs they requested at the most, or you can select the most popular URLs overall (a SELECT SUM() query), and so on.

If the overhead of checking to see if a row already exists before updating it or inserting a new one seems too excessive (if you're using MySQL you don't yet have the luxury of triggers or stored procedures that would allow the database to do the work), then you could just insert a new user/URL row each time and not both with the count field - just count the duplicate rows. But this could make for an uncomfortably large table.