Table Update

poscribes

I think I'm having mental block today so any help as to how I can achieve my goal greatly appreciated.

Basically speaking what I am trying to do is schedule the execution of a page to update two values calculated from other fields within the same table for the whole table and update the table as such.

The execution part is not a problem it's getting the whole table updated that is. I have the following queries which work as individual elements using the _POST execution method from within a form for updating individual table rows but I want to do a global update of all table rows as a server side scheduled task.

Here's the code for the queries that I use in the form based version.

$query6=" select date_add(creation_date, interval retention_days day) as purge_date from tapes where tape_id='$id'";

$query8=" select purge_date, (to_days(purge_date) - to_days(now())) as days_remaining from tapes where tape_id='$id'";

Now all I need is a way to update the whole table for the purge_date and days_remaining fields using the above type query.

Any ideas?

bastien

well, you can't update with a select statement.

update [tablename] set [fieldname]=[something] [where clause]

would update record(s) matching the where clause

what field(s) are you trying to update?

poscribes

I am trying to do a global update of all rows within the table named tapes for the fields purge_date and days_remaining as identified by the select statement. The select statement is only good for a specific record or row to display the information in a table and then do an update on the data displayed back to the relevant row in the table, however I want to be able to do this for the whole table in one go.

Any help appreciated.

Thanks.

bastien

can you dump your table structure?

Roger_Ramjet

If I understand you, you have a creation date and a retention interval which, together give a purge date. This in turn when compared to today's date gives an interval remaining until puge. Yes?

OK then


$sql1 = "UPDATE tapes SET purge_date = adddate(creation_date,  retension_interval)";
//will calculate the new purge date, retention_inteval must be a number (preferably an integer) of days to be added
$sql2 = "UPDATE tapes SET days_remaning = datediff(purge_date, now())";
//calculates the number of days remaining untill the purge date.

Incidentally, not a good idea to hard-wire the days remaining into your database, you have to update it every day to keep it accurate. Better to calculate this dynamically from the purge_date when you query the data.

bastien

well, his intention, as stated inthe original post was to automate it. so cron wold take care of running it daily

Roger_Ramjet

Still better to calculate at runtime, what if I query the db before the cron job has run today? Just basic good database practice.

poscribes

Thanks!

When you lay it out like that Roger Ramjet it really is much simpler than I first thought.

Thanks Bastien for the cron suggestion, however this application is running on windows so I'll just set up a scheduled task to call the page on a daily basis after the backup has run.

The idea of running it and getting a dynamic update on each visit is worth exploring and I'll certainly do it if the users feel it is warranted.

Thank again!

Roger_Ramjet

It would simply be the date calculation clause in the select query instead of a striaght select field, and you would not be able to tell the difference in the query execution time:

"SELECT datediff(purge_date, now()) as days_remaining "

// instead of

"SELECT days_remaining"

Plus side is that you use less disk space for the table, and that could improve overall performance if it is a large database.

poscribes

Excellent, thanks!

bastien

Originally posted by poscribes
Thanks!

Thanks Bastien for the cron suggestion, however this application is running on windows so I'll just set up a scheduled task to call the page on a daily basis after the backup has run.

umm. its the same thing 😉

poscribes

I know.... just providing the big picture 🙂