0

I am trying to implement a table lock on my database for a sample shopping website hosted locally.

The following is a sample of my index.php:

case "add":             
    if(!empty($_POST["quantity"])) {
        if(!isset($timestamp)){
            $timestamp = time();
            $lock = $db_handle->runQuery("LOCK TABLE orders_f write, prod_f read");
        }
        $productByCode = $db_handle->runQuery("SELECT * FROM prod_f WHERE code='" . $_GET["code"] . "'");
        $itemArray = array($productByCode[0]["code"]=>array('name'=>$productByCode[0]["name"], 'code'=>$productByCode[0]["code"], 'quantity'=>$_POST["quantity"], 'price'=>$productByCode[0]["price"]));

        if(!empty($_SESSION["cart_item"])) {
            if(in_array($productByCode[0]["code"],array_keys($_SESSION["cart_item"]))) {
                foreach($_SESSION["cart_item"] as $k => $v) {
                    if($productByCode[0]["code"] == $k) {
                        if(empty($_SESSION["cart_item"][$k]["quantity"])) {
                            $_SESSION["cart_item"][$k]["quantity"] = 0;
                        }
                        $_SESSION["cart_item"][$k]["quantity"] += $_POST["quantity"];
                    }
                }
            } else {
                $_SESSION["cart_item"] = array_merge($_SESSION["cart_item"],$itemArray);
            }
        } else {
            $_SESSION["cart_item"] = $itemArray;
        }

Where the table lock has been enforced. I get the following error when I try to add in the cart, which is the source of the case statement above:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in path\dbcontroller.php on line 24 Here is a snippet of my dbcontroller.php:

function runQuery($query) {
    $result = mysqli_query($this->conn,$query);
    while($row=mysqli_fetch_assoc($result)) {
        $resultset[] = $row;
    }       
    if(!empty($resultset))
        return $resultset;
    else
        return false;
}

I've been searching all night on Youtube and on Stackoverflow but none of the options seem to be working. It is on an Apache server using Wamp on a 64 bit Windows 10 machine. Please, any suggestions are welcomed.

Jainil
  • 1,488
  • 1
  • 21
  • 26
Snake4eva
  • 29
  • 4
  • Why do you use table locks in the first place? – Shadow Nov 11 '19 at 17:32
  • It's a requirement to prevent other users from accessing the product table for 10 mins while a user has added an item to their cart. – Snake4eva Nov 11 '19 at 17:39
  • Use mysqli in exception mode, to get usefull error messages: https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param – Paul Spiegel Nov 11 '19 at 17:42
  • Okay, i'll try that and let you know what happens – Snake4eva Nov 11 '19 at 17:48
  • Fatal error: Uncaught mysqli_sql_exception: Table 'prod_f' was not locked with LOCK TABLES in path\dbcontroller.php on line 23. mysqli_sql_exception: Table 'prod_f' was not locked with LOCK TABLES in – Snake4eva Nov 11 '19 at 18:04
  • 1
    I agree with @YuriG, locking tables for 10 minutes is an overkill. It effectively means that you can only have 1 customer at a time with anything in their carts. Unless this is a specific requirement in your assignment, I would forget about this. – Shadow Nov 11 '19 at 22:32
  • Yes it is a requirement. I'm thinking of an alternative if i can't get to resolve the locking table error. Which is to get the timestamp when the a user adds and item to the cart, add that time to the $_Server superglobal if it is not already set. Then when new users try to add i do a check to see if the difference between timestamps is greater than 10 mins, if so then the query is allowed to run otherwise the user has to wait. – Snake4eva Nov 12 '19 at 23:48

0 Answers0