PHP/MySQL Help

[JaTochNietDan]

Hello again nice people !

 

Ok so I want to make it so that when you query a page, it checks the CURRENT_TIMESTAMP to a row, and then if that rows timestamp is at least 10 minutes behind..well yeah, do some code. However the hard part comes when I want to know how many 10 minutes behind it is, so lets say its 40 minutes behind the current timestamp, then I want to add 40 points. So anyone got any ideas? Sorry for all these questions, I'm new to PHP/SQL programming

 

 

[RussellReal]

well..

 

lets break that down;

 

1) Your script will ONLY be affecting the rows which are older than 10 minutes, if I'm right continue onto step 2..

 

2) in order to select rows older than 10 minutes, what would be a good query,

 

-->a) (IF UNIX TIMESTAMP) "SELECT * FROM `table` WHERE `theTimestamp` < '".(time() - (60 * 10))."'"

 

-->b) (IF DATETIME FIELDTYPE) "SELECT * FROM `table` WHERE `theTimestamp` < DATE_SUB(NOW(),INTERVAL 10 MINUTE b)

 

3) grab the results

 

$qry = mysql_query($sql);
while ($r = mysql_fetch_assoc($qry)) {
  $results[] = $r;
}
// see step 4

 

4) do the math on each result, possibly in a foreach for the array $results, gather the ids and the time difference, then do the changes in another query

[Mchl]

UPDATE table SET field = field + 10 * (MINUTE(TIMEDIFF(NOW(),timestampField)) DIV 10)

[RussellReal]

wow, or that, mysql is intense =o

[Mchl]

Warning: I didn't test it!

[JaTochNietDan]

mysql_query('UPDATE `users` SET actionpoints = actionpoints + 10 * (MINUTE(TIMEDIFF(CURRENT_TIMESTAMP,lastactionpoints)) DIV 10) WHERE username = "'.$row['username'].'"');

 

Doesn't work..any ideas?

 

I kind of want it more like..every 10 minutes on the 10 minute point, you get 10 actionpoints. So this wouldn't do that really

[Maq]

Doesn't work?

 

What happens?

 

You need to add or die(mysql_error()) on the end of your query to see descriptive errors.

[Mchl]

 

I kind of want it more like..every 10 minutes on the 10 minute point, you get 10 actionpoints. So this wouldn't do that really

 

You need a scheduler for that I'm afraid (probably cron).