php - MySQL - INSERT validation in query -

take following scenario:

item cost = 30 user money = 25

form won't submit if user doesn't have enough money.

$error = false; if($user_money < $item_cost){  //don't submit form  $error = true; } 

but enough? can user around , purchase item if there isn't enough money?

would better this:

keep above:

$error = false; if($user_money < $item_cost){  //don t submit form  $error = true; }else{  $myclass->purchaseitem($item_id, $user_id); }  public function purchaseitem($item_id, $user_id) {  //do validation here again like. don t know how query exactly.  $q = $this->db->mysqli->prepare("insert buys (bl bla blah) values (?,?,?) if ... user has enough points in user_points table"); } 

hope makes sense , don't down voted.

in database can use trigger check constraint. depending on model might need transaction prevent record being inserted incorrectly.

assuming following:

two tables:

• buys
• wallet

if user buys (definite buy, not shopping cart placement action), wallet updated in same action.

to can either write transaction: see how start , end transaction in mysqli? on how to.

and use 2 statements:

 update wallet set amount=amount-{buyamount} user=?;  insert buys (amount,user,orderid) values (?,?,?); 

(of course buyamount ? in prepared statement)

or can use trigger. trigger has lock user record when inserting in buys table:

create trigger updatewallet() before insert on buys begin   set @updatedwalletamount=0;   select amount-new.buyamount wallet user=new.user update;   if(@updatedwalletamount>0)     update wallet set amount=@updatedwalletamount;   else      signal sqlstate 'err0r'        set          message_text = 'not enough money',          mysql_errno = 'user-1';   end; end; 

the error have caught in php.