[Resolved] Can i do this in MySQL?

[kcgame]

Hi,

Basically i'm having a column, "Income" in my database with value= 300,100,10,90 at this hour.

It means the specified user has 300 cash now and additional 100 in an hour's time, additional 10 in two hours' time and additional 90 in three hours' time.
How can i make it increment to next hours(s) value as follows (not via crontab, i'm testing through a button at the moment);

Next hour value; 400,10,90,0
2 hours later; 410,90,0,0
3 hours later; 500,0,0,0

I'm thinking to extract the data and input to an array before any manipulations(but i have no idea what function can i use🙁 ). Is there any suggestions?

Any help is appreciated. Thanks.

[nemonoman]

This is bad design.

WHat you need is a set of related tables.
Each record in the related table represents an amount and the releasetime

Assuming you have a table 'person', the related amounttable table structure would be something like:
personid (integer ?)
amount (decimal)
releasetime (time)

In your example, if person is ID 1 and NOW() is 20050212000000 (midinght 2 FEB 2005),
your records would say:

1/300/ 20050212000000
1/100/20050212010000
1/10/20050212020000
1/90/20050212030000

available now:

SELECT sum(amount) FROM amounttable
WHERE peronsid=1
AND releasetime <=NOW()

available in 1 hour
SELECT sum(amount) FROM amounttable
WHERE peronsid=1
AND releasetime <=NOW() +INTERVAL 1 HOUR

[kcgame]

Thanks, nemonoman🙂

Using another table combining with the date/time column really lighten my burden alot. Think i've still got more things to learn from MySQL DB:p