How to insert calculated field into database?

Question

I am inserting multiple records into a database. Many of the fields are consistent, but one field is calculated, and needs to calculate/subtract the value for each record inserted into the database.

<?php
$ExecuteQuery = new WA_MySQLi_Query($numeroseti);
$ExecuteQuery->Statement = "INSERT INTO transactions 
(fromAcct, amount, fromAcctNewBalance, toAcctNewBalance, toAcct, description, timestamp) 
(SELECT 1, ?, ?, balance + ?, acct_number, 'issuance', $time FROM users WHERE acct_number NOT LIKE '1800%' AND type <> 1 AND user_active = 1)";
$ExecuteQuery->bindParam("d", "".($_POST['issue_balance'])  ."", "0"); //acct1num
$ExecuteQuery->bindParam("i", "".($getSender->getColumnVal("balance") - $amount)  ."", "-1");
//acct2num
$ExecuteQuery->bindParam("i", "".($amount)  ."", "-1");
//acct3num
$ExecuteQuery->execute();
?>

The field is fromAcctNewBalance that I am having issues with. This is the code that calculates (like a ledger) what the new balance is:

$getSender->getColumnVal("balance") - $amount;

The problem is, it puts the same value into each record, when it should be subtracting the amount each time a new record is added.

For example, if the fromAcctNewBalance starting balance is 600 and the amount issued to a user is 10, and next record that is added should have a fromAcctNewBalance of 590, and so on, until it runs out of records to add to the table.

Does anyone have any ideas on how I can fix this issue?

What is `WA_MySQLi_Query`? Is it some weird mutant of PDO and mysqli? — Dharman, Jul 18 '20 at 21:15
Why dont you just put it in a single line outside of the bindParam, bind it to a value like $value= $getSender->getColumnVal("balance") - $amount; and set $value into the bindParam? — pr0cz, Jul 18 '20 at 21:21
Please add `bindParam` definition. Looks wrong but never seen that class before... a parameter is different than a value. — user3783243, Jul 18 '20 at 21:53
pr0cz, I've already tried it that way, and it didn't work. I'll post that code below. — Blondie, Jul 19 '20 at 13:57
I couldn't post all of the code (it was too long), but here is the code that I had previously used. getColumnVal("balance") - $amount; ?> ......... $ExecuteQuery->bindParam("i", "".($fromAcctNewBalance) ."", "-1"); //new admin balance — Blondie, Jul 19 '20 at 14:03
user3783243, I'm not sure I understand what you're suggesting.??? — Blondie, Jul 19 '20 at 14:03
@Blondie I posted an answer with which it hopefully will work :) — pr0cz, Jul 19 '20 at 20:13
@Blondie He (@user3783243) is referencing to this https://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue — pr0cz, Jul 19 '20 at 20:16

pr0cz · Answer 1 · 2020-07-20T06:48:22.927

1

I think the mistakes lies in the use of -> inside the bindParam.

So I tried to do the substraction without the bindParam and $getSender->$getColumnVal - $amount; simply doesn't work.

Use it like this

$getSender = $getColumnVal("balance") - $amount;
$ExecuteQuery->bindParam("i", "".($getSender)  ."", "-1");

"->, is used when you want to call a method on an instance or access an instance property."

It is unclear to me, if your $getColumnVal("balance") is a method or instance. If it is so, use it like this:

$getSender -> $getColumnVal("balance");
$getSender = $getSender - $amount;
$ExecuteQuery->bindParam("i", "".($getSender)  ."", "-1");

References:

Where do we use the object operator "->" in PHP?

https://www.php.net/manual/de/language.types.object.php

EDIT:

Full code example

<?php
$ExecuteQuery = new WA_MySQLi_Query($numeroseti);
$ExecuteQuery->Statement = "INSERT INTO transactions 
(fromAcct, amount, fromAcctNewBalance, toAcctNewBalance, toAcct, description, timestamp) 
(SELECT 1, ?, ?, balance + ?, acct_number, 'issuance', $time FROM users WHERE acct_number NOT LIKE '1800%' AND type <> 1 AND user_active = 1)";
$ExecuteQuery->bindParam("d", "".($_POST['issue_balance'])  ."", "0"); //acct1num

//Use the one that fits best
    $getSender = $getColumnVal("balance") - $amount;
    $ExecuteQuery->bindParam("i", "".($getSender)  ."", "-1");

//or 
    $getSender -> $getColumnVal("balance");
    $getSender = $getSender - $amount;
    $ExecuteQuery->bindParam("i", "".($getSender)  ."", "-1");

//acct2num
$ExecuteQuery->bindParam("i", "".($amount)  ."", "-1");
//acct3num
$ExecuteQuery->execute();
?>

edited Jul 20 '20 at 06:48

answered Jul 19 '20 at 20:10

pr0cz

509
7
22

pr0cz, are you suggesting that the query looks like this: $getSender = $getSender->getColumnVal("balance") - $amount; $ExecuteQuery->bindParam("i", "".($getSender) ."", "-1"); – Blondie Jul 19 '20 at 22:12
No. You need to make the Substraction `getColumnVal("balance") - $amount` outside of the bindParam. You call a method or instance with `->`, which is why your calculation isn't running. I edited the post with code to use. – pr0cz Jul 20 '20 at 06:48
Neither one of those is working, and both are throwing errors. I tried option #1 yesterday, and it's not subtracting the line item balance field. The starting balance is coming from the admin record in the users table, not the transactions table, so does that mean that I would need a starting balance in the transactions table, so I could just subtract from that? Hope that makes sense. – Blondie Jul 21 '20 at 14:10
Neither one of those is working, and both are throwing errors. Here's the catch. The starting balance comes from the admin's starting balance in the user's table, but each subsequent subtractions needs to come from the previous transaction. I even tried this: $startingBalance = $getSender->getColumnVal("balance"); $getSender = $startingBalance - $amount; $ExecuteQuery->bindParam("i", "".($getSender) ."", "-1"); Only the first transaction subtracts from the admin user's starting balance - the rest subtract from the transactions balance, if that makes sense. – Blondie Jul 21 '20 at 14:26
Okay, this is unfortunate. I will take a look after work at it and try to figure out whats wrong! I will write you here as I find another possible solution – pr0cz Jul 21 '20 at 14:28
it would be good to know what error you recieve to reconstruct the problem – pr0cz Jul 21 '20 at 14:29
pr0cz, don't I need some kind of foreach statement where it's grabbing the sender's balance? $getSender = $getColumnVal("balance") - $amount; – Blondie Jul 21 '20 at 18:46
pr0cz, thank you so much ... I really appreciate all of your thought into this dilemma! :) – Blondie Jul 21 '20 at 18:47
@Blondie I thought you already have this value $getColumnVal("balance") ? Do you have any final value for $getSender? If you print or echo it for example – pr0cz Jul 21 '20 at 18:58
Of course! I recieved so much help from stackoverflow - just trying to give something back – pr0cz Jul 21 '20 at 18:58
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/218306/discussion-between-pr0cz-and-blondie). – pr0cz Jul 21 '20 at 19:02
pr0cz, now I'm not getting any errors (cleared my cache a bunch of times), but it's not working. It's still putting the first subtraction amount in all records inserted. Is there a foreach statement that I need to use somewhere in this mess? I'm so sorry ... I am a PHP noob. :( I used this option: $getSender = $getSender->getColumnVal("balance") - $amount; $ExecuteQuery->bindParam("i", "".($getSender) ."", "-1"); – Blondie Jul 21 '20 at 21:12

score 1 · Answer 2 · answered Jul 29 '20 at 16:19

This ended up working for me:

    while (!$getRecipients->atEnd())
{
    $ExecuteQuery = new WA_MySQLi_Query($numeroseti);
    $ExecuteQuery->Statement = "INSERT INTO transactions 
     (fromAcct, amount, fromAcctNewBalance, toAcctNewBalance, toAcct, description, timestamp) 
    VALUES (1, ?, ?, ?, ?, 'issuance', $time)";                 
    $ExecuteQuery->bindParam("i", "".($amount)  ."", "0");
    $ExecuteQuery->bindParam("i", "".($startingbalance)   ."", "-1");   
    $ExecuteQuery->bindParam("i", "".($amount)  ."", "-1");
    $ExecuteQuery->bindParam("i", "".($getRecipients->getColumnVal("acct_number"))  ."", "-1");
    $ExecuteQuery->execute();
    
    $startingbalance = $startingbalance - $amount;
    $getRecipients->moveNext();
}
$getRecipients->moveFirst();

You realise that `"".($getSender->getColumnVal("balance") - $amount) .""` can be written as `($getSender->getColumnVal("balance") - $amount)` , yeah? — Martin, Jul 29 '20 at 16:22

How to insert calculated field into database?

2 Answers2