MySQL implements the delivery tracking function of the ordering system

In modern society, the ordering system has become an indispensable part of the mass catering industry. People not only require the quality and taste of food, but also require easy tracking during the delivery process. Meal delivery date, time and delivery location information. The MySQL database has good scalability and stability and is widely used in various industries. This article will introduce how to use the MySQL database to implement the delivery tracking function of the ordering system to meet users' needs for delivery.

1. Data table design

In the MySQL database, the data table is the basic unit for organizing and storing data. For the delivery tracking function of the ordering system, we can design the following data table:

1. Order table (orders)

##idint10Order numbercustomervarchar20Customer namephonevarchar11Customer phone numberaddressvarchar100Shipping addressstatusint1Order status (0: not delivered, 1: delivered) create_timedatetime0 Next Single timedelivery_timedatetime0delivery timetotal_price decimal10, 2Total order price

Field name	Data type	Length	Description

Dishes

Field nameData typeLengthDescriptionidint10Meal numbernamevarchar20Meal namepricedecimal10, 2Meal unit pricecategoryvarchar20Meal category

Order details table (order_details)

##Field nameidorder_iddish_idquantitysubtotal2. Database operation

Data type	Length	Description
int	10	Detail number
int	10	Order number
int	10	Meal number
int	3	Quantity of meals
decimal	10, 2	Subtotal of meals

Database connection

1. Use PHP language to connect to the MySQL database:

// 数据库连接参数
$host = 'localhost'; // 数据库主机地址
$user = 'root'; // 数据库用户名
$password = ''; // 数据库密码
$dbname = 'order_system'; // 数据库名称

// 连接数据库
$conn = mysqli_connect($host, $user, $password, $dbname);
if (!$conn) {
    die('数据库连接失败！' . mysqli_connect_error());
}

Query order information

The key to realizing the delivery tracking function of the ordering system is to query the order information and display the delivery status information on the page. The following code queries all delivered order information:

// 查询已配送的订单信息
$sql = "SELECT o.*, SUM(d.price * od.quantity) AS total_price FROM orders o 
        LEFT JOIN order_details od ON o.id = od.order_id 
        LEFT JOIN dishes d ON od.dish_id = d.id 
        WHERE o.status = '1' 
        GROUP BY o.id 
        ORDER BY o.delivery_time DESC";
$result = mysqli_query($conn, $sql);

Update order status

When the delivery status of an order changes, the status of the order in the database needs to be updated in a timely manner. The following code is used to update the status of the order to "delivered":

// 更新订单状态
$order_id = $_POST['order_id']; // 获取订单编号
$sql = "UPDATE orders SET status = '1' WHERE id = $order_id";
if (mysqli_query($conn, $sql)) {
    // 更新成功
    echo '1';
} else {
    // 更新失败
    echo '0';
}

3. Page implementation

Order list page

When ordering In the background management page of the system, we can display all the order information that has been delivered, and include operation buttons to modify the order status. The following code shows the HTML code of the delivered order list:

<table>
    <thead>
        <tr>
            <th>订单编号</th>
            <th>客户姓名</th>
            <th>客户手机号</th>
            <th>配送地址</th>
            <th>订单总价</th>
            <th>下单时间</th>
            <th>配送日期</th>
            <th>配送时间</th>
            <th>操作</th>
        </tr>
    </thead>
    <tbody>
        <?php
        while ($row = mysqli_fetch_assoc($result)) {
            echo '<tr>';
            echo '<td>' . $row['id'] . '</td>';
            echo '<td>' . $row['customer'] . '</td>';
            echo '<td>' . $row['phone'] . '</td>';
            echo '<td>' . $row['address'] . '</td>';
            echo '<td>' . $row['total_price'] . '</td>';
            echo '<td>' . $row['create_time'] . '</td>';
            echo '<td>' . date('Y-m-d', strtotime($row['delivery_time'])) . '</td>';
            echo '<td>' . date('H:i', strtotime($row['delivery_time'])) . '</td>';
            echo '<td><button class="delivered-btn" data-order-id="' . $row['id'] . '">已送达</button></td>';
            echo '</tr>';
        }
        ?>
    </tbody>
</table>

Order status update

When the operator clicks the "Delivered" button, an AJAX needs to be sent to the server Request to update the order status and dynamically display the order status changes on the page. The following code demonstrates the JavaScript code of the AJAX request:

// 订单状态更新
$(document).on('click', '.delivered-btn', function () {
    var order_id = $(this).attr('data-order-id'); // 获取订单编号
    $.ajax({
        url: 'update_order_status.php',
        method: 'POST',
        data: {order_id: order_id},
        success: function (res) {
            if (res === '1') {
                // 更新成功
                alert('订单状态已更新！');
                location.reload(); // 刷新页面
            } else {
                // 更新失败
                alert('订单状态更新失败！');
            }
        }
    });
});

4. Summary

The process of using the MySQL database to implement the delivery tracking function of the ordering system involves the design of the database table and the collection of order information. Query and update. At the same time, we also need to display order information on the page and implement dynamic order status updates. The above provides a basic implementation idea, and readers can expand and modify it appropriately according to actual needs and business logic.

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