Where to Put Validation and Update Logic? PHP or MySQL

I think I'm suffering from some kind of designer's block, I think. I'm trying to design what I think is a very simple website, but I keep running into barriers in how to implement the related functionality.

Here's the general idea:

A user creates a user account on the site.
The user then can create expense accounts to use in tracking personal expenditures.
The user is able to log personal transactions, itemizing each transaction by assigning a portion or all of the transaction amount to specific expense accounts.

I can't think of a good way to keep track of the daily balances for each account so that the user can look back and see a historical trend of his or her spending.

My current thought on implementation is to have the following tables:

users
accounts
balances
transactions
transaction_amounts

I have a ledger_date field in users that dictates the first date for which each account should have a balance. The trouble I'm having is what to do when the user updates his ledger_date. To succeed, the update must have the following features:

Validate that the new date does not put existing transactions "in the past".
Delete old balances that don't mean anything anymore.

Do I implement the validation and cascaded updates/deletions in JS, PHP or SQL? Sigh... I'm going to play Guilty Gear for a while and come back to this.

The devil is indeed in the details in this type of accounting system. I will take a pot shot at it, but I'm not sure my ideas will match your requirements.

#1: A search for transaction dates before the new "start date" should give a clear go/no go indication. If there is a conflict, you might need more logic to show the user what transactions must be removed/archived to make the change possible.

#2: Not so clear. If the balances are associated with dates, the same logic should apply. If not, perhaps the account itself does not exist?

Probably your best bet for #1 is what SonLight suggested: a search (probably using SELECT COUNT(1)) to see how many ongoing transactions exist before the date.

For #2, a simple DELETE query should suffice if I understand you correctly. Just DELETE all balances dated before the ledger date belonging to the user.

Incidentally, have you ever considered the use of a framework with object-relational capabilities? For example, symfony. I find as well as speeding up development significantly, they tend to really help you organize your thoughts and do things like this. In symfony, for example, you'd achieve #1 by setting up date-related criteria to be queried as part of the form validation, and #2 by overloading the save method for your Users class.

You've probably already got something pretty well established from the sound of things, just thought I'd note the merit of a framework.

Just to add, you asked if you should do it in javascript, php, or MySQL.

I think your question was answered, but don't do validation in javascript alone. It can be helpful to users, but it is easy to circumvent, so it should just be an enhancement, keep the validation server-side.

PHP, MySQL...
ASP +PHP+MySQL Tutorials
what is your php mysql apache installer package
Need some php/mysql dlls
The Basics (php, mysql etc)

What is useful way in this php+mysql problem?
PHP/Mysql - beginner!
A very good PHP MySQL Tutorial
Setup php,mysql and others...
php, mysql and iis5

EASY 40 frih$ IF YOU KNOW PHP+MySQL
Php Mysql security testing
Free PHP/MySQL login script
Php mysql and stuff