CODEWITHSUNDEEP
phpmysqlsql
Danejir
Danejir

Reputation: 507

Update two rows to add and subtract

I have a datatable with user information and a column called 'Bits'. There is a transfer function and I would like to transfer x amount of bits from the logged in user to the user they specify. I have the following function that works, but I would like to know if there is a better (simpler, cleaner, quicker?) way to do all of this, and possibly return the row of the first SQL statement at the end. I am not an advanced MySQL user by any means, but I am pretty sure there is a function I am missing that could make this much easier. 

function transferBits($toid, $amount)
{
    global $loggedInUser, $db;
    $fromid = $loggedInUser->user_id;
    $sql = "INSERT INTO `dl_Transfers` SET From_ID = '".$db->sql_escape($fromid)."',
        `To_ID`='".$db->sql_escape($toid)."',
        `Bits`='".$db->sql_escape($amount)."',
        `When`=Now()";
    $result = $db->sql_query($sql);
    $sql2 = "UPDATE `dl_Users` SET Bits = Bits - '".$db->sql_escape($amount)."' WHERE `User_ID` = '".$db->sql_escape($fromid)."'";
    $result2 = $db->sql_query($sql2);  
    $sql3 = "UPDATE `dl_Users` SET Bits = Bits + '".$db->sql_escape($amount)."' WHERE `User_ID` = '".$db->sql_escape($toid)."'";
    $result3 = $db->sql_query($sql3);
}

Upvotes: 0

Views: 204

Answers (2)

prodigitalson
prodigitalson

Reputation: 60413

I think you could hit the update in single query by joining on the previous record id from your insert on dl_Transfers:

UPDATE `dl_Transfers` r 
JOIN `dl_Users` f ON ( r.From_ID = f.User_ID )
JOIN `dl_Users` t ON ( r.To_ID = t.User_ID )
SET f.Bits = f.Bits - r.Bits, t.Bits = t.Bits + r.Bits
WHERE r.Transfer_ID = ?;

Also as others stated in their comments, you should definitely use transactions here.

Upvotes: 3

TonyTheJet
TonyTheJet

Reputation: 527

You might consider using a MySQL trigger to handle this. Basically, you would set it up so anytime there is an insert on dl_Transfers, it updates the appropriate rows in dl_Users.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Upvotes: 0

Related Questions