Complicated array situation for free budgeting tool site

[h20boynz]

Hello

 

I am busy trying to setup a free to use budgeting and cashflow tool.  I was recently going through my finances and whilst I found lots of spreadsheets on the subject, I didn't find a website that would allow me to store my data anonymously and access it when and where I felt fit.

 

Bare in mind that I am a bit of a novice so go easy on me...

 

So firstly, I've setup a page to collect 'account' information...that is, income items and expense items. I ask them to enter the amount, the frequency and when the next due date is.

The DB looks like this:

CREATE TABLE IF NOT EXISTS `income_accounts` (
  `inc_acc_ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_ID` int(11) NOT NULL,
  `freq_ID` int(11) NOT NULL,
  `amount` decimal(7,2) NOT NULL,
  `next_due` date NOT NULL,
  `income_typeID` int(11) NOT NULL,
  PRIMARY KEY (`inc_acc_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=105 ;

and the same for expense 'accounts':

CREATE TABLE IF NOT EXISTS `expense_accounts` (
  `exp_acc_ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_ID` int(11) NOT NULL,
  `freq_ID` int(11) NOT NULL,
  `amount` decimal(7,2) NOT NULL,
  `next_due` date NOT NULL,
  `ex_typeID` int(11) NOT NULL,
  PRIMARY KEY (`exp_acc_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

So once I have these entered by the user I should be able to then generate a cashflow forecast.

For example, I give 5 options for the user: '4 weeks' , '8 weeks',  '3 months',  '6 months'  '12 months'. Selecting one of these will then generate a table listing all of the items that apply for each period with a total at the bottom.

Lets take '4 weeks' for an example.

First I need to determine if the 'next_due' value is within the time period selected (4 weeks).  If it is, what frequency is it? If it is every week then each column of the 4 column table created (i.e. 4 weeks/4 columns) will have it in it.  If it is monthly frequency it will be only in the column for which it is due (i.e. column 1 would be todays date to todays date +6, column 2 = todays date + 7 to todays date +13 etc...)

This will go through both the income table and the expense table and once all the items have been checked and added to the page table a further row would be added to total all the items in each column, and give a sum (i.e. sum of incomes less sum of expenses).

For the '6 month' and '12 month' option, I want to display 1 month per column, rather than 1 week...just to not squeeze things up to much...

 

I do not have a great deal of experience with arrays but my instinct tells me that they are the answer....otherwise I guess I could create a temporary DB table?

 

I'm happy to share the code I've written so far if it helps...otherwise any advice on the best way to achieve this would be great.

 

 

[h20boynz]

Anyone?

[gizmola]

It looks to me like you want to take some data, and then make a computation with it.  Some of that data is based on data you have in your database.  If the computation is expensive, you might look into caching results using one of the numerous caching systems, the most widely used being memcached. You could also store some of the computations in session variables. 

 

php arrays are all associative, which means that they are keyed by strings.  You can easily nest arrays inside arrays, so in that way they are quite flexible, and could certainly be the basis for your output.

[h20boynz]

There is a computation component but the part I am struggling with is creating an array with all of the income/expense items to appear in a particular column and then sum these to determine a balance for each column...I've attached a diagram of what I mean.

 

Looking at the table in the attached doc. you will see that in some cases an income or expense is only in one column.  This is because it is a monthly frequency. If the user chose to review 8 weeks worth it would show it twice, in the column corresponding to the correct date range for it.

The trick for me is that this all has to be determined from the info in the table which is the next due date, which won't actually change once entered by the user, and the frequency.

So...the process is to fetch each inc/exp account that, using the next due date from each record and the corresponding frequency, will occur within the next 4 weeks, 8 weeks etc etc.  Then put this into an array and repeat.  This would be repeated for each column I think. (i.e. week 1, week 2 etc).

Then it would be a matter or arranging these in the table in the correct columns and with the correct gaps etc....this is where I am really lost.

 

I hope this all makes sense

 

 

[attachment deleted by admin]

[h20boynz]

I am still looking for help on this if anyone can assist?...