edit data after pulling it out from mysql

[mikecronauer]

I am trying to set up a web based tool for my wife and I to use to balance our checkbook together. I am using HTML, Javascript (Ajax), PHP and MYSQL. I have read beginner books on all of these subjects, I can get data out of MYSQL, BUT can't do it the way I want to do it. If someone could point me to a book, or even the name of what I need to study it would be very helpful. The top line will always be input. But as you submit the top line it refreshes the "growing list" beneath it. (kind of like on-line banking, where you can look at 50 or so lines and then go back further if necessary) The catch is within the "growing list" I want to show Bank and book balance running totals and have three buttons next to each transaction ... EDIT DELETE SUBMIT. If edit is clicked, I want to be able to change that line and then resubmit it to MYSQL.

HTML form .... []=input {}=drop down list \/=auto populate ()=button
here is the line...
\date&time/ [transaction name] {type} [positve amt] [negative amt] {Cleared} (SUBMIT)
so once submit is clicked it gets pushed to the top of the running list below
Same line shows, W/running totals \bankbalance/ \bookbalance/ (EDIT) (DELETE) (SUBMIT)

This way we can both input our transactions as we spend money and know at all times how much money is in the account. What do I need to study to be able to program this?

[Graphix]

You are making a list that has:

>> A column with the transaction name
>> A column with the type
>> A column with the positive amount
>> A column with the negative amount
>> A column with the date and time of the transaction
>> A column with a button that onclick shows a form on adding a row
>> A column with a button that onclick shows a form on deleting a row
>> A column with a button that onclick shows a form to edit a row

Do you want the page to be reloaded with every action, or do you want it to automatically reload without the refresh.

If you want the page to reload, you only need to use PHP, MySQL and HTML. Everything is handled by the PHP script, on the reload the PHP script is re-executed and shows HTML depending on what POST/GET data it received. I'd suggest you should read PHP and MySQL for Dummies and XHTML for Dummies.

If you do not want the page to reload, you need to use PHP, MySQL, HTML and JavaScript/AJAX. The forms need to show up by using JavaScript, and when the Delete/Add/Modify button is called, a AJAX call to the PHP-script goes out with the form data and retrieves the new rows after processing the action that is required. This is the more advanced way, but can be the best if you do it right :) . I'd suggest you should read PHP and MySQL for Dummies, XHTML for Dummies and PHP 5 Advanced (which covers AJAX and advanced PHP programming).

Links:

PHP 5 Advanced: http://www.amazon.com/PHP-Advanced-Visual-QuickPro-Guide/dp/0321376013
XHTML for Dummies: http://www.dummies.com/store/product/XHTML-For-Dummies.productCd-0764507516,navId-322467.html
PHP & MySQL for Dummies: http://www.dummies.com/store/product/productCd-0470527587.html

~G

[mikecronauer]

Hi thank you for your response. A couple more questions :) I definately want to use AJAX. There will only be one input line at the top and I want it to auto refresh the "lower list" beneath it. (with the option of editing each line in the lower list... this will be to mark a check as cleared or still outstanding)

What would be better in this situation, to use XML or a TEXT string with the AJAX call back? If XML, how do I get my MYSQL database to produce the XML within a PHP file so I can pull it back into HTML with the AJAX "xmlhttprequest"?

With the "lower list", does each separate line/list element need to be with a form element? So would I call back 50 form elements at a time? I want each transaction to have a live link back into the database.

It seems there are so many types of strategies for moving data ... Is there a specific one I can study that will be the best one to tackle this problem? I can pieces of data back from mysql, but to make one line out of 50 "re editable" so I can push it back has proven to be very difficult.

And finally, it would be nice to have to option of sorting by any one of the fields. What function handles this?

Thanks so much.

[Graphix]

- What would be better in this situation, to use XML or a TEXT string with the AJAX call back?

Well the easiest way to do this is to let the PHP-script that is called with AJAX to just simply return the entire table (<table> etc. etc. </table>. For example, this is the space in which the table needs to popup:

<div id='TableDiv'>
</div>

and then with the ajax-call/refresh function:

... The call ...
   document.getElementById('TableDiv').innerHTML = xmlhttp.ResponseText;
... The rest of the function ...

- With the "lower list", does each separate line/list element need to be with a form element?

The table can also be something like this, where only 1 form is required. The form can not be submitted, else the page will be reloaded. So you need to add an id to your MySQL-table as well, which is used as an argument in the functions for editing and removing. The edit function and remove function also need to have AJAX-calls to a PHP-script that gets an ID as POST/GET - data. A small example (the 1 in the RemoveTransaction and ShowFormTransaction are the ID's):

<form id='ListForm' method='post' onsubmit='return false;'>
<table
<tr>
<td>A Tranaction name..</td>
<td>Some type</td>
<td>68596</td>
<td>-97900</td>
<td>2009-08-19</td>
<td><input type='button' onclick='RemoveTransaction(1)' value='Remove' /></td>
<td><input type='button' onclick='ShowFormEditTransaction(1)' value='Edit' /></td>
</table>
</form>

- It seems there are so many types of strategies for moving data ...

See previous question

- it would be nice to have to option of sorting by any one of the fields. What function handles this?

When you pull the data from the MySQL table you can use SQL to sort the data:

SELECT * FROM transactions ORDER BY transaction_date

~G

[mikecronauer]

Thanks for all your help! I will move in this direction.