INSERT statement works only once

Question

I asked something like this earlier, but now I ran better tests, and tried to analyze what the problem was. This is the ajax request:

  //start ajax request here//
   $.post('purchaseitem.php',{itemAmount:itemAmount, itemId:itemId}, function(data){
    $('.savestatus_'+itemId).text(data);
   });
  //end it here

I echo out all the items in the items table, along with an ahref link and an input field to allow the user to type in the quantity and click buy.

<?php
    $shop_query = mysql_query("SELECT * FROM sector0_item WHERE item_location = '$chapter'");
    while(($shop_row = mysql_fetch_array($shop_query))){
        $itemid = $shop_row['item_id'];
        $item_name = $shop_row['item_name'];
        $item_price = $shop_row['item_price'];
        ?>
        <div class = "item_name"><?php echo $item_name; ?></div> 
        <div class = "item_price"><?php echo $item_price; ?></div> 
        <input type = 'text' class = "purchaseAmount_<?php echo $itemid;?>" name = "purchaseAmount" />
        <a id = "purchaseButton_<?php echo $itemid; ?>" href = "prevent default();" class = "purchase_button" onclick = "buy(); return false;">Buy</a>
        <div class = 'savestatus_<?php echo $itemid; ?>'></div>
        <hr /><br />
        <?php
    }
?>

This is a test version of my code, so I know that it is messed up... The php part:

$user_inventory_query = mysql_query("SELECT * FROM sector0_inventory WHERE id = '$dbid' AND item_id = '$item_id'");
                        $checking_item_inventory = mysql_num_rows($user_inventory_query);
                        if($checking_item_inventory === 0){
                            /*^*/  $insertion_into_inventory = mysql_query("INSERT INTO `sector0_inventory`(`id`, `item_id`, `username`, `item_name`, `quantity`) VALUES ('$dbid','$item_id','$dbuser','$item_name','$purchase_amount')");
                                if($insertion_into_inventory === true){
                                mysql_query("UPDATE sector0_players SET cash = cash-'$total_cost' WHERE id = '$dbid'");
                                echo "Purchase complete";
                                }
                        }else if ($checking_item_inventory === 1){
                            $update_inventory_quantities = mysql_query("UPDATE sector0_inventory SET quantity = quantity+'$purchase_amount' WHERE id = '$dbid' AND item_id = '$item_id'");
                            if($update_inventory_quantities===true) {
                                mysql_query("UPDATE sector0_players SET cash = cash-'$total_cost' WHERE id = '$dbid'");
                                echo "Purchase complete, quantity updated.";
                                }
                        }

The above is the query. the /^/ part is the part that fails. When I Truncate the table, and click buy, the insertion is completely successful. But for any other item, insertion fails. It's a php, I guess, I am really confused. relative table to the insert and update queries

CREATE TABLE `sector0_inventory` (
 `id` bigint(20) NOT NULL COMMENT 'The input in this field will be php code exclusive. No increment allowed.',
 `item_id` bigint(20) NOT NULL COMMENT 'The input is also code exclusive',
 `username` varchar(250) NOT NULL COMMENT 'This value will be used to search for the user inventory information. Admin privileges only',
 `item_name` varchar(250) NOT NULL COMMENT 'This value will be used to identify (user side) the item. It will be used by admins to query out when a removal of a specific item is needed',
 `quantity` bigint(20) NOT NULL COMMENT 'This value will be 0 by default BIG int is to allow calculations',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I have a very hard-coded custom function to sanitize every sort of user-input variable :) — Khalid Okiely, Apr 19 '12 at 23:38

Jeff Allen · Accepted Answer · 2012-04-19T23:47:24.213

Show the output of CREATE TABLE sector0_item to get more help, but my guess is that your primary key on that table is id and you're trying to specify that manually in your INSERT statement:

INSERT INTO `sector0_inventory`(`id`, `item_id`, `username`, `item_name`, `quantity`) VALUES ('$dbid','$item_id','$dbuser','$item_name','$purchase_amount')

Your primary key must be unique for each row. Try:

INSERT INTO `sector0_inventory`(`item_id`, `username`, `item_name`, `quantity`) VALUES ('$item_id','$dbuser','$item_name','$purchase_amount')

That will work if your id column is set to AUTO INCREMENT.

EDIT: After you posted the table structure, your problem is the database table design. Right now the primary key is id which means you can only have one row per PHP session ID. I don't know your application, but that seems wrong.

If you can delete the table and start from scratch, then DROP the table and re-create it using:

CREATE TABLE `sector0_inventory` (
 `transaction_key` INT NOT NULL AUTO INCREMENT COMMENT 'The unique ID for each row',
 `id` bigint(20) NOT NULL COMMENT 'The input in this field will be php code exclusive.     No increment allowed.',
 `item_id` bigint(20) NOT NULL COMMENT 'The input is also code exclusive',
 `username` varchar(250) NOT NULL COMMENT 'This value will be used to search for the     user inventory information. Admin privileges only',
 `item_name` varchar(250) NOT NULL COMMENT 'This value will be used to identify (user     side) the item. It will be used by admins to query out when a removal of a         specific item is needed',
 `quantity` bigint(20) NOT NULL COMMENT 'This value will be 0 by default BIG int is to     allow calculations',
 PRIMARY KEY (`transaction_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

then restore your PHP back to the way you had it.

Note that this will forfeit all of your data in that table...

The first id column is inserted/valued as the user's session/cookie ID It is a bigint(20), item_id is the same, username is a varchar, item_name is varchar, quantity is bigint(20). I will try to add an auto increment index field, and retry the the insertion. — Khalid Okiely, Apr 19 '12 at 23:36
Edit the question to include the output of `SHOW CREATE sector0_inventory` and that will help. — Jeff Allen, Apr 19 '12 at 23:39
This really did work! Thanks for, not only the answer, but also the explanation, much appreciated! — Khalid Okiely, Apr 19 '12 at 23:54

INSERT statement works only once

1 Answers1