SQL Manipulation

[AnViLe]

$delete = mysqli_query($connection,"SELECT Quantity FROM Books WHERE (ID='$idB' AND Quantity < 1)");

        if($delete){

            $delete_run = mysqli_query($connection,"DELETE FROM Books WHERE ID='$idB'");

            echo "<script>alert('Book successfully deleted!')</script>";

        }else{

            $remove= mysqli_query($connection, "UPDATE Books SET Quantity = Quantity - $quantityB WHERE (ID='$idB' AND Quantity > 0)");

            if($remove){

                echo "<script>alert('Book successfully removed!')</script>";

            }

        }

    }

 

My goal here is to delete a data from a table only if the quantity reaches zero and I am having trouble on how to implement that idea what I have so far is just deleting the data with whatever ID was input by the user. $idB and $quantityB are the variables that I used to store user input. Need help

 

Edited November 14, 2021 by AnViLe

[Barand]

Your code seems to be assuming that $delete will be "false" if that first select query does not find any results. Wrong. It will be false only if the query fails with an error. Not finding records is not an error.

I would use that first select query to find out how many books there were for that id. Here's a PDO example...

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $res = $conn->prepare("SELECT quantity FROM book
                           WHERE idb = ?
                           ");
    $res->execute( [$_POST['idb'] ] );
    $available = intval($res->fetchColumn());
    
    if ($available >= $_POST['quantity'] ) {
        $res = $conn->prepare("UPDATE book
                                 SET quantity = quantity - ?
                               WHERE idb = ?  
                               ");
        $res->execute( [ $_POST['quantity'], $_POST['idb'] ] );
        echo "Book/s successfully removed<br>";
    }
    else {
        echo "There are only $available books in stock<br>";
    }
    
    // Remove books with zero quantity
    $count = $conn->exec("DELETE FROM book WHERE quantity = 0");
    echo "$count books were removed<br>";
}           

 

[AnViLe]

I got questions about your solution:
1.)What is prepare() function? Is it the same as mysqli_query() and is execute() also the same with mysqli_query()?
2.)"$available = intval($res->fetchColumn());" Does this code mean you're storing the data from the table to a variable $available and that specific data is the ID?
3.) Does the "?" represent the user's input?

Edited November 14, 2021 by AnViLe

[Barand]

you should not put user-provided data into your sql query strigs. It exposes you to "SQLinjection". Used prepared statements intead

https://www.php.net/manual/en/pdo.prepare.php

https://www.php.net/manual/en/pdostatement.execute.php

The first query "SELECT quantity FROM book ..." gets the quantity then stores that quantity in the variable "$available". If the book id isn't found, fetchColumn() would return "false" hence the intva()l to change that to 0 - if no books or no record I don't want to update).

[AnViLe]

FetchColumn() doesn't work on my end like it is an undefined method can I use fetch_assoc() as an alternative? I am using vscode so I think I missed out some files needed for php

Edited November 15, 2021 by AnViLe