MySQL - 查询中的INSERT验证

Take the 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 is that enough? Can a user get around it and purchase the item even if there isn't enough money?

Would it be better to do something like this:

Keep the 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 the validation here again something like. I don t know how to do the query exactly.
 $q = $this->db->mysqli->prepare("INSERT INTO buys (bl bla blah) VALUES (?,?,?) IF ... user has enough points in user_points table");
}

Hope that makes sense and I don't get down voted.

In your database you can use a trigger to check the constraint. Depending on you model you might need a transaction to prevent a record from being inserted incorrectly.

Assuming the following:

Two tables:

• buys
• wallet

If a user buys something (definite BUY, so not a shopping cart placement action), the wallet is updated in the same action.

To do this you can either write a transaction: See How to start and end transaction in mysqli? on how to.

and use 2 statements:

 UPDATE wallet SET amount=amount-{buyAmount} WHERE user=?;
 INSERT INTO buys (amount,user,orderId) VALUES (?,?,?);

(Of course buyAmount is also a ? in the prepared statement)

Or you can use a trigger. The trigger has to lock the user record when inserting in the buys table:

CREATE TRIGGER updateWallet() BEFORE INSERT ON buys
BEGIN
  SET @updatedWalletAmount=0;
  SELECT amount-NEW.buyAmount FROM wallet WHERE user=NEW.user FOR UPDATE;
  IF(@updatedWalletAmount>0) THEN
    UPDATE wallet SET amount=@updatedWalletAmount;
  ELSE
     SIGNAL SQLSTATE 'ERR0R'
       SET
         MESSAGE_TEXT = 'Not enough money',
         MYSQL_ERRNO = 'USER-1';
  END;
END;

The error will have to be caught in php.

Validating data on server shouldn't be made twice. Validating the data on the php side would be easier and as reliable as on your database server. For more information on validating input data you can check this.