November 20, 2006 1:35 PM Subscribe
Can anyone help a n00b with a MySQL (& coldfusion) problem?
posted by juv3nal to computers & internet (12 answers total)
I have a table with a list of transactions by date, so suppose:
(transactionid, userid, transactiontype, transactiondate, balance)
Where balance is the number/volume remaining for that user after the transaction. There are 2 transactiontypes.
I will have seed/initial balances available to me (assume 0 to start if it matters).
I want to graph the total aggregate balance of all users for each day in a given date range, one line for each transactiontype, both on the same chart.
What is the best way (performance-wise) to go about doing this?
I have a couple of implementations which involve a lot of looping + arithmetic in coldfusion but that's a bit of a slog performance-wise so I imagine that pushing more work over to the db would be a good thing here, but my MySQL is not really up to the task so maybe someone out there in AskMe land can help me out? My MySQL experience is limited to pretty much just getting the results I want returned; I have very little understanding of what the performance is like of a subquery vs a temporary table vs coldfusion query of a query. Any help would be appreciated.
Actually, on the coldfusion side, if anyone knows how to get 2 separate groups of "stacks" (seriesplacement="stacked") onto the same chart, that would save me a lot of work, but I have the feeling it's not possible.
Also, please don't hesitate to recommend a graphing package that hooks in easily to coldfusion if you know of one that would be the better way to go here.
Coldfusion MX 7.02 & MySql 4.1 on Apache/Linux if that matters.