Android Question How to avoid alterations in the online inventory?

[fhersof]

Hello everyone, I would like you to give me a little help, I have an application created in B4A, which executes a php file that is on a server with a mysql database, when I perform the tests from a mobile everything seems to work well, rest and adds to the inventory, but when using several mobiles, alterations are generated in the inventory when 2 or more mobiles consult the same product at the same time.

Two mobiles consult a product that has availability of 5 units, both make the sale and the inventory remains at -5, how to avoid it?

I have also noticed that when 2 or more mobiles consult the same product at the same time, an alteration is mysteriously generated in the inventory that is mysql. How to handle several connections at the same time?

I attach the code of the php file that is on the server and is called from android phones.

Thank you very much and I hope you can give me a hand.

?php

    $host = "localhost";
    $user = "xxxxx";
    $pw = "xxxxx;
    $db = "xxxxx";


    $con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
    mysqli_select_db($con,$db) or die(mysqli_error());
    mysqli_query($con,"SET CHARACTER SET utf8");
    mysqli_query($con,"SET NAMES 'utf8'");

    
    $json = file_get_contents("php://input");
    $jsall = array();
    $jsone = array();
    $jsall=json_decode($json, true);
    $jsone=$jsall[0];
    
    $action = $jsone["Action"];
    
    switch ($action)
    {
    //..........................................................................   
    Case "ConsulStk":
        $pcode=stripslashes(mysqli_real_escape_string($con,$jsone["code"]));
        $stmt = $con->prepare("SELECT code, stock, price FROM products WHERE code = '$pcode'");
        $rc=$stmt->execute();
        $q = $stmt->get_result();
        $count=mysqli_num_rows($q);
        $stmt->close();

        $rows = array();
         while($r = mysqli_fetch_assoc($q))
         {
             $rows[] = $r;
         }

        exit (json_encode($rows));
        
        break;
    //..........................................................................
    
        Case "RestoStock":
        $pcode=stripslashes(mysqli_real_escape_string($con,$jsone["code"]));
        $pstock=stripslashes(mysqli_real_escape_string($con,$jsone["stock"]));
        
        $stmt = $con->prepare("UPDATE products SET stock = stock - '$pstock' WHERE code = '$pcode'");
        $rc=$stmt->execute();
        $ar=$stmt->affected_rows;
        if ($ar == 0)
        {
            exit (json_encode(array(array('resul' => 'NO'))));
        }
        else
        {
            exit (json_encode(array(array('resul' => 'OK'))));   
        }
        
        break;
    //..........................................................................
    
        Case "SumoStock":
        $pcode=stripslashes(mysqli_real_escape_string($con,$jsone["code"]));
        $pstock=stripslashes(mysqli_real_escape_string($con,$jsone["stock"]));
        
        $stmt = $con->prepare("UPDATE products SET stock = stock + '$pstock' WHERE code = '$pcode'");
        $rc=$stmt->execute();
        $ar=$stmt->affected_rows;
        if ($ar == 0)
        {
            exit (json_encode(array(array('resul' => 'NO'))));
        }
        else
        {
            exit (json_encode(array(array('resul' => 'OK'))));   
        }
        
        break;

 

[Erel]

Two mobiles consult a product that has availability of 5 units, both make the sale and the inventory remains at -5, how to avoid it?

Each one buys 5 units?

You need to check the current stock before changing it and you need to do it sequentially. If it was a B4J server then you can easily do it with a single threaded handler.

 

[Sandman]

Some solutions also reserve the goods for a limited time, making them unavailable to anybody else for the duration of that time. I've seen this happening mostly when buying movie tickets online (for a physical movie theater). Not saying you have to do it like that, I'm just adding some info that might be inspiration.

 

[fhersof]

Thanks for the concepts, the first thing I will do is verify and then discount, since not doing it sequentially would generate alteration.