I need to get a balance variable out from a table and insert it back to the same table to different value

Question

I need to get a balance variable out from a table and insert it back to the same table to different value, i am not sure am making it right as SOL is asking me to check the right syntax to use.

This is my full php script

   <?php

// Connects to the database 

$db = mysql_connect("$Sname","$Uname","$Pname") or die("Could not connect to the Database."); 
$select = mysql_select_db("$Dname") or die("Could not select the Database."); 


//Converts form values to simple variables 
if ($_POST['submitButton']) {
$amount = $_POST['amount'];

$today = date("d/m/Y");
$time = date("h:i A");

$sql = "SELECT * FROM $UUname WHERE full='".$amount."'"; 
$result = mysql_query($sql) or die(mysql_error()); 
$num_return = mysql_num_rows($result); 
if ($num_return == 1){
// inserts the values into the DB 

mysql_query("UPDATE $TATname SET date='".$today."', time='".$time."', amount='".$amount."' WHERE user='{$_SESSION['user']}'");


$user = "{$_SESSION['user']}"; 

// get credits

$query = mysql_query("SELECT SUM(credit) FROM $UUname WHERE user='".$user."'");
$credit = mysql_result($query, 0);


// get debits

$query = mysql_query("SELECT SUM(debit) FROM $UUname WHERE user='".$user."'");
$debit = mysql_result($query, 0);


$bal = $credit - $debit;

$query = "UPDATE $UUname SET full='".$bal."'"; 
$update = mysql_query($query) or die(mysql_error()); 

    header("Location: successful.php");
    exit;
}
    else
{
    echo "Insufficient Funds";
}
}
?>

Please what is the right syntax to use on that last line, or what is the right way for me to do this, because i want to tell users that they have insufficient balance when they try to make a transfer.

Answer 1

you must write query like this

$query = "UPDATE $UUname SET full='".$bal."'"; 
$update = mysql_query($query) or die(mysql_error());

Answer 2

It's not clear from your post, but I assume you have one table $UUname which contains columns named credit, debit and full (the latter containing the balance)?

If that is the case, the correct way to update the balance column would be:

$query = "UPDATE $UUname SET full = $bal";

If that is not the case, please provide your table layout to be more specific.

UPDATE

Not sure if I fully understood your problem, but from what I got a solution could look like this:

Create a user table:

user_id | username | balance

user_id is primary key and there is only one row for each user. You can add further user related attributes as you like.

Create a transaction table:

transaction_id | user_id | pdate | bdate | amount | text

When a user adds credit, you add a row with a positive amount, when he is "purchasing" something, you add a negative amount. "text" is simply a description of the transaction, like so:

User receives credit of 10000:

INSERT INTO transaction_table (user_id, pdate, bdate, amount, text) VALUES
('12345', '2014-08-08', '2014-08-08', 10000, "Credit charge");

User spends 5000:

INSERT INTO transaction_table (user_id, pdate, bdate, amount, text) VALUES
('12345', '2014-08-08', '2014-08-08', -5000, "bought some cool item");

Now when you add up all amounts for a user, you receive the user's balance:

SELECT user_id, SUM(amount) FROM transaction_table GROUP BY user_id;

or, for a single user with user_id 12345:

SELECT SUM(amount) FROM transaction_table WHERE user_id='12345';

You can update the balance in the user table like that:

UPDATE user_table
   SET balance=$balance
WHERE user_id=12345;

Now, when you perform a transaction, you simply check if the balance is sufficient for the amount due. If it is, you proceed; if not, you stop.

That should provide a good compromise between simplicity and integrity for such a use case. You could also do a full update for all user balances using one sql statement (e.g. analog to https://stackoverflow.com/a/10095710/1140979). Hope this helps.