PHP与MySQL构建物流网站的货物跟踪系统

<?php
// 连接数据库
$conn = new mysqli("localhost", "username", "password", "database");

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 创建货物表
$sql = "CREATE TABLE shipments (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tracking_number VARCHAR(30) NOT NULL,
    origin VARCHAR(50) NOT NULL,
    destination VARCHAR(50) NOT NULL,
    status ENUM('shipped', 'in_transit', 'delivered') NOT NULL,
    estimated_arrival DATETIME,
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "表 shipments 创建成功";
} else {
    echo "创建表失败: " . $conn->error;
}

// 创建跟踪表
$sql = "CREATE TABLE tracking (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shipment_id INT(6) UNSIGNED,
    location VARCHAR(50) NOT NULL,
    status ENUM('shipped', 'in_transit', 'delivered') NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (shipment_id) REFERENCES shipments(id)
)";

if ($conn->query($sql) === TRUE) {
    echo "表 tracking 创建成功";
} else {
    echo "创建表失败: " . $conn->error;
}

$conn->close();
?>

<?php
// 更新货物状态
$conn = new mysqli("localhost", "username", "password", "database");

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$shipment_id = $_POST['shipment_id'];
$new_status = $_POST['new_status'];
$location = $_POST['location'];

$sql = "INSERT INTO tracking (shipment_id, location, status) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("iss", $shipment_id, $location, $new_status);
$stmt->execute();

$sql = "UPDATE shipments SET status = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("si", $new_status, $shipment_id);
$stmt->execute();

echo "货物状态已更新";

$conn->close();
?>

<?php
// 查询货物状态
$conn = new mysqli("localhost", "username", "password", "database");

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$shipment_id = $_GET['shipment_id'];

$sql = "SELECT * FROM shipments WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $shipment_id);
$stmt->execute();
$result = $stmt->get_result();
$shipment = $result->fetch_assoc();

$sql = "SELECT * FROM tracking WHERE shipment_id = ? ORDER BY timestamp DESC";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $shipment_id);
$stmt->execute();
$result = $stmt->get_result();
$tracking_history = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode(array('shipment' => $shipment, 'tracking_history' => $tracking_history));

$conn->close();
?>

<?php
// 分页查询货物
$conn = new mysqli("localhost", "username", "password", "database");

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$per_page = 10;
$offset = ($page - 1) * $per_page;

$sql = "SELECT * FROM shipments LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $per_page, $offset);
$stmt->execute();
$result = $stmt->get_result();
$shipments = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode($shipments);

$conn->close();
?>