Need some advice about php application

[phpN008]

Hello to all php freaks.

 

I post here because I have some problems with app design. I want to design simple statistical app. I find myself stuck at DB design. I would be very grateful if someone gives me even some hints or simple advice about my problem.

 

i want to make app that actually looks like excel table where user can define how many rows and columns he needs, because not all people needs exact number of rows/columns, and of top of that i need to sum every row separately and every column separately. Statistic is kept at daily basis, and must be transparent, so i should store all those numbers somewhere for possible inspection later, but don't know where (except totals), i don't know where to start with this. 

 

Other things like login, choosing dates and etc. are no problem.

 

so i would like to make app that can do this on picture and that can expand table with new rows and columns if necessary.

 

 

 

 

thanks in advance 

[requinix]

Store column/row coordinates, and only the ones that actually have data. That way there's really no limit on how many rows or columns there are besides the ones you may (arbitrarily) impose yourself.

 

I've never had to tackle the problem of storing different types of data as one logical unit (ie, A2 is "Books" while B2 is 12) but the best solution I know is like

col | row | type | strval     | intval | floatval
----+-----+------+------------+--------+---------
  2 |   1 | str  | staff      |        |
  3 |   1 | str  | students   |        |
  4 |   1 | str  | professors |        |
  1 |   2 | str  | Books      |        |
  2 |   2 | int  |            |     12 |
  3 |   2 | int  |            |      3 |
...

(You'd need to include a reference to the "spreadsheet" each piece of data belongs in.)

 

A SUM would be a matter of

-- column
SELECT SUM(intval + floatval) FROM table WHERE spreadsheet = (wherever) AND col = (column) AND type IN ("int", "float")

-- row
SELECT SUM(intval + floatval) FROM table WHERE spreadsheet = (wherever) AND row = (row) AND type IN ("int", "float")

Formulas would be handled a little differently: you'd store a "formula" somewhere and use that to calculate the cell's value. You could store that value in the same table as the other data but then you'd have to worry about recalculating it if the source numbers changed.

[phpN008]

I get what are you saying and this is giving me some other perspective for possible solutions, I'll listen to your advice and try to solve it on that way!

 

Thanks a lot! =)