MySQL implements the payment management function of the ordering system

MySQL implements the payment management function of the ordering system

With the popularity of online ordering services, the payment management function of the ordering system has become a must-have for restaurant operators a function. As a mature and stable database management system, MySQL provides strong support for payment management of the ordering system. This article will introduce in detail how to use MySQL to implement the payment management function of the ordering system, and attach specific code examples.

1. Database design

First, you need to design a database table to store order and payment related information. The following is a common design solution:

• Order table (order): stores the basic information of the order, such as order ID, user ID, order amount, etc.
• Payment method table (payment_method): stores payment method information, such as payment method ID, payment method name, etc.
• Payment record table (payment_record): stores the detailed information of each payment, such as payment record ID, order ID, payment method ID, payment status, etc.

2. Create database tables

Use the MySQL command line or graphical tools (such as Navicat) to create the database and corresponding tables. Here is the sample code to create the above table:

CREATE DATABASE IF NOT EXISTS `order_system`;
USE `order_system`;

CREATE TABLE IF NOT EXISTS `order` (
  `order_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `payment_method` (
  `payment_method_id` INT NOT NULL AUTO_INCREMENT,
  `payment_method_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`payment_method_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `payment_record` (
  `payment_record_id` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NOT NULL,
  `payment_method_id` INT NOT NULL,
  `payment_status` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`payment_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Insert initial data

Insert initial data for use. The following is a sample code for inserting several pieces of data into the payment method table (payment_method):

INSERT INTO `payment_method` (`payment_method_name`) VALUES
('支付宝'),
('微信支付'),
('银行卡支付');

4. Implementing the payment function

In the ordering system, when the user places an order Finally, the order information needs to be inserted into the order table (order) and a payment record to be paid is generated. After the user completes the payment, the payment record is updated to the paid status.

The following is a sample code to implement the payment function:

<?php
// 连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'order_system');

// 生成订单
$user_id = 1; // 假设用户ID为1
$amount = 10.00; // 假设订单金额为10.00元
$sql = "INSERT INTO `order` (user_id, amount) VALUES ($user_id, $amount)";
$result = mysqli_query($conn, $sql);
if ($result) {
  $order_id = mysqli_insert_id($conn);
  
  // 新建支付记录
  $payment_method_id = 1; // 假设支付方式ID为1，即支付宝
  $payment_status = '待支付';
  $sql = "INSERT INTO `payment_record` (order_id, payment_method_id, payment_status) VALUES ($order_id, $payment_method_id, '$payment_status')";
  $result = mysqli_query($conn, $sql);
  if ($result) {
    $payment_record_id = mysqli_insert_id($conn);
    
    // 用户完成支付，更新支付记录
    $payment_status = '已支付';
    $sql = "UPDATE `payment_record` SET payment_status = '$payment_status' WHERE payment_record_id = $payment_record_id";
    $result = mysqli_query($conn, $sql);
    if ($result) {
      echo '支付成功';
    } else {
      echo '支付失败';
    }
  } else {
    echo '创建支付记录失败';
  }
} else {
  echo '生成订单失败';
}

// 关闭数据库连接
mysqli_close($conn);
?>

Through the above sample code, we can realize the basic functions of payment management in the ordering system. After the user places an order, the order is generated and related payment records are created. After the user completes the payment, the payment record is updated to the paid status.

To sum up, using MySQL can easily realize the payment management function of the ordering system. Through reasonable database design and corresponding code implementation, efficient and stable payment management solutions can be provided for restaurant operators.

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