3
\$\begingroup\$

so this is my code for a equipping an item in my game dev't:

try {
        $db = getConnection();

        $db->beginTransaction();

        $sql_chara_gold = $db->query("SELECT chara_gold FROM chara WHERE chara_id = $shop->chara_id");
        $get_chara_gold = $sql_chara_gold->fetchColumn();

        $sql_shop_price = $db->query("SELECT item_price FROM shop WHERE item_id = $shop->item_id");
        $get_shop_price = $sql_shop_price->fetchColumn();

        if($get_chara_gold >= $get_shop_price){
            $sql_put_item = "INSERT INTO bag(chara_id, item_id, item_qty)
            VALUES(:id,:item_id,1) 
            ON DUPLICATE KEY 
            UPDATE item_qty = item_qty +1";

            $sql_set_gold = "UPDATE chara ch 
            INNER JOIN item it
            ON it.item_id = :item_id
            SET ch.chara_gold = ch.chara_gold - it.item_price 
            WHERE chara_id = :id";

            $sql_set_qty =  "UPDATE shop 
            SET item_qty = item_qty - 1 
            WHERE item_id = :item_id";


            $stmt = $db->prepare($sql_put_item);  
            $stmt->bindParam("id", $shop->chara_id);
            $stmt->bindParam("item_id", $shop->item_id);
            $stmt->execute();

            $stmt = $db->prepare($sql_set_gold);  
            $stmt->bindParam("id", $shop->chara_id);
            $stmt->bindParam("item_id", $shop->item_id);
            $stmt->execute();

            $stmt = $db->prepare($sql_set_qty);  
            $stmt->bindParam("item_id", $shop->item_id);
            $stmt->execute();

            $db->commit();

            $db = null;

            echo json_encode($shop); 
        }else{
            echo '{"error":{"text":'.json_encode($get_shop_price).'}}'; 
        }
\$\endgroup\$
  • \$\begingroup\$ Have you considered using an ORM? \$\endgroup\$ – Sam Selikoff May 10 '13 at 3:23
  • \$\begingroup\$ i dont(or i dont know), i only(recently learned) use PDO. \$\endgroup\$ – Viscocent May 10 '13 at 5:33
2
\$\begingroup\$

I don't know if I'd use ORM for something so simple, but it may be something to look into. At the very least, you should get in the practice of escaping query parameters to avoid SQL injection. I often use Aura SQL for this, because it's dead simple: https://github.com/auraphp/Aura.Sql

An example query using Aura would look like this:

$user = $db->fetchOne('SELECT chara_gold FROM chara WHERE chara_id = :id', [
    'id' => $shop->chara_id
]);

Aura will automatically escape any bound parameters, like :id, so you don't have to worry about SQL injection. If you don't use Aura, at least use PDO::quote.

Funds transfer is the textbook example of why you need transactions to meet the ACID requirements, so it's good that you're using one, but I don't see your rollback. I assume this is in your catch clause which wasn't included.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.