The question is about ajax, mysql, select and update, transaction and locks etc., and sorry if I've put the background story so long but in order to understand my needs I think it is necessary. This is a long question but the need is simple so I would really appreciate it if you read through the question and help me out.

The techs I use/ wish to use in my project:

ajax, php, mysql, transaction and locks

Background story:

So here is the problem that bugs me a lot in my project, which should be quite common but I can't seem to find a seamless solution online for my situation.

My project is quite like an online shop and when different customers come to buy an item, I always want them to get the right inventory for this product. If there are other customers are viewing this item and may possibly go ahead to check out, then the customers came to view this product later than those customers cannot proceed to select the quantity they want and proceed to check out until the first-come customers finish viewing this item or finished checking out. So that prevents the shop oversells.

What I currently know and have done:

Now, I know I should use a sql transaction, or lock the table if a customer comes to view the product by use SELECT...FOR UPDATE or just a lock etc. I think I perfectly know how to perform the steps while writing the SQL in series in terminal or in phpmyadmin.

However, in my situation, I use an ajax call to get the product page for the customer (for which I mean I use ajax to get the data from a php file in which there is a SELECT clause), and after that I use another ajax call to send the check-out data to another php to update the table under the press of a button which is used proceed to check out:

For instance in my get-order-detail.js(pseudocode but pretty much like the real code I write in my project):

    $.ajax({
        method: "post",
        url: "get-order-details.php",
        success: function(backData){
            $("#ordersTable").html(backData);
            $("#check-out-button").on("click", function(){
                var quantity_selected = $("#check-out-qty-input").val();
                var sendData = "qty=" + quantity_selected;
                $.ajax({
                    method: "post",
                    url: "check-out.php",
                    data: sendData,
                    success: function(backData){
                        alert(backData + "is deducted.")
                    }
                });
            });
    });

And in get-order-details.php(pseudocode, eliminate try-catch):

$pdo->beginTransaction();
$sql = "SELECT quantity_left FROM orders WHERE orderId = 1 FOR UPDATE"
$stmt = $pdo->prepare($sql);
$stmt->execute();
if ($row = $stmt->fetch()){
    $quantity_left = $row["quantity_left"]
    echo "You can still take " . $quantity_left;
    echo "I want to take <input id='check-out-qty-input'>";
    echo "<button id='check-out-button'>checkout</button>";
$pdo->commit();

And in check-out.php(pseudocode, eliminate try-catch):

$qty = $_POST["qty"];
$pdo->beginTransaction();
$sql = "UPDATE orders SET quantity_left=quantity_left-$qty WHERE orderId = 1"
$stmt = $pdo->prepare($sql);
$stmt->execute();
echo $qty;
$pdo->commit();

What my question is:

So as you see, these are two ajax calls to two php files (one to SELECT to display quantity_left info and provide a checkout button and one to UPDATE the quantity_left if a customer has checked out some products), and they don't work as I wished. So is it possible to split a transaction into these two php files? If so, how can I do it and if not possible, what is a more proper way to say combine those two php into one php and just use one ajax call to perform these two actions?

What I expect to see at the frontend:

When a customer is viewing an item, the page won't display any information or will delay displaying the quantity info of this product after the earlier-come customer has finished his/her action on this product. It would also be better if the quantity can be updated real-time(I know maybe we have to use backbone for that, but if simple ajax cannot achieve it then it's fine to keep this way).

Thanks a lot for solving my problem!

0

There are 0 best solutions below