auto reset a mysql field

[ziggy1621]

hello all

I've got a field named hoursPurchased and hoursRemaining in a db setup to keep track of monthly contracts. I need to reset hoursRemaining to hoursPurchased on the 1st of each month. Can someone point me to a good tutorial for the script for that if you know of one. I know about cron, but don't know how to tell it to reset for each row in the database.

thanks in advance
david

[Inigoesdr]

SQL:
UPDATE `table` SET `hoursRemaining` = `hoursPurchased` WHERE 1
Lookup some tutorials for running PHP scripts with cron.

[aedrin]

This sounds like a design flaw.

You shouldn't be storing such a number. It should be calculated. For example, to get the hoursPurchased/Remaining, just add up all hours purchased for the current month.

[ziggy1621]

This sounds like a design flaw.

You shouldn't be storing such a number. It should be calculated. For example, to get the hoursPurchased/Remaining, just add up all hours purchased for the current month.

they are purchased before hand. So i'll have a number like 10 that is contracted for 12 months, so it won't change (hoursPurchased). But hoursRemaining works off a function that deducts every time work is performed for that client, so hoursRemaining starts at 10 on the 1st of every month and works down during the month. So i need it to restart at 10 on the 1st.

[ziggy1621]

SQL:
UPDATE `table` SET `hoursRemaining` = `hoursPurchased` WHERE 1
Lookup some tutorials for running PHP scripts with cron.

makes sense... thanks i'll give it a run

[aedrin]

But hoursRemaining works off a function that deducts every time work is performed for that client

Ouch.

Like I said before, don't store calculations in a database. Use its abilities to calculate the result based on what is in the database.

You should be storing an item for every work item and include an hours worked column.

[ziggy1621]

SQL:
UPDATE `table` SET `hoursRemaining` = `hoursPurchased` WHERE 1
Lookup some tutorials for running PHP scripts with cron.

worked like a charm... thanks

[ziggy1621]

Ouch.

Like I said before, don't store calculations in a database. Use its abilities to calculate the result based on what is in the database.

You should be storing an item for every work item and include an hours worked column.

I have a table named businessName created for each business. then a row in a table admin to keep up with hours remaining. when a job is entered into businessName, it takes the hoursUsed and deducts it from the hoursRemaining in admin. so..


$aSQL = "SELECT * FROM admin WHERE businessName='$businessName'";
$hoursRemaining=$aRow["hoursRemaining"];
$hoursLeft=$hoursRemaining - $hoursUsed;
$query1 = "UPDATE admin SET hoursRemaining='$hoursLeft' WHERE businessName='$businessName'";

[aedrin]

I'm not trying to understand how you currently implement it (this I already understood).

I was trying to show you a much better way of storing the information that you require.

[ziggy1621]

I'm not trying to understand how you currently implement it (this I already understood).

I was trying to show you a much better way of storing the information that you require.

I must be missing what you are saying then..

[aedrin]

A database stores information. This information should be clean. And it should not represent a state. You should be able to reconstruct a previous state from the information. But the state itself is not stored. Only information about this state.

That is why one should store transactions. Not results.

Imagine a balance, where we have some positive and negative numbers. And the result is the balance. Why would you store the balance? You store the numbers. Then when you need them, you ask the database to give the result of that balance through a calculation.

[ziggy1621]

A database stores information. This information should be clean. And it should not...

I understand where you are coming from now, but don't you think that would be a bit overkill in this situation. I just have one number that will be changing and resetting.

[aedrin]

but don't you think that would be a bit overkill in this situation.

No, not really. Of course, it would've been a lot less work if you had started this way. But it's up to you whether you want to work with the current situation.

I myself wouldn't want to, but that is because I don't want to spend effort on something that will not work for me.

[ziggy1621]

No, not really. Of course, it would've been a lot less work if you had started this way. But it's up to you whether you want to work with the current situation.

I myself wouldn't want to, but that is because I don't want to spend effort on something that will not work for me.

now i know you are really missing what i'm really after because its working perfectly for me... i've moved on to the next step. But thanks anyway

[aedrin]

When I say that it doesn't work for me, I mean that it's not an effective solution. I was not trying to say that it doesn't function.