Updating mysql database once a day

[andrewgarn]

Ok, what I am creating is a website that will track the highscores off a website, then record each day the scores into a mysql table named "date" with a primary key of the current date.

 

I have worked out the way of extracting the highscores to variables, what I need to work out now is how to make the php file retrieve and store the highscores once a day

 

I was thinking of something like creating a "last update" field in a table then:

 

If last update is greater than 24 hours then run highscore script and save highscores into todays date. This could be a few lines on the homepage, which would be ignored if the last update had been today.

 

However this relies on the php page being loaded once a day, is there any way to automate this, or does this seem like a good solution?

 

Can anyone enlighten me as to what the if function would be for checking todays date against the latest update? (to see if it was greater than 24 hours?)

 

Thanks

[Jabop]

You don't even really have to create a last update col in your table. Create a cron job and run the php file every 24 hours, regardless of last update time.

[andrewgarn]

It has to be hosted on a free hosting service on the internet and i dont know any of them with cron. (+mysql +php)

[DarkWater]

Correction:

It doesn't HAVE to be hosted on a free service, you just WANT it to be hosted on a free service because you don't feel like paying. 

 

If you aren't going to pay, then don't expect everything to work nicely.  Or work at all.

[andrewgarn]

I did ask for a PHP solution, i am just creating it in PHP, as a learning exercise, as I have little spare money to spend on such things, being a student at the moment with no job.

[Jabop]

Then make an if statement to pull the last update time from the table, and if more than 24 hours, run the update query. However, it will still require a page to be loaded to even check against the date.

[andrewgarn]

Then make an if statement to pull the last update time from the table, and if more than 24 hours, run the update query. However, it will still require a page to be loaded to even check against the date.

 

Yeh i figured that thanks.

 

I have found a web hosting service with cron, i'll try and learn that, see if i can work that out - http://www.aokhost.com

 

Thanks

 

As an additional question, for the website I wanted the ability to view changes in highscores:

 

today

this week

last week

this month

last month

this year

etc

 

How can I work out the date one week ago, one year ago with PHP from todays date? (and include error control if there is no highscore data a year ago choose the date in database closest with data)

 

the highscore change would then be worked out by today's highscores - highscores a year ago = highscore change

[Jabop]

SELECT * FROM HighScores WHERE Date>=DATE_SUB(DATE_ADD(CURDATE(), INTERVAL -1 DAY), INTERVAL 1 WEEK)  AND Date<CURDATE()

 

That would get the high scores from the last week, starting before the current day. So you don't get the highest scores from today also, just last week.

[andrewgarn]

SELECT * FROM HighScores WHERE Date>=DATE_SUB(DATE_ADD(CURDATE(), INTERVAL -1 DAY), INTERVAL 1 WEEK)  AND Date<CURDATE()

 

That would get the high scores from the last week, starting before the current day. So you don't get the highest scores from today also, just last week.

 

So that would select the highscore change from a week ago to yesterday?

 

The highscores work by a username, then a set of values for each username, stored in a date table which is:

 

date (primary) username (foreign) highscore1 highscore2 highscore3

 

would this select the change from yesterday to today?

 

SELECT * FROM HighScores WHERE Date>=DATE_SUB(DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND Date<CURDATE()

[Jabop]

That query would select the records within the past week which are not today.

[andrewgarn]

ok, slightly confused now.

 

Would it be easier just to locate the date (minusing a week from current date), then retrieve all the values.

 

retrieve the values from todays date

 

then do todays date values - previous values -> echo on screen the change

 

 

[Jabop]

I guess I'm confused now. Please explain in greater detail what you're trying to accomplish.

[andrewgarn]

Sorry I think i'm confusing myself.

 

Functions of website:

 

<> Users sign up to the website

<> Each day the php page takes each user, locates their highscores, retrieves them and saves them to the mysql database with todays date. (this will be achieved with a php script + cron)

 

<>Tracking - users can view their highscore progression by looking:

 

change in highscores over:

 

the current week

last week

the current month

last month

the current year

last year

 

This will be expanded into graphs showing highscore progress once i make the tracking work.

 

EDIT: this tutorial explains how to find out the date a week/day ago: http://www.tutorialparadise.com/watchtutorial.php?tid=514