2. PHPRunner General questions
  3. [SOLVED]  Mixing multiselect insert code with other code
This topic is locked
[SOLVED]

 Mixing multiselect insert code with other code

 		8/17/2019 10:47:05 AM
PHPRunner General questions
D
 david22585 author

I'm looking to use the multiselect values to individual database record code, and after a lot of research that it needs to be a VARCHAR field instead of a INT field, I got it to work for inserting the records as individual records. Here is the code I used:

if ($values["account_id"])

{

$arr = explode(",",$values["account_id"]);

// This is the name of the multi check box or

// list select field, its value becomes $arr

for ($i=0;$i<count($arr);$i++)

{

$strInsert = "insert into accounts_transactions (account_id, debit) values ('".$arr[$i]."','".$arr[$i]."$values["debit"]')";

db_exec($strInsert);

}

header("Location: test_multi_add_add.php");

exit();

}


This works for storing the account_id and debit field as individual records. What I'm doing is monthly billing, where the user balance is stored in 2 spots, on their individual record and on the accounts_transactions page. The individual record is on an accounts_data table and used for their real time balance. The account_transactions is for a ledger of their balance over time. To update their balance on their data, I use this code:

DB::Query("update accounts_data set balance=balance + ".$values["debit"]." where id = ".$values["account_id"]."" );


This code will retrieve the balance from their account, and add the debit "charge" to their account where the account_id is.
And to update the transaction log, I use this code:

$sql = "SELECT(balance) AS total FROM accounts_transactions where account_id = ".$values["account_id"]." ORDER BY id DESC LIMIT 1";

$rs = DB::Query($sql);

$data = db_fetch_array($rs);

$values['balance'] = $data["total"] + $values["debit"] - $values["credit"];


This code selects the balance from the accounts_transactions where the last account_id entry is for them. It will retrieve the value, and add the debit, and set a new balance for the ledger of their balance.
I'm wondering if there is a way to modify the first code to work with these other 2 codes? I'm not concerned with performance, as this will only happen once a month for billing at night time where one bulk execution is done to add a set fee to every single account and update all those account balances.
Thanks
Sergey Kornilov admin 8/17/2019

What kind of problem you are facing running all these code snippets together?
D
 		david22585 author 8/17/2019



What kind of problem you are facing running all these code snippets together?


I tried a few ways to use the first code to run the next 2 codes individually, but I just couldn't figure out how to run each code as an array. So lets say that I select 30 different accounts to add a balance to. Account selection is by the account_id field. I need the code to run the 2 following codes:

DB::Query("update accounts_data set balance=balance + ".$values["debit"]." where id = ".$values["account_id"]."" );


$sql = "SELECT(balance) AS total FROM accounts_transactions where account_id = ".$values["account_id"]." ORDER BY id DESC LIMIT 1";

$rs = DB::Query($sql);

$data = db_fetch_array($rs);

$values['balance'] = $data["total"] + $values["debit"] - $values["credit"];


So lets say I select account IDs 1-30 from the multiselect. I need the first code to run 30 times for each account_id to update their balance in the accounts_data table, and the 2nd code to run 30 times for each account_id for the accounts_transactions table so that each account has a statement of their charges and balance after each charge. So for each account_id, it will run 2 query, 1 for each piece of code.
D
 		david22585 author 8/21/2019

After a lot of different methods, I was able to get this to work. I can use this code to create a transaction log to generate statements, along with updating the balance on a given account. I have 2 tables, 1 that holds the user account information with the current balance (accounts_data), and another that has a list of all transactions for all accounts (accounts_transactions). Lets say you do monthly billing and want to bill each account $50/month, or lets say add a $10 late fee every month to a list of accounts. Doing it 1 by 1 would be tedious for a large number of accounts. With this code,every account that you select will update their current balance on the accounts_users table, and insert an entry into the account_transactions table with the bill amount (debit), along with reading their last balance and providing an updated balance to create statements so a user can see their transactions.

// Updates the account data current balance

if ($values["account_id"])

{

$arr = explode(",",$values["account_id"]);

for ($i=0;$i<count($arr);$i++)

{

$strInsert = "update accounts_data set balance=balance+".$values["debit"]." where id='".$arr[$i]."'";

DB::Query($strInsert);

}
// Update the transaction log for the statements

if ($values["account_id"])

{

$arr = explode(",",$values["account_id"]);

for ($i=0;$i<count($arr);$i++)

{

$sql = "SELECT(balance) AS total FROM accounts_transactions where account_id = ".$arr[$i]." ORDER BY id DESC LIMIT 1";

$rs = DB::Query($sql);

$data = db_fetch_array($rs);

$values['balance'] = $data["total"] + $values["debit"];

$strInsert2 = "insert into accounts_transactions (account_id, debit, balance) values ('".$arr[$i]."','".$values["debit"]."','".$values["balance"]."')";

DB::Query($strInsert2);

}
header("Location: test_multi_add_list.php");

exit();

}}