replacing columsn on the time

taydag

Hi ppl
I would like to write a code to replace 2 columns in mysql database , "todayrank and todayhit" with "yesterdayrank, yesterdayhit" when the newday come (0:00am)
How can i program such a timer code on PHP?
Thanks by now for your responses

Norman_Graham

Hi Taydag

As far as I understand it, you can't. You need a cron-job, which any Unix-based provider will do for you, but usually for a small fee. However, I could be wrong.

A better solution might be to alter the DB query which displays the ranking. How are you displaying the ranking at the moment?

Norm

taydag

Hi Norman

I list main ranking with the following sql

 $sql = "SELECT * FROM uyesite WHERE 	approved=1 AND topcategory='".$cat."'  AND adult <= '".$_SESSION['adult']."' ORDER BY todayhit desc";

and updating ranking in my index page the followin function


<? function update_rank() {
global $n, $id, $conn;
	$sql1 = "SELECT * FROM uyesite WHERE approved ='1' ORDER BY todayhit DESC ";
$result1 = mysql_query($sql1, $conn) or die(mysql_error());
$n = mysql_num_rows ($result1) ;
$counter = 1 ;
	while ( $counter <= $n ) {
	$na1 = mysql_fetch_array($result1) ;
	$id  = $na1['id'] ;
	$sql2 = "UPDATE  uyesite SET todayrank = '".$counter."'  WHERE id = '".$id."' ";
	$result2 = mysql_query($sql2, $conn) or die(mysql_error());
    $counter++;
 }	
 $sql2 = "UPDATE siteinfo SET lastaction = NOW() ";
$result2 = mysql_query($sql2, $conn) or die(mysql_error());
}?>

I guess i should play with time() funcition when if the time has pasted a new day it should replace them .. but i just couldnt make the structure in my head so i can not code it 🙁

Norman_Graham

Hi Taydag

Got your e-mail. Em ... I tell you what I'd do: I'd just have a single column in the table, or even better a separate table, listing rankings. I'd stick a UNIX timestamp on it. In my query I'd then ask MySQL to calculate the age of the ranking. You can then display rankings any way you like: today, yesterday, last week etc.

I have a comments page in which I calculate the age of the comments and then only display the most up-to-date ones. The same system would work for displaying rankings of different ages.

Have a look at this: http://www.mysql.com/doc/en/Date_and_time_functions.html

I think this is a better approach than having columns which specify how old a ranking is. What do you do after today and yesterday? You'd have to either have a delete sequence or dump all the rankings into a column 'older that 48 hours'. It seems to me that your current approach is hard-coding time and will lead to a lot of unnecessary work. Your update function is fine, but you'd have to run it every time someone calls the page with the ranking display and thus increase overheads. If you have heavy traffic, the MySQL will be running extensive updates over an entire table every few seconds. I'd prefer to let time run and just do a simple calculation every time the ranking is called.

Does that help? If not, let me know.

Norm