0

I'm writing a query which updates stock of a product when a customer adds it to their cart.

As there are many different sizes I thought the best way to go about it would be to bind the selected size as a parameter from a variable, however this gives the error:

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in ... Stack trace: #0 ... include() #1 {main} thrown in..."

Here's a simplified version of my table 'stock':

Product ID | S_stock | M_stock | L_stock
    1            3        1          1

I've tried this:

if (isset($_POST['addtocart'])){
    $stockupdate = $_POST['size'];

    $sql = "UPDATE stock SET ?_stock = ?_stock - 1 WHERE productID = ?";
    $stmt = $link->prepare($sql);
    $stmt->bind_param("sss", $stockupdate, $stockupdate, $productid);
    $stmt->execute();

and this (adding "_stock" to the variable first):

if (isset($_POST['addtocart'])){
    $stockupdate = $_POST['size'].'_stock';

    $sql = "UPDATE stock SET ? = ? - 1 WHERE productID = ?";
    $stmt = $link->prepare($sql);
    $stmt->bind_param("sss", $stockupdate, $stockupdate, $productid);
    $stmt->execute();

Performing a var_dump on the prepared statement above returns bool(false) leading to the error.

The query works succesfully if I type in the size values contained in the variables:

$sql = "UPDATE stock SET M_stock = M_stock - 1 WHERE productID = ?";

Can someone explain why it behaves this way? And is there a way I can get it to work? (Without writing out a load of if statements for every clothes and shoe size!)

Thanks in advance.

M. Eriksson
  • 13,450
  • 4
  • 29
  • 40
Liam Bell
  • 25
  • 6
  • 1
    You can't have the column name as a bound param. – M. Eriksson Dec 23 '20 at 08:04
  • Although the duplicate talks about table names, this also applies to column names. – Nigel Ren Dec 23 '20 at 08:05
  • 5
    You can't parametrise a field name. It's not intended for that. TBH having different columns for each size seems like a bit of a denormalised data structure. Gets awkward if you add sizes or change their names. IMO It would be better in a child table, this data, with a foreign key back to the master table. And then you wouldn't have this problem with trying to use variables for field names in your query either – ADyson Dec 23 '20 at 08:06

0 Answers0