Avatar of sasnaktiv
sasnaktiv
Flag for United States of America asked on

How can I calculate values of fields and update the db without leaving or refreshing the PHP page?

How can I calculate values of fields and update the db without leaving or refreshing the PHP page?

Boy do I need help! I've been struggling with this for far too long.
The frustrating thing about it is that I know there's a simple solution because I've done it before -- but I can't find my old code.
So I'm reaching out to guys who know this stuff much better than I do.

I'm merely trying to subtract a value of one field from the value of another field, then inserting the results into a third field, and updating the database -- without leaving or refreshing the page.

Below is a simple example of what it is I'm trying to accomplish.
Any help is greatly appreciated.
Sas

<INPUT type=text readonly='readonly' name='[b]AmountBILLED[/b]' value='[b]$AmountBILLED[/b]'> 
[i]NOTE: The value of field '[b]AmountBILLED[/b]' is harvested from a database [/i]

<INPUT type=text name='[b]AmountPAID[/b]' > 
[i]  The value of field '[b]AmountPAID[/b]' is entered by the end user [/i]

[i]  On Focus or Click of a (CALCULATE button) do the math - subtract the value entered in '[b]AmountPAID[/b]' from the value of '[b]AmountBILLED[/b]' and
 insert that value into the '[b]AmountDUE[/b]' field automatically, and UPDATE the db [/i]

<INPUT type=text readonly='readonly' name='[b]AmountDUE[/b]'  value='[b]$AmountDUE[/b]'>
[i] Another option to updating the value of [b]AmountDUE[/b] is to merely do so as a variable that is not in an <INPUT field. [/i]
[i] Thank the Expert who made my life a little easier  [/i]

Open in new window


PS: I don't know why but the BOLD and ITAL tags which I used for emphasis do not seem to be functioning properly on EE
DatabasesPHP

Avatar of undefined
Last Comment
Chris Stanyon

8/22/2022 - Mon
Jim Riddles

You'll need to use AJAX on the user side, and setup a PHP page on the server to process the update.  You can't use the text formatting in code blocks...they are intended for viewing code, so that is why you see the tags.
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sasnaktiv

ASKER
Thanks Chris,
I'm unfamiliar with AJAX (thought it was a detergent). The same goes for PDO. I use MySQLi.
So I'm not sure how that impacts your PHP script that UPDATES the DB.

But, are we building 3 documents or is this all incorporated into one PHP page?
Sas
Jim Riddles

You need to build at least two documents.  One that gets served to your user to enter the information, and another that injects the results into your database.

PHP is server side only and can not directly interact with your end-user.

In Chris' solution, the first two bits of code would go into a single HTML file that is presented to your users.  The third code sample is a PHP script to handle the AJAX call and return the result.

Regarding MySQLi and PDO, you likely won't need to change much in his code for it to do what you need.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
Thanks Guys,
I'll kick this around and get back to you.
Sas
sasnaktiv

ASKER
Hey Guys,
This is a very easy way to do it. I just have to include a save to db feature.
  <head>
    <script>
      function test(){
        var f1 = document.getElementById("AmountBILLED");
        var f2 = document.getElementById("AmountPAID");
        var f3 = document.getElementById("AmountDUE");
        f3.value = f1.value - f2.value;
      }
    </script>
  </head>
<body>
  AmountBILLED
    <input type="text" name="AmountBILLED" id="AmountBILLED" value="700" ><br />
 AmountPAID
 <input type="text" name="AmountPAID" id="AmountPAID" value="" onKeyUp="test()"><br /><br />
AmountDUE
<input type="text" name="AmountDUE" id="AmountDUE" value="700">
 </body>
</html>

Open in new window

I seem to remember a PHP page where I used if (isset($_POST['save'])) to execute an UPDATE to a database.
Can you guys remind me how to do that?

Thanks for all the help,
Sas
Chris Stanyon

Putting your code inline like that is a very old-school way of doing it. You're better of binding the events directly like I showed in my code above.

You say you need to include a save to db function, but I've already shown you how to do that - you need to make an AJAX request to a PHP script on your server that saves the data to the DB. That script will receive the data you send it in the $_POST array. Now, if you're feeling particularly adventurous, then by all means go ahead and write your own AJAX code, although I'd really suggest you use jQuery's built-in functionality but that's your call.

Also, trying to call a function on a keypress seems like a very inefficient way to do it - if someone types in a Paid amount of 123.50, then your function will fire 6 times.

Is there a particular reason you don't want to follow my code example. It has all the moving parts you've asked for.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
Thanks Chris,
The reason I used (notice past tense) my approach is because it was easier for me to understand. But now I've noticed some problems with it when multiple accounts are on the same page. Basically I'm harvesting a number of accounts from the DB and giving my client the ability to update the AmountsPAID & AmountsDUE for any one of the individual accounts.

So, although I don't understand AJAX at all, and don't understand some of the jargon (eg binding), I'm going to turn back to your input and give it another go. That's going to take me a while. Now as far as my code being "Old-School" — what do you expect from an old guy?
Thanks for the help. It is indeed appreciated,
Sas
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
Ok Chris, the following is exactly what I did. Copied your code and placed it it two documents—this one (named 'EE_2.php') and 'calc.php'.
The result was two input text fields (Amount Billed & Amount Paid) followed by the label (Amount Due) and a 'Calculate' button.
The 'Amount Billed' text field indicates '500'. The 'Amount Paid' text field shows no value. Entering a number in the 'Amount Paid' field does nothing. Clicking the button does not change anything.
I notice that there is no input text field (Amount Due) and there is no $AmountDUE in the document. Does that mean there is no place to feature any results of the calculation?
The 'calc.php' is identical to yours except that I noted out the MySQLi code. When I set 'calc.php'  to target my actual MySQLi DB there was no effect on the EE_2.php page.
So, what am I doing wrong?
Thanks for the help Chris.
Sas
<?php
error_reporting(E_ALL);
ini_set('display_errors','On');

echo"
<head>
<script>
$(function() {
    $('.calculate').click(function() {
        let wrapper = $(this).closest('.account'); // get the isolated wrapper
        let id = $(this).data('id'); // get the ID from the button
        let data = {
            id: id,
            amountBilled : $('input[name='AmountBILLED']', wrapper).val(),
            amountPaid : $('input[name='AmountPAID']', wrapper).val()
        };

        $.ajax({
            url : 'calc.php',
            method : 'post',
            data : data,
            dataType : 'json',
        }).done(function(response) {
            $('.amountDue', wrapper).html(response.due);
        });
    });
});
</script>
</head>

<body>
<div class='account'>
    <label>Amount Billed</label>
    <input type='text' name='AmountBILLED' value='500' readonly> 

    <label>Amount Paid</label>
    <input type='text' name='AmountPAID'>

    <label>Amount Due</label>
    <span class='amountDue'>
</span>

    <button class='calculate' data-id=1>Calculate</button>
</div>
</body>
";

Open in new window

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
Hi Chris,
I used your code completely unchanged with no result.
The cPanel Error Reported: " PHP Parse error:  syntax error, unexpected '?' in /home/xxxxxx/xxxxx/xxxx/xxx/calc.php on line 2: "

So, it appears that part of the problem is in the 'calc.php' document.

Now I have another question for you about using PHP vs HTML (Your advice: "you're not running this particular page as a PHP script - it's just standard HTML with some Javascript.")

But I need to make it a PHP page simply because many of the $values are harvested from the DB.
So (<input type='text' name='AmountBILLED' value='500' readonly> ) is just one example.
The value='500'  would actually be value='$AmountBILLED.'

What do you think about these two issues?
Thanks,
Sas
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
Chris, don't you ever take a day off?
But thanks for getting back to me so quickly.
I'll kick this around again and let you know what happens.
Thanks again,
and you can take the rest of the day off!
Chris Stanyon

Haha - I really need a day off
sasnaktiv

ASKER
Okay Chris,
You nailed it. Now I'm going to attempt to incorporate your solution into my actual page.
Fingers crossed,
Sas
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
Chris, is this a problem with versions of PHP?
I'm now running into another problem with calc.php.
The following line of code:
$stmt = $db->prepare("UPDATE I changed it to my Table SET billed = ?, paid = ?, due = ? WHERE id = ?");

Generates the following error:
PHP Fatal error:  Call to a member function prepare() on null in /home/***/****//calc.php

Is it because I'm using a PHP 5 version instead of PHP 7?
Chris Stanyon

Hey Sas,

The error you're getting shouldn't have anything to do with the version of PHP, although v5 is beyond end-of-life, so you might want to speak with your hosting provider to get that updated. You should really be on at least v7.3

It looks like the error is down to you not having a connection to your DB.

In my code, I skipped over that because I thought you'd already got that covered - I just presumed you would make a connection and store it in $db.

Here's the bit that I missed off:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
 
$hostname = 'localhost';
$username = 'username';
$password = 'password';
$database = 'yourDb';

try {
    $db = new mysqli($hostname, $username, $password, $database);
}
catch (Exception $e)
{
    die("Could not connect to the DB");
}

Open in new window

Make sure you've got that at the start of your calc.php (or put it in another file and include it. Then you'll be able to call the right methods on the $db object.
sasnaktiv

ASKER
Got it, Chris. The problem was with $db.
I include my contact document which uses $con & $link etc., but no $db.
Once I added the $db to the contact doc -- a miracle happened!

Now I've got to incorporate the code into my actual page.
We'll see what I screw up next.

I'll let you know what happens.
Sas
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Chris Stanyon

No worries. You can use whatever variable name you want for the connection (within reason) - as long as you then use that throughout your code. I always just use $db, but others use things like $con, $conn, $mysql etc. Personal choice, I guess :)
sasnaktiv

ASKER
Hi Chris,
It works! Kind of...
Once I separate the "amountPAID field and the "amountDUE" fields, and place them into different <TABLE><TD CELLS — it fails!
That's odd.
Why?
Chris Stanyon

Hmmm. Not sure.  Post up your code for the html/jquery and the php script and I'll take a look. Sure it's something simple
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
It's always something simple, Chris,
Thanks for the help on this one... I'm sure there will be more.
I used your html script and placed it into a table. It fails to function even when treated as simple as that.
Below is the html page:
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>Chris Stanyon // EE - 29171895</title>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
        <script type="text/javascript">
        $(function() {
            $('.calculate').click(function() {
                let wrapper = $(this).closest('.account'); // get the isolated wrapper
                let id = $(this).data('id'); // get the ID from the button
                let data = {
                    id: id,
                    amountBilled : $('input[name="AmountBILLED"]', wrapper).val(),
                    amountPaid : $('input[name="AmountPAID"]', wrapper).val()
                };

                $.ajax({
                    url : 'calc.php',
                    method : 'post',
                    data : data,
                    dataType : 'json',
                }).done(function(response) {
                    $('.amountDue', wrapper).html(response.due);
                });
            });
        });
        </script>
    </head>
    <body>
<table border=1 width=250px>
<tr><td>
        <div class='account'>
            <label>Amount Billed</label> 
            </td><td>
            <input type='text' name='AmountBILLED' value='500' readonly> 
</td></tr><tr><td>
            <label>Amount Paid</label>
            </td><td>
            <input type='text' name='AmountPAID'>
</td></tr><tr><td>
            <label>Amount Due</label> 
            </td><td>
            <span class='amountDue'></span>

            <button class='calculate' data-id=1>Calculate</button>
</td></tr></table>        </div>

    </body>
</html>

Open in new window

And below is the calc.php
<?php
$billed = isset($_POST['amountBilled']) ? (float)$_POST['amountBilled'] : 0;
$paid = isset($_POST['amountPaid']) ? (float)$_POST['amountPaid'] : 0;
$id = isset($_POST['id']) ? $_POST['id'] : 0;

$data = [
    'billed' => $billed,
    'paid' => $paid,
    'due' => $billed - $paid,
    'id' => $id,
    ];

$TableName="INVOICES";

// mysqli way of doing things
include "../_db/db_XXXXXXXX.php";

$stmt = $db->prepare("UPDATE $TableName SET AmountBILLED = ?, AmountPAID = ?, AmountDUE = ? WHERE id = ?");
$stmt->bind_param("dddi", $data['billed'], $data['paid'], $data['due'], $data['id']);
$stmt->execute();

echo json_encode($data);
?>

Open in new window

Chris Stanyon

Ahh right OK.

Your HTML is slightly off. You've got the <div class="wrapper"> inside the <td> but the closing </div> outside of the <table>. HTML is generally hierarchical, meaning you tend to have opening and closing tags, and these need to match. Effectively,  what you have is this

table
    row
        cell
            accountWrapper
        /cell
    /row
/table
/accountWrapper

Open in new window

What you need is

accountWrapper
    table
        row
            cell
            /cell
        /row
    /table
/accountWrapper

Open in new window

This way, everything is nicely nested. Just move your <div class="account"> to before your table and you'll be good to go:

<div class="account">
    <table>
    ...
    </table>
</div>

Open in new window

Bonus Tip: Drop the border="1" and width="250px" from your table. That;s old-school and not supported in HTML5. You'd set that using CSS instead of HTML attributes.
sasnaktiv

ASKER
Well Chris, you fixed it.
Now I'm having a problem calculating the numbers.
When subtracting from (1,000.00 or 1000.00) the result is .99
It may have something to do with money format.
So I would probably have to create different fields -- one for the actual calculations and another that presents the number in money format.

I'll let you know how that works out.
Thanks,
Sas
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
sasnaktiv

ASKER
Oddly though, the problem does not occur with any values less than 1000!
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
That fixed it, Chris!
But I noticed something else that's fishy.
Let me see if I can remedy it myself, and if not -- well you will find out. It's not necessarily from your work.
Thanks for everything,
Sas
sasnaktiv

ASKER
Here's the fishy thing Chris.
First I apologize for the length of this note, but it's not something that's easy to articulate.
I think it's a logic problem with "AmountDUE".

When the page first opens it presents with the following fields AmountBILLEDAmountPAID. It should also feature the AmountDUE field, but it doesn't.
So we don't know how much should be paid until the calculation is made (subtracting the AmountPAID from AmountBILLED).
That's when the results are shown in the AmountDUE field, and recorded in the DB as expected.

Currently when I return to the page for a new session it starts all over again (subtracting the AmountPAID from AmountBILLED).
It's just keeps going around in circles. Am I clear so far?
It should actually subtract the AmountPAID from AmountDUE — not from AmountBILLED. That's an easy fix.

The problem is that the AmountDUE field should always be visible, and present the AmountDUE value that was recorded in the DB.
But how do we pull that off when there is no real AmountDUE input field? I'm stuck!

Any ideas, Chris?


Just one more issue.
I want to also populate the DB with the InvoiceNUMBER and the PaymentDATE.
I've tried several ways with no success. So I thought that I had some understanding of your code but I obviously don't.
Below is an example of one of my FAILURES. How am I screwing up, Chris?
// ~~~~~~~~~~~~~~~~~~~~~~ START ReviewINVOICES.php ~~~~~~~~~~~~~~~~ //
// ~~~~~~~~~~~~~~~~~~~~~~~ javascript ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
        <script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
        <script type="text/javascript">
        $(function() {
            $('.calculate').click(function() {
                let wrapper = $(this).closest('.account'); // get the isolated wrapper
                let id = $(this).data('id'); // get the ID from the button
                let data = {
                    id: id,
                    amountBilled : $('input[name="AmountBILLED"]', wrapper).val(),
                    amountPaid : $('input[name="AmountPAID"]', wrapper).val(),
                    PaymentDATE : $('input[name="InvoiceNUMBER"]', wrapper).val(),
                    PaymentDATE : $('input[name="PaymentDATE"]', wrapper).val()
                };

                $.ajax({
                    url : 'calc.php',
                    method : 'post',
                    data : data,
                    dataType : 'json',
                }).done(function(response) {
                    $('.amountDue', wrapper).html(response.due);
                });
            });
        });
        </script>
// ~~~~~~~~~~~~~~~~~~~~~~~ CREATE PAYMENT DATE ~~~~~~~~~~~~~~~~~~ //
date_default_timezone_set('America/New_York');
$PaymentDATE = date('m/d/Y');
<input type='HIDDEN'  name='PaymentDATE' id='PaymentDATE' value='$PaymentDATE'> <BR/>
// ~~~~~~~~~~~~~~~~~~~~~~~END CREATE PAYMENT DATE ~~~~~~~~~~~~~~~~ //
// ~~~~~~~~~~~~~~~~~~~~~~ END ReviewINVOICES.php ~~~~~~~~~~~~~~~~~ //

// ~~~~~~~~~~~~~~~~~ START calc.php ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
$paid = isset($_POST['amountPaid']) ? (float)$_POST['amountPaid'] : 0;
$id = isset($_POST['id']) ? $_POST['id'] : 0;
$PaymentDATE = isset($_POST['PaymentDATE']) ? $_POST['PaymentDATE'] : 0;
$InvoiceNUMBER = isset($_POST['InvoiceNUMBER']) ? $_POST['InvoiceNUMBER'] : 0;
$billed = isset($_POST['amountBilled']) ? filter_var($_POST['amountBilled'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION) : 0;

$data = [
    'billed' => $billed,
    'paid' => $paid,
    'due' => $billed - $paid,
    'id' => $id,
    'InvoiceNUMBER' => $InvoiceNUMBER,
    'PaymentDATE' => $PaymentDATE,
    ];

include "../_db/dbConnect.php";
$stmt = $db->prepare("UPDATE $TableName SET AmountBILLED = ?, AmountPAID = ?, AmountDUE = ?, InvoiceNUMBER = ?, PaymentDATE = ? WHERE id = ?");
$stmt->bind_param("dddi", $data['billed'], $data['paid'], $data['due'], $data['InvoiceNUMBER'], $data['PaymentDATE'], $data['id']);  
$stmt->execute();

echo json_encode($data);
// ~~~~~~~~~~~~~~~~~ END calc.php ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Stanyon

Hey Sas,

How you go about this will depend in part on how your database is setup in the first place. so you may want to explain that in more detail.

If I were coding this, I would have 2 database tables - 1 to record the Invoice (Invoice Num / Invoice Date / Amount Billed). I would then have another table to record the payment transactions against the invoice (Invoice Num / Payment Date / Amount Paid). This would give you a one-to-many relationship - 1 invoice can have many payments. You could then run a simple Join query to select the Invoice Num / Amount Billed / Sum of Amount Paid). From that you could display a page to your users showing the Amount Billed / Amount Paid and Amount Due. You could include a form <input> on this form allowing them to enter a value of the amount they want to pay, and when they submit that form, you would insert a new record into the transactions table.  This way, the only things your form would need to POST to the server would be the Invoice Number and the new Amount Paid.

Regarding your PHP code, your prepared statement is slightly wrong. With a prepared statement, we use a question mark in place of data, and then we bind those question marks to variables (in order). The number of variables and their type that you bind to has to match the number of place holders, so if we take a look at this:

$stmt = $db->prepare("UPDATE $TableName SET AmountBILLED = ?, AmountPAID = ?, AmountDUE = ?, InvoiceNUMBER = ?, PaymentDATE = ? WHERE id = ?");

Open in new window

you'll see that we have 6 placeholders. When we bind the parameters, we need to tell the system what data types these 6 place holder are expecting. We do this with the first argument of the bind_param method, so :

$stmt->bind_param("dddisi", $data['billed'], $data['paid'], $data['due'], $data['InvoiceNUMBER'], $data['PaymentDATE'], $data['id']);

Open in new window

The first parameter here tells the query that we're populating 6 placeholders. The letters used indicate the data type or each variable : d = double (number with decimals), i = integer (whole numbers), s = string (non-numeric text).
sasnaktiv

ASKER
Well that was enlightening, Chris.
Are there any other placeholders I should know about in addition to:
d = double (number with decimals), i = integer (whole numbers), s = string (non-numeric text?

What about (text and number with decimals) such as Feb 17, 2020?
Is there a way to use "varchar" and couldn't varchar be used for everything?

Also, I don't understand how a change in my BD structure can make AmountDUE  visible.
After all, "<span class='amountDue'></span>" is not really an <input field. Is it?

Thanks for the education and your patience, Chris.
Sas
Chris Stanyon

Hey Sas,

In MySQLi, bind_param has 4 types available to it - the 3 I've mentioned plus blob (Binary Large Objects, such as files).

The types available don't directly map to the field types in MySQLi as that's not what they're used for. They're used simply to tell your MySQL query how to escape the data, which prevents problems with SQL injection attacks and having to escape data such as quotes etc.

Basically, if it's not a number (integer or float), use string. For your date example (Feb 17, 2020), use string (s).

As for the DB side of things, that's really a design choice. You say the AmountDue is not an input field, but surely you wouldn't want it to be. The user won't ever be asked to enter the Amount Due (will they??).

The change in the DB structure was just my take on it. You may already have a structure that suits you, but from my understanding, a user can make several payments against an AmountBilled. The sum of those (several) payments, subtracted from the AmountBilled would give the AmountDue, no?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
sasnaktiv

ASKER
Hi Chris,
I'm not so sure I've been clear on what it is I'm trying to accomplish. So I created a screen dump to help. I only want to show the results from calc.php in the AmountDUE input field. I do not want to show the <span class='amountDue'></span> results on the page.  
I hope this screen dump demonstrates what I'm looking for.
Thanks,
And I appreciate the fact that you are not tired of me — YET!
Sas
Updating AmountDUE
Chris Stanyon

Haha. No worries. We'll get there :)

The point I was making about the form is this. As I understand it, the user sees three values when they first load the page - AmountBilled, AmountPaid and AmountDue. Only the AmountPaid actually needs to be a UserInput field - the other two fields aren't editable by the user (or it's my understanding that they sholdn't be editable!). All three of these data values are pulled from the Database and displayed to the user (2 of them in a <p> tag or a <span> tag so they just display but can't be edited, and 1 of them (AmountPaid) in an <input> tag so that it can be edited by the user).

When the user clicks on the Calculate button, your javascript then sends the AmountPaid info back to the server. Your server stores this in the Database, re-calculates how much is now Due, and returns that value to the your page. You then use that value to update the <p> or the <span> with the new AmountDue value.  Because you've put the AmountDue value back into the <p> or <span> tag, the user will see the new value, but they won't be able to edit it.

If you'd rather display the readonly data in <input> fields instead of a <p>/<span> tag, then you can easily do that, but with this, the user can easily edit the AmountBilled and AmountDue fields, which feels a little wrong to me.

Hope that makes sense.
sasnaktiv

ASKER
Yeah Chris,
That kind of makes sense. I think I understand the process to update the <p> or the <span>. I will give it a try, and then I'll find out if I really do understand it. Wish me luck!

And
Yes you are right with "Only the AmountPaid actually needs to be a UserInput field - the other two fields aren't editable"

Thanks again,
Sas
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
I'm ready to shoot myself Chris.
I've been distracted for couple of days by paying jobs (this ain't one of them). And I still have not mastered this problem. I haven't given up.
But now there also seems to be a problem with simple arithmetic!
I thought that 496.00 - 6 = 490.00 or that 209.00 - 9 = 200.00
But according to the results I get –– I'm wrong!
I guess Sister Edna didn't beat me hard enough when I was in grammar school.
Arithmetic Errors
Chris Stanyon

Hmmm. Without seeing your code, I can't even guess what's going on. If I can see your HTML/jQuery page as well as your PHP script, I may be able to shed some light on it

* For me, it was a board rubber to head from 20 paces :)
sasnaktiv

ASKER
For me Chris it was not artillery from the blackboard, it was hand-to-hand combat.
Here's the code (stripped down for clarity of course). Plus, I have another question at the bottom.
    
 <!-- ~~~~~~~JAVASCRIPT~~~~~~~~~~~~~~~~~~~~~~~~ -->
    <script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
        <script type="text/javascript">
        $(function() {
            $('.calculate').click(function() {
                let wrapper = $(this).closest('.account'); // get the isolated wrapper
                let id = $(this).data('id'); // get the ID from the button
                let data = {
                    id: id,
                    PaymentDATE : $('input[name="PaymentDATE"]', wrapper).val(),
                    AmountDUE : $('input[name="AmountDUE"]', wrapper).val(),
                    LastBALANCE : $('input[name="LastBALANCE"]', wrapper).val(),
                    amountPaid : $('input[name="AmountPAID"]', wrapper).val()
                };

                $.ajax({
                    url : 'calc.php',
                    method : 'post',
                    data : data,
                    dataType : 'json',
                }).done(function(response) {
                    $('.amountDue', wrapper).html(response.due);
                });
            });
        });
        </script>


 <!-- ~~~~~~~CURRENT BALANCE~~~~~~~~~~~~~~~~~~~~~~~~ -->
<table border=1 width=303><tr>
<td valign=top colspan=1 style='border: 0px solid {$Border}; background-color: {$BG};color:$Border; font-size:9px;line-height:15px;font-family:Verdana, Arial;font-weight:bold;width:111px;'>
<!--td#: 4a<br>-->
CURRENT BALANCE: 
      </td>
      <td valign=top colspan=2 style='border: 0px solid {$Border}; background-color: {$BG};color:$Border; font-size:9px;line-height:15px;font-family:Verdana, Arial;font-weight:normal;width:202px;'>
<!--td#: 4b<br>-->
<!--..............LastBALANCE ............-->
<span style='font-size:13px;font-weight:normal;color:#000000;'>&nbsp;$</span>
<input type='text'  name='LastBALANCE' id='LastBALANCE' value='$LastBALANCE' readonly
style='width:101;border: 0px solid {$Border}; background-color: {$BG};'> <!-- Calculate using LastBALANCE instead of AmountBILLED  -->
      </td>
    </tr>
    <tr>
 <!-- ~~~~~~~TODAY'S PAYMENT~~~~~~~~~~~~~~~~~~~~~~~~~~ -->
             <td valign=top colspan=1 style='border: 0px solid {$Border}; background-color: {$BG};color:$Border; font-size:9px;line-height:15px;font-family:Verdana, Arial;font-weight:bold;'>
<!--td#: 5a<br>-->
TODAY'S PAYMENT:
      </td>
            <td valign=top colspan=2 style='border: 0px solid {$Border}; background-color: {$BG};color:$Border; font-size:9px;line-height:15px;font-family:Verdana, Arial;font-weight:normal;width:202px;'>
<!--td#: 5b<br>-->
<span style='font-size:13px;font-weight:normal;color:#000000;'>-$</span>            
            <input type='text' name='AmountPAID' 
             style='width:101;border: 2px solid {$AmountPAID_BORDER}; border-bottom: 2px solid; background-color:#fee404;'>
      </td>
    </tr>
    <tr>
        <!-- ~~~~~~~BALANCE DUE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -->
      </td>
    </tr>
    <tr>
            <td valign=top colspan=1 style='border: 0px solid {$Border}; background-color: {$BG};color:$Border; font-size:9px;line-height:15px;font-family:Verdana, Arial;font-weight:bold;'>
<!--td#: 6a<br>-->
BALANCE DUE:
      </td>
            <td valign=top colspan=2 style='border: 0px solid {$Border}; background-color: {$BG};color:$Border; font-size:9px;line-height:15px;font-family:Verdana, Arial;font-weight:normal;width:202px;'>
<!--td#: 6b<br>-->
 <div class='amountDue' style='font-size:13px;font-weight:bold;color:#EE0000;'>
$           <span  class='amountDue'></span></div>
<BR/><BR/>
        <button class='calculate' data-id=$ID> CALCULATE AMOUNT DUE $ID</button>
        </div> 
      </td>
    </tr>
</table>
";?>

Open in new window

calc.php
<?php
$paid = isset($_POST['amountPaid']) ? (float)$_POST['amountPaid'] : 0;
$id = isset($_POST['id']) ? $_POST['id'] : 0;
$PaymentDATE = isset($_POST['PaymentDATE']) ? $_POST['PaymentDATE'] : 0;

$LastBALANCE = isset($_POST['LastBALANCE']) ? filter_var($_POST['LastBALANCE'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION) : 0;
$AmountDUE = isset($_POST['AmountDUE']) ? filter_var($_POST['AmountDUE'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION) : 0;

$data = [
    'billed' => $billed,
    'paid' => $paid,
    'due' => $AmountDUE - $paid,
    'id' => $id,
    'PaymentDATE' => $PaymentDATE,
    ];

$TableName="INVOICES";

include "../_db/db_XXXXXXXX.php";
   
$stmt = $db->prepare("UPDATE $TableName SET  AmountPAID = ?, AmountDUE = ? , LastBALANCE = ? , PaymentDATE = ? WHERE id = ?");
$stmt->bind_param("dddsi", $data['paid'], $data['due'],  $data['due'], $data['PaymentDATE'], $data['id']);
$stmt->execute();

echo json_encode($data);
?>

Open in new window

Now Chris, (as usual) there's one more thing; is there a way to configure <span  class='amountDue'></span> to money_format?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Chris Stanyon

Haha :)

Right. I still think you have some problems with the way you're setting up your database. You seem to have one table that contains AmountPAID, AmountDUE, LastBalance, PaymentDate and an ID.

If someone makes more that one payment, how do you plan on recording all the payments. As it stands, you can only ever record one payment against an Invoice. So if I pay £10 off of Invoice # 1 and then I pay another £20 off of Invoice #1, you're only ever going to record the second payment - you will have no record of me ever paying the first £10. This is the reason I suggested having a one-to-many relationship between an Invoice and the Payments.  If you only ever intend to record the details of the last payment made, then you might get away with a table structure like this.

Also, I don't really understand what the meaning of LastBalance and AmountDue is all about - surely they're the same figure. If the LastBalance is £100, then the AmountDue is £100. Maybe I'm missing something.

In your calc.php page, you're still using the float cast to get the value of Paid, when you should be using the filter_var method - the same as you are for LastBalance, AmountDue.

As I understand it (maybe I'm wrong). You have a Database record that contains the Invoice Details. This record contains an Invoice Number, Amount Billed and Invoice Date that won't change. You then store a value indicating the AmountDue. At the beginning, this is going to be exactly the same as the AmountBilled. Then a user can make a payment. You need to then store the Payment Date on that record, as well as re-calculate the AmountDue. This new amount due will be the current AmountDue minus the AmountPaid. So the only parts of the record that need to be updated when a user makes a payment is the AmountDue and the PaymentDate. Is that understanding correct or is there something I'm missing. Let me know what columns you have in your DB table, as that may help me understand what you're trying to do.

And as for formatting the amountDue to money format - yeah ... that's straight forward enough, but I'd recommend getting your data correct first.
sasnaktiv

ASKER
This explains why I got an oak ruler across my knuckles so often.
The problem was I was creating the math with     'due' => $AmountDUE - $paid,
When it should have been:     'due' => $LastBALANCE - $paid,

As far as the DB goes, I have no intention of recording transactions with this application so the DB does not need to be more complex. I'm keeping that very simple.

Now how can I configure <span  class='amountDue'></span> to money_format?
Chris Stanyon

OK. No worries.

For the money format, you can use the number_format function to format your value to 2 decimal places. If you want a currency symbol you can either add it in your javascript or in the PH P script:

'due' => number_format($LastBALANCE - $paid, 2),

or

'due' => "£" . number_format($LastBALANCE - $paid, 2),

Open in new window

That will format the due amount to 2 decimal places (the second example will prefix the GBP symbol).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
Thanks Chris,
What I'm trying to do is add a '$' or in your case '£' to the AmountDUE result.
But that destroys the calculation capabilities altogether. Because it can't subtract or add when the '$'  is included in the calculation.
You thought this was going to be easy, didn't you?
Sas
Chris Stanyon

Haha. Easy is a very relative term :)

I still can't see why you'd be wanting to send the AmountDue to the server for calculation. That just doesn't make any sense. Surely the AmountDue is the response from the server. In order to do that calculation, don't you just need to send the Balance and AmountPaid (or even better - just send the AmountPaid - you already have the balance in your DB). You can then use those 2 values to do the calculation and send back whatever you like as the AmountDue - it's only being used for displaying something to the user.

$amountDue = BALANCE - PAID;
echo "Your current balance is $" . number_format($amountDue, 2);

Open in new window

If you send the AmountDue to the server, what's to stop a user changing it themselves to $1.00 before clicking on the calculate button.
sasnaktiv

ASKER
Hi Chris,
As far as the end user having the capability to change the AmountDUE goes. They can't because it's either in a "readonly' field or a <span></span> . The only item that can be changed is the AmountPAID. Also access and use of this page is only made available to the bookkeeper or administrator -- customers never see it. It's for internal use only.

Now, returning to the final challenge at hand -- and I do indeed hope it's the last one.EE_Dollars.jpgNotice in the included image that before a payment is made the Balance Due includes a '$' sign.
But once the calculation is made the contents of the <span>is replaced with the correct figure but the '$' is lost as well as the decimal '.00' I guess that's because it's not returning from the server or altered in this php document in money-format.

And when I add the  '$' or  '£' by including the following line of code to the calc.php, the calculation fails completely.
   'due' => "£" . number_format($LastBALANCE - $paid, 2),
This approach kills the calculation because it includes a '$' or  '£' .

Also, I don't understand how or where to apply your suggestion:
$amountDue = BALANCE - PAID;
echo "Your current balance is $" . number_format($amountDue, 2);


Thanks for all your help Chris. I do appreciate it immensely,
Sas
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
sasnaktiv

ASKER
Maybe I should have mentioned this, Chris.
The following is where I'm presenting the 'amountDUE' in the php document.
 
<div class='amountDue' style='font-size:13px;font-weight:bold;color:#EE0000;'>
$  $LastBALANCE         <span  class='amountDue'> </span></div>

Open in new window

So I guess that's where I need to change class='amountDue' to money-format — yes, no???
Chris Stanyon

OK. No worries. Just so you're aware, setting an input to readonly, doesn't stop it from being edited by someone who really wants to!

I think the point I'm struggling to get across is that the Amount DUE is NEVER used in a calculation. You have an CurrentBalance and you have an AmountPaid - they're the only 2 figures you need to do the calculation. Once you've done the calculation, you then have a result. This result is only ever used to display to the user (it's NEVER used in a calculation). Because it's only used to display to the user (and NEVER used in a calculation), then you can set it to be whatever you like - you can include the $ sign, you can include some text - it doesn't matter because it's NEVER used in a calculation!

Hopefully that clears it up a little :) It's NEVER used in a calculation.

Your Javascript needs to send 2 figures to the server - CurrentBalance and AmountPaid (it doesn't need AmountDue because that's NEVER used). It then uses those 2 figures to calculate the amount due, and sends back a bit of information that you want to show to the user. That bit of information is only to show to the user, so it can be whatever you want it to be. Here's a very quick, overly simplified example of calc.php:

<?php
$result = $_POST['CurrentBalance'] - $_POST['AmountPaid'];
$infoToShow = "The amount due is now $" . number_format($result, 2);

echo json_encode([ 'due' => $infoToShow ] );

Open in new window

I'm really hoping that makes more sense as we seem to be going around in circles. To work out how much someone owes, you have to subtract the Amount they've paid from the Balance. At no point do you use the AmountDue in the calculation.



One other thing that I noticed in your HTML - you have 2 elements with the amountDue class. When your jQuery sets the content of .amountDue, it will only set the content of the first one (the DIV). It will overwrite the second one (the SPAN), so the SPAN will no longer exist.
sasnaktiv

ASKER
Hey Chris,
Congratulations! You nailed it with:
<?php
$result = $_POST['CurrentBalance'] - $_POST['AmountPaid'];
$infoToShow = "The amount due is now $" . number_format($result, 2);

echo json_encode([ 'due' => $infoToShow ] );

Open in new window


But when calculating a figure in the thousands the math gets screwy again.
$14500 - .01 = $13.99

FYI the correct amount is indeed entered in the db: 14499.99

Are you tired of me yet?
Sas
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Stanyon

This is a problem we discussed a while back. You need to make sure the values that are submitted are converted to floats before doing any calculations:

$balance = isset($_POST['CurrentBalance']) ? filter_var($_POST['CurrentBalance'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION) : 0;

Open in new window

sasnaktiv

ASKER
Oh, but it is as you suggested Chris.
Here's the calc.php code I'm using. Notice that Ive changed 'CurrentBalance' to 'LastBALANCE'
<?php  // This document is calc.php //
$id = isset($_POST['id']) ? $_POST['id'] : 0;
$PaymentDATE = isset($_POST['PaymentDATE']) ? $_POST['PaymentDATE'] : 0;
//$paid = isset($_POST['amountPaid']) ? (float)$_POST['amountPaid'] : 0;
$paid = isset($_POST['amountPaid']) ? filter_var($_POST['amountPaid'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION) : 0;
$LastBALANCE = isset($_POST['LastBALANCE']) ? filter_var($_POST['LastBALANCE'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION) : 0;
$AmountDUE = isset($_POST['AmountDUE']) ? filter_var($_POST['AmountDUE'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION) : 0;

$data = [
    'paid' => $paid,
    'LastBALANCE' => $LastBALANCE - $paid,
    'due' => $LastBALANCE - $paid,
    'id' => $id,
    'PaymentDATE' => $PaymentDATE,
    ];

$TableName="INVOICES";

// mysqli way of doing things
include "../_db/db_XXXXXX.php";
   
$stmt = $db->prepare("UPDATE $TableName SET  AmountPAID = ?, AmountDUE = ? , LastBALANCE = ? , PaymentDATE = ? WHERE id = ?");
$stmt->bind_param("dddsi", $data['paid'], $data['due'],  $data['LastBALANCE'], $data['PaymentDATE'], $data['id']);
$stmt->execute();

//echo json_encode($data);

$result = $_POST['LastBALANCE'] - $_POST['amountPaid'];
$infoToShow = " $ " . number_format($result, 2);

echo json_encode([ 'due' => $infoToShow ] );
?>

Open in new window


I believe the calculation is functioning properly because the correct results are entered in the DB.
The problem may be occurring after the calculation is executed.
What do you think? Am I missing something?
Sas
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sasnaktiv

ASKER
It's MAGICAL!!!

Let's keep this ticket OPEN just for old time's sake.
Or until I figure out how to award you with a MILLION POINTS.

I don't like the EE interface since they changed it from its first presentation. Lots of problems now, like trying to "Preview" a page before submitting. It's impossible to go back to make changes. And yes its more difficult to figure out how to award the points.
Got any pointers?

But most importantly Chris,
I want you to know that I truly appreciate the time and patience and true expertise you showed while walking me through all this — without even cussing — at least that I'm not aware of any.
Sas
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
sasnaktiv

ASKER
Thanks Chris. Thanks for sticking with me on this. Have a wonderful weekend.
Sas
Chris Stanyon

No worries Sas. I knew we'd get there in the end.

Enjoy your weekend