tezza42 Posted October 22, 2009 Share Posted October 22, 2009 I would really appreciate any help anyone can offer updating fields in the same table on a monthly basis with Cron and/or PHP, my level is novice. I am on a hostgator shared server running: cpanel version 11.24.5-RELEASE Apache version 2.2.11 (Unix) PHP version 5.2.9 MySQL version 5.1.30 I have a table called stats with the following fields: id visit each time a certain id (page) is visited it inserts a count in the visit field. What I would like to do is at the end of each month at midnight copy the count in the visit field to a month field in the same table and reset the visit field to zero. Also at the end of each year at midnight copy the total for all month fields in a year field and reset the months to zero. So far I have added the following fields after visit field so the table now looks like this... id visit Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2009 2010 2011 I have looked around the internet and found out how to do this manually by running the following SQL query in phpmyadmin: UPDATE stats SET `Jan`=`visit` This inserts the data in the visit field into the month of Jan field. This is ok if I can remember to do this at exactly midnight of each month but I am pretty sure I will forget one or not do it on time. I would like to automate this with a cron job or php which I am usure how to do. I am also unsure of how to set the original field (visit) back to zero. Basically this is what I would like but am sort of stuck. Midnight - Last day in January update Jan field with current visits and reset visit field to zero Midnight - Last day in February update Feb field with current visits and reset visit field to zero Midnight - Last day in March update Mar field with current visits and reset visit field to zero Midnight - Last day in April update Apr field with current visits and reset visit field to zero Midnight - Last day in May update May field with current visits and reset visit field to zero Midnight - Last day in June update Jun field with current visits and reset visit field to zero Midnight - Last day in July update Jul field with current visits and reset visit field to zero Midnight - Last day in August update Aug field with current visits and reset visit field to zero Midnight - Last day in September update Sep field with current visits and reset visit field to zero Midnight - Last day in October update Oct field with current visits and reset visit field to zero Midnight - Last day in November update Nov field with current visits and reset visit field to zero Midnight - Last day in December update Dec field with current visits and reset visit field to zero Midnight - Last day in December 2009 update 2009 field with the total from all months fields Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec BUT NOT RESET month fields to zero instead just overwriting each month as and when... For example: the data in month Jan (which would be the count for January 2009) would be overwritten on the midnight 31st January 2010 with data from the visit field. I hope this makes sense and someone can help me out to run on autopilot! Please remember I'm a novice! Thanks in advance. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 31, 2009 Share Posted October 31, 2009 Forgot about the cron for now.... how might you handle this for a single month? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.