Page 1 of 1
Automatic Datebase Updating ?
Posted: Fri Oct 25, 2002 9:34 am
by HUWUWA
Hi Guys,
I'm making a moving average and need to update my database every hour. Basically I just need to run a PHP funtion every hour to do some calculations and manipulate the data.
What is the best way to do this ? I also want to be sure the update function gets called only once per hour.
Do I do a time test when someone hits the PHP page ? Where do I store the last update time variable, in the database ?
I am not sure which route to go. Please help.
Posted: Tue Oct 29, 2002 3:54 am
by rlogin
I assume you are trying to plot a graph of the moving average....
I would suggest the following:
1) It is not a php job, perl / c / java will be your choice depending on the database.
2) Get the script to retrieve data from the database, manipulate it to your requirements and then insert the data into another table....
3) Plot your graph from the second table.
4) Establish a cron job and call it every hour to manipulate the data.
5) If you are updating a different table, you have all your original data and also your moving average data with you....
By having the moving average in a different table, data retrieval and plotting will be fast.
Original data is still lying for microscopic analysis.
By having a cron job, you are delinking any external aid for data updation.
Dont forget to have a log written everytime the cron job succeeds or fails.
Rgds
Posted: Tue Oct 29, 2002 8:12 am
by BDKR
Hi rlogin,
I was just curious about something. I'm am not sure what you meant in number 1. Are you saying that it is NOT a job for PHP? If that's the case, then why?
Just curious?

I have cron two cron jobs that run every evening and update multiple db's in a, I suspect, more simplified fashion, but do it nonetheless. I wrote these jobs in PHP. They append log files at each run in /var/log and even have a debug option.
Anyways, I'm open to hearing what others have to say and you got me curious.
Cheers,
BDKR
Posted: Tue Oct 29, 2002 8:34 am
by rlogin
Hi BDKR,
In the context mentioned:
What is the best way to do this ? I also want to be sure the update function gets called only once per hour.
Do I do a time test when someone hits the PHP page ? Where do I store the last update time variable, in the database ?
If iam depending on a page being hit to do periodic update of database, there is an external dependency. It should be done with a cron job as you mentioned or through a system daemon.
Personally i prefer to use a tool based on the job that needs to be done.
I find PHP best suited to scripting.
In the circumstances, if i had a MySQL / Postgres SQL backend, i would prefer perl::DBI. On the other hand if it was an Oracle / SQL Server backend i would prefer to do it in Java...
Rgds,
Posted: Tue Oct 29, 2002 11:44 am
by m3mn0n
Hello,
I think i might be of some assistance to you. I run an online strategy game, and my database is automatically updated every 20 mins because that is how often i give my players 'turns' to play with. This is the most common function i have seen used for auto DB updating but it should work with your function. What you need to do is setup a page that has the query you wish to run every so often, and setup crontabs to make it be executed by the server whenever you wish. I however did this origonally didnt use this, and when the need for this came, i was completley stuck on how to work it since i did everything the tutorials told me to do and it still didnt work (maybe it's because i'm not a linux kinda guy but oh well...). I got someone to set this up for me, who had prior knowledge of cron and how it worked.
Maybe you will have better luck with the tutorials, but i suggested asking someone who knows what there doing to help you along! Also, do a search for 'cron' on this forum with me as the author name, a while ago i had quite a few replies to a thread i started in the PHP forum.
Cheers!
Posted: Tue Oct 29, 2002 12:41 pm
by HUWUWA
Hi guys,
Basically, I'm making a Top 100 list. I count all the hits for each site from the last 24 hours and rank them. So once an hour I have to update the datebase table by shifting hours 2-24 to 1-23 (discarding the first hour) then setting element 24 to the current hour and then reseting the current hour to start counting again. So it's like a 24-hour moving average.
Also, can I store an array as a table field in MySQL ?
Is that the best way to do it ?
Posted: Wed Oct 30, 2002 1:58 am
by twigletmac
You can't store an array as an array in a table field in MySQL, however, you can use
implode() to turn the array into a string and
explode() to turn it back into an array.
Mac