We help IT Professionals succeed at work.

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

sasnaktiv
sasnaktiv asked
on
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
Comment
Watch Question

Jim RiddlesPrepress/OMS Specialist
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hi there,

There are 2 parts to your solution. The first part is 'sending the data to the server without leaving the page'. For this, you'll need to make an AJAX request. Easiest way to do that is by adding jQueyr to your page and then writing a small script that makes the AJAX request. Bind that script to a user action (such as clicking a Calculate button).

The second part of your solution is to write a PHP script on the server that receives the data from that AJAX request and processes it - i.e. calculates the AmountDue and writes the information to the database. Once it's done that, it will send some information back to your AJAX script so that you can update the User Interface.

Here's a very quick example:

First the HTML:

<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 id="amountDue"></span>

<button id="calculate">Calculate</button>

Open in new window

And now the jQuery:

<script src="https://code.jquery.com/jquery-3.3.1.min.js" integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=" crossorigin="anonymous"></script>
<script>
$(function() {
    $('#calculate').click(function() {
        let data = {
            amountBilled : $('input[name="AmountBILLED"').val(),
            amountPaid : $('input[name="AmountPAID"').val()
        };

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

Open in new window

And finally the PHP script (calc.php)

<?php 
$username = 'username';
$password = 'password';
$dsn      = 'mysql:host=localhost;dbname=yourDb;charset=utf8mb4'; 
$options  = [
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
];

try {
    $db = new PDO($dsn, $username, $password, $options);

    $billed = (float)$_POST['amountBilled'] ?? 0;
    $paid = (float)$_POST['amountPaid'] ?? 0;

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

    $stmt = $db->prepare("INSERT INTO yourTable (billed, paid, due) VALUES (:billed, :paid, :due)");
    $stmt->execute($data);

    echo json_encode($data);

} catch(PDOException $e) {
    die( $e->getMessage() );
}

Open in new window

You may want to add some error checking, but that should give you the general idea of what's needed.

Author

Commented:
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 RiddlesPrepress/OMS Specialist
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Haha - no heard of AJAX (the detergent) for ages. Made me chuckle.

Basically, you'll have 2 pages - one will be your HTML page which contains the jQuery library, AJAX script (in the <head> of your page) and your Inputs/Button etc. The other will be a PHP script that sits on the server waiting to receive the data.

You can use mySQLi instead of PDO if you'd prefer. The principle is pretty much the same. MySQLi supports prepared statements, just not with named parameters, so we need to use placeholders instead (I would still suggest you use a prepared statement as this prevents SQL Injection attacks).

Here's a quick idea on the mySQLI way of doing things. I'll leave out the connection bit as I'm guessing you already have that covered (the code assumes your connection is stored in $db)

$billed = (float)$_POST['amountBilled'] ?? 0;
$paid = (float)$_POST['amountPaid'] ?? 0;

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

// mysqli way of doing things
$stmt = $db->prepare("INSERT INTO yourTable (billed, paid, due) VALUES (?, ?, ?)");
$stmt->bind_param("ddd", $data['billed'], $data['paid'], $data['due']);
$stmt->execute();

echo json_encode($data);

Open in new window

Author

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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Haha - I have to believe you CAN teach an old dog new tricks ... I'm no spring chicken ;)

AJAX is just a name for making an Asynchronous call to your server (i.e. do something on the server in the background). Binding is just a way of saying 'attach a function to a user action, such as a button click'

If you have several accounts on the same page, then we'll need to adapt the code slightly. You'll need to isolate each account and you'll also need to be passing in some kind of ID.

Change the HTML to this:

<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>

Open in new window

You'll notice we've wrapped each 'account' in a wrapper DIV. We've also added a Unique ID to the button and changed from using an ID on the button to using a class.

You'll also need to adapt your Jquery slightly:

$(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);
        });
    });
});

Open in new window

Few changes their just to make sure we're only ever dealing with the correct account. You'll notice that we're also passing along an ID to the back-end. This makes sure that we're only updating the correct DB record, so you'll need to adapt your PHP slightly as well:

$billed = (float)$_POST['amountBilled'] ?? 0;
$paid = (float)$_POST['amountPaid'] ?? 0;
$id = $_POST['id'] ?? 0;

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

// mysqli way of doing things
$stmt = $db->prepare("UPDATE yourTable SET billed = ?, paid = ?, due = ? WHERE id = ?");
$stmt->bind_param("dddi", $data['billed'], $data['paid'], $data['due'], $data['id']);
$stmt->execute();

echo json_encode($data);

Open in new window

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hey Sas,

Couple of issues with your code. First off, you don't need to echo out code - you're not running this particular page as a PHP script - it's just standard HTML with some Javascript. You'll need to make sure it's a proper HTML page, so include the doctype / html tags etc. The reason your code does nothing when you click the button is because you haven't actually included the main jQuery library. Here's the full 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>

        <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>
</html>

Open in new window

When you make your AJAX call, your PHP script will pass data back to the done() method and that data will be available in the response object. The PHP script is passing back an object with properties billed, paid, due, id. If you look through the done() method, you'll see how we're handling the Amount Due. Basically, we access the due property with response.due. You'll see that we set the HTML (text) of an element with a class of amountDue to that value, so whatever get's passed back from your server in the 'due' property will end up getting put inside that element in your HTML:

<span class='amountDue'> the response.due value will go here when your AJAX call completes </span>

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hey Sas,

Right. The first error you're getting may be down to you using an older version of PHP. I used something called a null coalesce operator (double question mark), but that's only available in PHP7. If you're using an older version, you can't use that so you'll need to change the code at the start of the calc.php

$billed = isset($_POST['amountBilled']) ? (float)$_POST['amountBilled'] : 0;
$paid = isset($_POST['amountPaid']) ? (float)$_POST['amountPaid'] : 0;
$id = isset($_POST['id']) ? $_POST['id'] : 0;

It does the same as my code, but it's just a bit longer.

As for the PHP thing. You can mix PHP and HTML in the same document. My point was simply that you were using echo "" to output all of your HTML. There's no need to do that. You were doing this

echo"
    <head>
    < script>
    $(function() {

and basically wrapping your entire HTML and Javascript into an echo function. You only really need to dip in and out of PHP when needed, so you'd have something like this:

<?php
error_reporting(E_ALL);
ini_set('display_errors','On');

// make you DB connection and retrieve your values.
$amountBilled = 500; // assume this has come from your DB
?>
<!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>

        <div class='account'>
            <label>Amount Billed</label>
            <input type='text' name='AmountBILLED' value='<?php echo $amountBilled ?>' 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>
</html>

Open in new window

You'll notice that I've wrapped the opening code in PHP tags to make the DB connection. I've then closed off those tags and reverted to normal HTML. Further down the page, I've opened PHP tags again, just to echo out a value and then closed the tags. We're only using PHP when needed. For the rest, we're just using standard HTML / CSS / JS as if it was a normal HTML page. (you'll still need to give your page the php extension).

Author

Commented:
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!
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Haha - I really need a day off

Author

Commented:
Okay Chris,
You nailed it. Now I'm going to attempt to incorporate your solution into my actual page.
Fingers crossed,
Sas

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 :)

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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

Author

Commented:
Oddly though, the problem does not occur with any values less than 1000!
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Haha - the joys of PHP. That's an error in my original code (using the float cast)

The reason you're getting the problem above 1,000 is that when you try and convert the POSTed data from a string of "1,000", it doesn't recognise the comma, so this:

$billed = (float)"1,000";

will just set $billed to 1. This means that you need to strip out any commas from the user-input.

You could use str_replace, but I think you're better off using the filter_var() method instead:

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

Open in new window

Now $billed will contain a proper float so you won't have to worry about commas

Author

Commented:
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

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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).

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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?

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 :)

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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).

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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

Author

Commented:
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???
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
So close :)

In your code, you convert the POST variable to floats, which is correct. You then use those values for the calculation and for the DB record, which again is correct.

You then revert back to using the POST variables to try and calculate the due amount again for the result which is wrong!. There's no need to do that - you've already done the calculation so just use that :

instead of this:

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

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

Open in new window

You want this:

$infoToShow = " $ " . number_format($data['due'], 2);
echo json_encode([ 'due' => $infoToShow ] );

Open in new window

Author

Commented:
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

Author

Commented:
Thanks Chris. Thanks for sticking with me on this. Have a wonderful weekend.
Sas
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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

Enjoy your weekend