Daily link

klyan

i have a database of links in the database.

how can i show a link everyday and then going over a unique link each and every day and then maintaining the same pattern in the next round.

thanks in advance

klyan

Xzone5

Try this:

$query = "SELECT links FROM links_table WHERE used = 0";

$result = mysql_query("$query");

if (empty($result))
{
$query = "UPDATE links SET used = 0";

mysql_query("$query");

$query = "SELECT links FROM links_table WHERE used = 0";

$result = mysql_query("$query");
}

$link = mysql_result($result, 0, "links");

$query = "UPDATE links SET used = 1 WHERE links = $link";

mysql_query("$query");

//echo $link and formatting code...

links contains the URL of the linked page, and used has either the value of 1 or 0, where 0 is the default.

Matt

Weedpacket

Stream of semiconsciousness....

Two new fields in the database: a boolean "seen", and a timestamp "seenwhen".

First, see if it's a new day.
SELECT link FROM linkstable WHERE seenwhen(I forget the MySQL, but test to see if seenwhen is "less than one day ago") ORDER BY seenwhen DESC LIMIT 1
You should be able to get away with dropping the ORDER BY ... DESC LIMIT 1; there should only be 0 or 1 record less than a day old anyway.

If you get a result,
{ then we've already rolled over to the new link for today. We've alredy fetched it. Nice, no more work here.
}
else
{ Still here? You didn't get a result? Then it's time for a new link.
SELECT id,link FROM linkstable WHERE seen=false ORDER BY seenwhen ASC LIMIT 1

If you get a result,
{	this is the new link for today. Couple of updates needed:

	[b]UPDATE linkstable SET seen=true, seenwhen=NOW() WHERE id=$id[/b]

	where $id is of course the id from the previous SELECT. Okay, we have our link now. No more work.
}
else

{	Still haven't got a link? That must mean that every link in the table has been seen. Fine: we'll just insist that we want to seem them all again and try once more:

	[b]UPDATE linkstable SET seen=false[/b]
	[b]SELECT id,link FROM linkstable WHERE seen=false ORDER BY seenwhen ASC LIMIT 1[/b]
	[b]UPDATE linkstable SET seen=true, seenwhen=NOW() WHERE id=$id[/b]

	If you STILL haven't got a link by this stage,
	{	it's because the table is empty.
	}
}

}

When inserting a new link into the table, set seen to false and seenwhen to NOW()

Warning: Maybe when I'm more awake this will look silly to me.

Xzone5

God, you're tired?, I forgot a page gets looked at more than once a day. seenwhen() is not to be found, although I've never found the MySQL site to be very well organized. I'll mark the post and see if tonight this looks any different.

klyan

hi,

thanks to both of you.
i will try the codes tomorrow and let u know

thanks in advance.
klyan

Xzone5

Mine will not work right. If I have some time I'll come back here with a code that does, provided that weedpacket's does not.