rotating links

fris

Curious how I would go about rotating links based on day.

I have a .txt file with links on each line, currently im just doing random, but i want to have say 20 links, and they stay in the same spot for 24 hours, then the first link becomes the 2nd, and the 20thlink becomes the first, so they move down a spot after 24hours has gone by.

is this possible without a sql db?

bpat1434

Why move them? Let's say you move the links to a SQL database. Each row will have a unique ID (well, each row should have a unique ID) and it would need a timestamp field. So your table might look like:

CREATE TABLE `links` (
    `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `url` TEXT NOT NULL,
    `last_used` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = MYISAM;

Now, given that table, we can have say 20 links. So we'll have IDs 1 through 20. Now it's just a matter of a series of queries.

<?php

function getLink()
{
	$query = "SELECT `id`, `url` FROM `links` WHERE `last_used` = NOW() ORDER BY `last_used` DESC LIMIT 1";
	$result = mysql_query($query) or die('SQL Error: ' . mysql_error());

// There is already a link defined for today... return the URL
if(mysql_num_rows($result) == 1)
{
	$row = mysql_fetch_assoc($result);
	return $row['url'];
}

// Was there one yesterday?
$query = "SELECT `id` FROM `links` WHERE `last_used` = DATE_SUB(NOW(), INTERVAL 1 DAY)";
$result = mysql_query($query) or die('SQL Error: ' . mysql_error());

// There wasn't one yesterday, let's just get the first link
if(mysql_num_rows($result) < 1)
	$linkID = getFirstLinkID();
// Let's get the next ID after the one from yesterday...
else
{
	$row = mysql_fetch_assoc($result);

	$query = "SELECT `id` FROM `links` WHERE `id` > " . intval($row['id']) . " LIMIT 1";
	$result = mysql_query($query) or die('SQL Error: ' . mysql_error());

	// If there is no ID that's greater, get the first link
	if(!$result || mysql_num_rows($result) < 1)
		$linkID = getFirstLinkID();
	else
	{
		$row = mysql_fetch_assoc($result);
		$linkID = $row['id'];
	}
}

// Get the link URL:
$query = "SELECT `url` FROM `links` WHERE `id` = " . intval($linkID);
$result = mysql_query($query) or die('SQL Error: ' . mysql_error());
$row = mysql_fetch_assoc($result);

// Update it so subsequent calls are faster ;)
$query = "UPDATE `links` SET `last_used` = NOW() WHERE `id` = " . intval($linkID);
$result = mysql_query($query) or die('SQL Error: ' . mysql_error());

return $row['link'];
}

function getFirstLinkID()
{
	$query = "SELECT `id` FROM `links` ORDER BY `id` ASC LIMIT 1";
	$result = mysql_query($query) or die('SQL Error: ' . mysql_error());

$row = mysql_fetch_assoc($result);
return $row['id'];
}

It's quite a few queries, but it works. I'm sure it could be trimmed down, but I wasn't going for efficiency, just a kind of "proof of concept".

igorlopez

I would also suggest bpat1434's solution but yes it is still possible to solve without sql db.

Since you own the text file and therefore also the format you need to add a timestamp and current top 1 row pointer (number indicating which row should be at top). Placement of timestamp and row pointer can be where ever you fell but typically on the first row with a separator or in the beginning of the first two rows.

Then it is just a matter of parsing the timestamp and compare with current timestamp and if 24 hr has elapsed you need to rewrite the file with the new timestamp and move the current row 1 pointer 1 step (and don't forget the wrap).

This can essentially be accomplised in two ways,
1) let your php code perform the operation during normal usage which maybe looks the easiest way but has potentially problems with simultaneous access to file read/write.
2) set up a cron job run every 24 hr (which actually means you can get rid of the timestamp and just have a top 1 row pointer). This solution only has one task writing the file and everybody else is just reading.

fris

how would i display all 20 links? its quite different from doing it via text file.

cause i was using file and fopen and a while loop

igorlopez

In the same way you have done before,

I have a .txt file with links on each line, currently im just doing random,

and you are already reading the file content

cause i was using file and fopen and a while loop

So it comes down on how to handle the string read by fread where you now with the newly added information on which row is the top 1 link. One example is this

// $linkStrings : 
//    is the variable with all links read in the 
//    same order as they are in the text file.
// $topRowPtr :
//    is the variable read from somewhere in the beginning 
//    of the file denoting  which link should be at the top.
// Note!! I am using a hardcoded 20 which is not a good coding
// praxis but this is just an example
for($link=0; $link<20; $link++)
{
   // assign the current link
   $currentLinkStr = $linkStrings[$topRowPtr];
   // Perform your html magic

   // increment $topRowPtr
   $topRowPtr++;
   // test for wrap of $topRowPtr
   if(20 >= $topRowPtr)
   {
      $topRowPtr = 0;
   }
}