Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Pre-Sales Question - Can the tables calculate entries

Quote Reply
Pre-Sales Question - Can the tables calculate entries
Hello,

If I wanted to calculate entries into the tables...would that be possible? I need to create an in depth statistics system using dbman.

Thanks for your help.

P a i n t b a l l C i t y . c o m
http://www.paintballcity.com
Quote Reply
Re: [Ground Zero] Pre-Sales Question - Can the tables calculate entries In reply to
Hi,

Sorry it took me so long to get back to you, the answer is YES. You can absolutely build the statistic system with a little bit knowledge of Perl programming and database simply by adding a new global sub or a new plugin.

Cheers,

jean(at)Gossamer Threads Inc.
Quote Reply
Re: [jean] Pre-Sales Question - Can the tables calculate entries In reply to
Hi Jean.

I have a similar problem, but on a much simpler scale. I understand how to use globals to perform basic calculations and display the results when a record is displayed, modified, etc. My question, though (and this may demonstrate my near total ignorance of MySQL!), is whether there's a way to set up a particular column so that its value is always the result of some basic calculation from some other column value?

For example - I have a database of members. There is a field called JoinedDate, a field called TerminatedDate, and a field called Duration. I have it set up to enter today's date in JoinedDate every time a record is added. TerminatedDate gets entered by the admin when a member cancels. What I'm trying to figure out how to do, though, is set Duration to always equal either the value of TerminatedDate minus JoinedDate or (when a member is still active) today's date minus JoinedDate. Again, I know how to use template globals to do this for display purposes, but I'm wondering if there's a way to do it directly with the database, whether or not the record is displayed.

Again - I'm sure I'm demonstrating just how little I know about MySQL, but it seems to me that this should be possible.

Any advice would sure be appreciated.

Thanks,
Adam

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Quote Reply
Re: [hennagaijin] Pre-Sales Question - Can the tables calculate entries In reply to
Were you ever able to accomplish what you were inquiring about? I am in the beginning stages of developing a "bank" system for an online game that I run, and the answers to these questions would really help me on my way lol. The game "bank" isn't needing much security, other than what is offered, but the calculations are my first obstacle. This is all very new to me, but I'm of the type that if I have that first push, I can usually plug my way through lol.
Quote Reply
Re: [ArtistikDD] Pre-Sales Question - Can the tables calculate entries In reply to
I've learned a bit about database technology since I wrote that post, so I think I can give you some useful but incomplete information.

The short answer is that no database system can constantly update data, since that would require an infinite number of queries (always checking to see if it needs to be changed and changing it if it does). Not only is that technically impossible, but even an attempt to come close to "constant" updates/calculations would be extremely inefficient.

I don't have any real experience with commercial db systems (Oracle, Sybase, etc.), but my understanding is that they accomplish this with "triggers". Basically, everytime a given action is performed (a record is viewed, for example) another corresponding action is also performed (the update calculation). As far as I can tell, that's how the big boys solve this problem.

MySQL does not have trigger technology, however, so you've got to find a way to work around that. I've used cron jobs with quite a bit of success. Since I only really need my "duration" data to be calculated daily, I wrote a very simple update script that runs by cron at midnight every day and updates all the records. Maybe that's not the most efficient way to accomplish this, but it works perfectly for my limited needs.

Since your bank system will presumably need far more frequent updating, you might need to take a more sophisticated approach. I do have some experience programming basic online accounting systems, which is probably closer to what you're talking about. In that case, I handled the update/calculation issue by building them into the relevant functions/subroutines - sort of a pseudo-trigger. So if you have a subroutine for cash withdrawals, say, you just build into it two queries: one that reduces the balance in the account and one that increases the balance in the wallet.

That approach only works if you're writing a script from scratch, of course. If you're using DBManSQL (that's what this forum is for, after all) then you'd probably need to write a plugin(s) to accomplish the same thing.

Hope that helps.

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Quote Reply
Re: [hennagaijin] Pre-Sales Question - Can the tables calculate entries In reply to
Thank you very much for your response! I have a feeling this project is going to be a long time in the making, so every bit of info I can gather helps. Happy holidays!
Quote Reply
Re: [ArtistikDD] Pre-Sales Question - Can the tables calculate entries In reply to
Hello, today, I just started (researching) on the Point Systems, or Bank style system for my staff members and members, I want to have 3 levels of access, such as:
Account Holder (access only to there account),
Accountant (can view, but not edit accounts and see balances)
Administrator (can view/edit/add/remove/change accounts)

Basicly used for staff to get better access, or more storeage space on the server, and to compeat with other staff to work more and get more "points" in the form of money for example.

I think we can help eachother on the creation of this script. Contact me at
"ceo AT ssiops DOT com" (change "AT" to "@", and "DOT" to "." -this way to stop spam and no to violate forum rules)

If we work together to could get this script gone quickly.