How to query in y column according to x column with php sql query

Hello,
I have a table called materials
You have two columns “product_quantity” and “min_stock”
In the “min_stock” column, it contains the value to be notified when the product drops to the minimum level.

How do I list materials that are less than or equal to the min_stock number with a query?

$bildir = $db->prepare("SELECT * FROM materials");
$bildir->execute();
$stokbildir = $bildir->fetchAll();

It’s just a simple comparison in the WHERE clause between the current quantity and the minimum quantity column, to find the row(s) you are interested in.

Your inventory/stock system should NOT maintain the quantity by updating the value in a column. This does not provide an audit trail so that you can detect if a programming mistake, accidental submit button press, or nefarious activity has altered a value. You would instead insert a row into an inventory table for every transaction that affects the normal +/- inventory amount and you would also record the consumption of inventory for sales/projects by inserting rows in an order items or similar table. To get the current quantity of any item(s), you would use a UNION query between the inventory table and the order items table to SUM() the +/- quantities of each item.

1 Like

Can we use Triggers in this case? Thank you!

On the inventory page, the amount to be deducted from the stock is entered with the “-” icon in each product line. The amount to be added is entered with the “+” icon. With the “edit” icon, the product name, product total quantity, minimum stock level quantity fields are available.
Ekran görüntüsü 2022-12-21 114555

On another page, the product is automatically deducted when sold.

3 people use this program and we know what we are doing. The database is automatically backed up every day at 00:00

I don’t know how to do it can you help with a sample code?

Sponsor our Newsletter | Privacy Policy | Terms of Service