Home  >  Article  >  Database  >  MySQL implements the delivery tracking function of the ordering system

MySQL implements the delivery tracking function of the ordering system

WBOY
WBOYOriginal
2023-11-01 15:31:531217browse

MySQL 实现点餐系统的配送跟踪功能

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
  1. 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
  1. 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
  1. 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
  1. 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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn