Jalapeno5 Jalapeno5 - 6 months ago 11
PHP Question

Field subtraction using Mysql

Please could someone help me with this newbie problem I've been struggling with for some time now:

The first part of my code below is a validation check that the team has sufficient funds available to purchase a player; the second part adds the player to the team sheet; but I also need to update the Teams table to remove the player's cost from the Available_Funds pending successful validation.

My question is two-fold... the validation query doesn't work as is stands. I think I need to add a mysqli function around the $result<0 check, but I'm not sure which one. I also need to update the Avaliable_Funds in the Teams table accordingly; but am unsure of the necessary syntax to combine this with the Insert Into statement. Any guidance on these two related issues would be gratefully received.

This is the relevant section from my code as it stands. $Cost is a variable that's being posted correctly. (I've commented-out the parts that aren't currently working):

//$result = $mysqli->query("Select (Available_Funds-'{$Cost}') as check From Teams Where Team_ID = '{$Team}'");
//if ($result<0) {$error.='<p>You have insufficient funds</p>';}

$sql="Insert Into Players (Player_Name,Status,Type_ID,Team_ID) Values ('" . $player_name . "','Active','" . $Type . "','" . $Team . "')";
$result = $mysqli->query($sql); }

//$sql="Update Teams set Available_Funds=(Available_Funds-'{$Cost}')";
$result = $mysqli->query($sql);

Many thanks in advance for any assistance on this one!


Change your first query. The query below will return a match iff the team has enough funds

$sql =  "SELECT * FROM ( "
        ."SELECT Available_Funds - $Cost as balance " 
        ."FROM Teams Where Team_ID = '$Team' "
        .") AS t "
        ."WHERE t.balance >= 0"

$result = $mysqli->query($sql);

The inner query returns the remaining balance. Then the outer query selects that result iff that balance is non-negative.

Process results:

if($result===false){//DB error. Probably bad query. See $mysqli->error        
    $error = "Server error: no changes were made";
}elseif($result->num_rows===0){//unknown $Team or insufficient funds        
    $error = "Insufficient funds";
    //team has sufficient funds. The next two queries should be run
    //as a transaction, meaning if either one fails, all changes
    //should be rolled back. This is to avoid a team getting a player
    //without paying, or a team paying, but not getting the player

    //server won't commit changes until we say so

    //learn to use prepared statements. This is dangerous
    $sql2 =   "Insert INTO Players (Player_Name,Status,Type_ID,Team_ID) "
             ."VALUES ('$player_name','Active','$Type','$Team')";

    $sql3 =   "Update Teams SET Available_Funds=(Available_Funds-$Cost) "
             ."WHERE Team_ID = '$Team'";        

    if($mysqli->query($sql2) && $mysqli->query($sql3)){
        $mysqli->commit(); //save changes
        $mysqli->rollback(); //undo changes
        $error = "Server error: no changes were made";
    $mysqli->autocommit(true); //end transaction mode
    $mysqli->close(); //close connection

I should re-iterate: it's really poor security practice to insert user-defined values directly in a query. Learn to use prepared statements instead. Good luck :-)