MySQL implements the inventory counting function of the ordering system

MySQL implements the inventory counting function of the ordering system

With the continuous development of the catering industry, an effective and efficient ordering system has become a necessity for restaurant operations condition. For the implementation of the inventory counting function of the ordering system, the MySQL database is a very convenient and efficient choice.

This article will introduce how to implement the inventory counting function of the ordering system in the MySQL database, as well as specific code examples.

1. Database design

In MySQL, the inventory counting function requires the design of the following database tables:

1. Product table (product): contains product ID, Fields such as product name and product unit price are used to store basic information about all products.
2. Order table (order): Contains fields such as order ID, order status, order total price, etc., used to store basic information of all orders.
3. Order item table (order_item): Contains fields such as order item ID, order ID, product ID, product quantity, etc., used to store product information in each order.
4. Inventory table (stock): Contains fields such as product ID, current inventory, etc., used to record the current inventory of each product.

Through the design of the above four tables, we can realize the inventory counting function of the ordering system, and it can also be easily implemented in subsequent operations.

2. Inventory counting implementation

Next, let’s introduce in detail how to implement the inventory counting function of the ordering system.

1. Putting goods into storage

When a new product is put into storage, we need to add a new record in the product table and inventory table. The specific code is as follows:

-- 商品表新增记录
INSERT INTO product (product_id, product_name, product_price)
VALUES (1, '鸡腿堡', 15.00);

-- 库存表新增记录
INSERT INTO stock (product_id, current_stock)
VALUES (1, 100);

In the above code, we have added a new product called "Chicken Leg Burger" and set the initial inventory to 100 in the inventory table.

2. Goods out of the warehouse

After the customer places an order and pays, we need to update the order table, order item table and inventory table. The specific code is as follows:

-- 新增订单记录
INSERT INTO order (order_id, order_status, total_price)
VALUES (1, '已支付', 30.00);

-- 新增订单项记录
INSERT INTO order_item (order_item_id, order_id, product_id, product_num)
VALUES (1, 1, 1, 2);

-- 更新库存表中鸡腿堡的库存量
UPDATE stock SET current_stock = current_stock - 2
WHERE product_id = 1;

In the above code, we have added an order record and an order item record, in which the order item table records the product with the product ID 1 (i.e. "Chicken Leg Burger") The quantity is 2. Next, we updated the inventory of "Chicken Leg Burgers" in the inventory table through the SQL UPDATE statement, reducing it by 2.

3. Inventory Count

When we need to take inventory, we can achieve it through the GROUP BY statement of SQL. The specific code is as follows:

-- 查询商品的销售量
SELECT product.product_id, product.product_name, SUM(order_item.product_num) AS sales_num
FROM product
JOIN order_item ON product.product_id = order_item.product_id
GROUP BY product.product_id, product.product_name;

-- 查询商品当前库存量
SELECT product.product_id, product.product_name, stock.current_stock
FROM product
JOIN stock ON product.product_id = stock.product_id;

The above code uses two SELECT statements to query the sales volume and current inventory of the product. Among them, the first statement calculates the total sales of each product through the GROUP BY statement, and the second statement directly queries the current inventory of each product in the inventory table.

Through the code examples in the above three parts, we can see how to implement the inventory counting function of the ordering system in MySQL. In actual development, we can use this method to easily perform inventory management and inventory operations, and further optimize the restaurant's operations and services.

The above is the detailed content of MySQL implements the inventory counting function of the ordering system. For more information, please follow other related articles on the PHP Chinese website!