What to use, PHP, MYSQL or AJAX?

[mikecronauer]

How would I build a web page similar to an online banking page that has a running account balance, only I want to make each line editable. If a specific line was edited the total would then change up through the top. (transactions are sorted by date order)

How do you break up a sql table in 50 line increments? So it only shows you 50 lines on one page with the ability to click on the previous 50 items, etc etc until you reach the first/oldest transaction?

I can pull back full tables, but breaking them up in sections is hard. I also cant figure out where to do the "running total" math.

If someone could give me some specific functions I could study, or a strategy using the various technologies I would be very very grateful. (I have read beginner books on SQL, Javascript, PHP, AJAX, HTML/CSS and now am reading an advanced book on PHP.)

It seems relatively simple what I am trying to do ...

ID key--Date--Name--CODE--Cleared?--Amount--RunningTotal-- EDIT--SUBMIT
1-50 -> 50-100 (link at bottom of block)

ID Key - this would be primary key, never to be deleted, just made zero if necessary
Date - Creation Date for each record
Name - What a person would want to name each transaction
CODE - A dropdown to code the transaction type
Cleared - YES or NO dropdown to tell if transaction cleared the bank
Amount - Dollar amount typed for a transaction
Running Total - TRICKY ONE- Starting with first/oldest transaction math is done
So an amount could be a positive or negative, and it ignores checks that have
not cleared. Do you store this amount? Do you calc it every time? Use SQL or
PHP or Javascript? What do you do here?
Edit - Edit button if you want to change any of the fields (other than ID)
Submit - This would make the change and load it back to SQL (page s/b refreshed
at this point because running total would have to change)
1-50 -> 50-100 - If 50-100 is clicked then it would show 50-100->101-150

Basically an on line check rec tool, I want to include this on my site. (not a business, I am trying to help people, I dont get any money for what I am trying to set up)

[svilla]

1) Look at the LIMIT keyword in SQL You can specify the number records to retrieve and the starting record number. You can simply add your page size to the start position on a Next request and subtract page size for a Previous request.

2) If you are using the LIMIT for your paging, you can get a seperate SQL statement that SUMs the amount for a specific account. Use this total for your account total on the page.

3) If you are going to let the user change transaction lines and want the account total to reflect the changes, without having to requery the database, then use Javascript to keep you totals adjusted. Remember to subtract the original amount and add back the new amount.

4) If yo have made it this far, make sure that when the user submits the page all of the changes are stored in the Database. Don't forget to add this logic to your Next and Previous functions so the changes are not lost when the user requests another page.

[MooGeek]

Regarding on your title, you should use them all. pages nowadays are AJAX+PHP oriented.

[mschroeder]

How would I build a web page similar to an online banking page that has a running account balance, only I want to make each line editable. If a specific line was edited the total would then change up through the top. (transactions are sorted by date order)

How do you break up a sql table in 50 line increments? So it only shows you 50 lines on one page with the ability to click on the previous 50 items, etc etc until you reach the first/oldest transaction?

- Pagination. Search these forums and you'll find a lot of coverage of the topic.

I can pull back full tables, but breaking them up in sections is hard. I also cant figure out where to do the "running total" math.

- Look up the Mysql SUM() function. Then apply your where clauses so you're only selecting the transactions you want. That will give you a running total. Apply limit clauses in conjunction with your pagination and you would have the running balance on that page of transactions.

If someone could give me some specific functions I could study, or a strategy using the various technologies I would be very very grateful. (I have read beginner books on SQL, Javascript, PHP, AJAX, HTML/CSS and now am reading an advanced book on PHP.)

- There are no specific functions that jump out at me that would make this any easier for you. Get a solid understanding of php and how it interacts with mysql. Make everything work without javascript and ajax first. Then refactor it with ajax and javascript. This will insure it works when the use does not have javascript capabilities, rare but it happens, and it will also help you gain a better understanding of what really needs to be done with ajax to improve the user interface. To often it is applied to things that don't gain anything from the interation.

It seems relatively simple what I am trying to do ...

- Doesn't it always haha

ID key--Date--Name--CODE--Cleared?--Amount--RunningTotal-- EDIT--SUBMIT
1-50 -> 50-100 (link at bottom of block)

ID Key - this would be primary key, never to be deleted, just made zero if necessary

- Primary keys should never be changed. In your database a checkbook would be a row in a table, and then transactions would be rows in another table and joined to a checkbook record.

e.g. Checkbook 1 contains transactions 5, 6, 7, 8 and 11

If you intend to delete transactions from the checkbook then either remove the entire record or have a separate column that flags the row as deleted.

Date - Creation Date for each record
Name - What a person would want to name each transaction
CODE - A dropdown to code the transaction type
Cleared - YES or NO dropdown to tell if transaction cleared the bank
Amount - Dollar amount typed for a transaction
Running Total - TRICKY ONE- Starting with first/oldest transaction math is done
So an amount could be a positive or negative, and it ignores checks that have
not cleared. Do you store this amount? Do you calc it every time? Use SQL or
PHP or Javascript? What do you do here?

- All of the above are possible but each brings its own set of hurdles. If you calculate the total at the database you'll have to execute at least two queries. One that calculates the sum based on the transactions for the entire checkbook (to get the running total) and a second that would return the rows of transactions that satisfy the pagination results.

You could also pull back every row for a checkbook and paginate it via php and handle all the calculations via php, but this is what you have a database server for and this is why it has things like WHERE and LIMIT and functions like SUM()

You could also do the calculations with javascript, EXCEPT you'll only have access to what is contained in the DOM. Ajax would provide access to the server side for pulling totals or updating values on the fly, but this will be the hardest implementation to troubleshoot when you run into a problem.

Edit - Edit button if you want to change any of the fields (other than ID)
Submit - This would make the change and load it back to SQL (page s/b refreshed
at this point because running total would have to change)
1-50 -> 50-100 - If 50-100 is clicked then it would show 50-100->101-150

Basically an on line check rec tool, I want to include this on my site. (not a business, I am trying to help people, I dont get any money for what I am trying to set up)

[Stefano Mtangoo]

not mega-maverick but may I suggest Code Igniter.
It have a lot of classes coded by experts for you to use.
You can use it with Ajax calls too

[mikecronauer]

I really appreciate your response, I am checking out pagination and have made progress. I will post the code here soon and mark as solved .... I would never have searched for that term so your replys is very helpful!

[MooGeek]

No problem! just ask away! people here are very friendly you know ;)