How to put variable within mysql command? php

Question

got a little problem here with php. I got a simple calculation system, in which there are fields balance, trtype (long version transaction type), tramt(amount of that specific transaction), and finalbalance, which is the balance AFTER the operation. It shows up as a table and it has the list of all transactions a user had. So i make a form where the ""employees""(theres only me), can fill out the form like date trans type and so on. To avoid any kind of miscalculations, i dont want the ""employees"" to put the finalbalance field by themselves. I want the computer to do it. And the finalbalance is always same what happened after the transaction. When new transaction happens, it creates a new number and keeps the old one. It is kind of messy, and im 99% sure this is not the best approach, but would appriciate any help.

$AS = $CONNECT->prepare('UPDATE acc balance = CONCAT(balance + ?,"<br>"), WHERE username = ? '))
$AS->bind_param('ss', $_POST['balance'],$_POST['ac']);
$AS->execute();
$AS->store_result();
$AS->close();
$AS2 = $CONNECT->prepare('SELECT finalbalance FROM accounts WHERE username = ?');
$AS2->bind_param('s', $_POST['acc']);
$AS2->execute();
$AS2->bind_result($fnb);
$AS2->store_result();
$AS3 = $CONNECT->prepare('UPDATE accounts SET finalbalance = CONCAT(finalbalance+.$fnb.,"<br>") WHERE username = ?');
$AS3->bind_param('s', $_POST['acc']);
$AS3->execute();
$AS3->store_result();
$AS3->close();

I do this code, but this gives me an error Stack trace {main} thrown, which usually means i screwed up in my mysql command, so im guessing it didnt get the variable. Thanks for reading and help.

surely the `balance` column in the is numeric rather than a string so why on earth are you trying to add HTML `BR` tags into it? Also missing the `SET` from the `update` statement and there should not be a comma before the `where` clause — Professor Abronsius, Jun 06 '20 at 15:47
I think the first update command lacks set statement or am i wrong? — Mostafa Harb, Jun 06 '20 at 15:48
Those are edit errors, actual code doesnt have them. Main issue is AS2 and AS3 sql command — Univ Ersal, Jun 06 '20 at 15:48
also missing trailing `semi-colon` after initial statement. And the `select` statement never calls `fetch` on the resultant object — Professor Abronsius, Jun 06 '20 at 15:49
`CONCAT(finalbalance+.$fnb.,"
")` - `$fnb` is string, right? And what are the dots (`.`) supposed to be? — Alon Eitan, Jun 06 '20 at 15:50

score 0 · Answer 1 · answered Jun 06 '20 at 15:56

Perhaps this might help.

# query modified, removed `concat` and kept data as numeric rather than a string.
# The `update` statement needs the `SET` keyword.
$AS = $CONNECT->prepare('UPDATE acc set balance = balance + ? WHERE username = ?');
$AS->bind_param('ss', $_POST['balance'], $_POST['ac'] );
$AS->execute();
$AS->close();
# No recordset is generated, no need to `store` the result

#
$AS2 = $CONNECT->prepare('SELECT finalbalance FROM accounts WHERE username = ?');
$AS2->bind_param('s', $_POST['acc'] );
$AS2->execute();
$AS2->bind_result($fnb);
$AS2->store_result();
$AS2->fetch(); # need to fetch the recordset!

# again - removed the `concat` and maintain as numeric. Bind the variable to a placeholder rather than embedding
$AS3 = $CONNECT->prepare('UPDATE accounts SET finalbalance = finalbalance + ? WHERE username = ?');
$AS3->bind_param('ss', $fnb, $_POST['acc']);
$AS3->execute();
$AS3->close();
# No recordset is generated, no need to `store` the result

score -1 · Answer 2 · answered Jun 06 '20 at 15:51

-1

try this:

$AS3 = $CONNECT->prepare('UPDATE accounts SET finalbalance = CONCAT(finalbalance+".$fnb.","<br>") WHERE username = ?');

answered Jun 06 '20 at 15:51

mentamarindo

539
9
16

How to put variable within mysql command? php

2 Answers2