Design method of order evaluation table for food shopping system in MySQL

Design method of order evaluation table for grocery shopping system in MySQL

1. Introduction
In the grocery shopping system, users can evaluate the purchased items after submitting the order. Product evaluation. In order to implement the order evaluation function, a corresponding order evaluation table needs to be designed in the database. This article will introduce in detail the design method of the order evaluation table of the grocery shopping system in MySQL and provide code examples.

2. Table structure design
In order to realize the order evaluation function, a separate order evaluation table can be designed. The table can contain the following fields:

1. order_id: Order ID, associated with the orders table as a foreign key.
2. rating_score: Rating, used by users to rate products, which can be represented by integers or floating point numbers.
3. comment: Evaluation content, users can enter comments on the product.
4. created_at: Evaluation creation time, record the date and time when the evaluation was created.

The specific SQL code implemented is as follows:

CREATE TABLE order_rating (
  order_id INT,
  rating_score DECIMAL(2,1),
  comment TEXT,
  created_at DATETIME,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

3. Table association design
In order to associate the order evaluation with the specific order, you can use foreign keys to Establish relationships. Taking the above order evaluation table as an example, the order_id field can be used as a foreign key to associate with the order_id field in the order table.

Assume that the name of the order table is orders, and its structure is as follows:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  // 其他字段...
);

When designing the order evaluation table, you need to ensure that the order_id field has the same data type and length as the order_id field in the order table. .

4. Usage Example
In the grocery shopping system, users can evaluate the products in the order after submitting the order. When the user submits the form, you can use the following code to insert relevant evaluation information into the order evaluation table:

<?php
// 连接数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

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

// 提交评价信息
$order_id = $_POST['order_id'];
$rating_score = $_POST['rating_score'];
$comment = $_POST['comment'];
$created_at = date('Y-m-d H:i:s');

$sql = "INSERT INTO order_rating (order_id, rating_score, comment, created_at)
        VALUES ('$order_id', '$rating_score', '$comment', '$created_at')";

if ($conn->query($sql) === TRUE) {
    echo "评价提交成功";
} else {
    echo "评价提交失败: " . $conn->error;
}

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

In the above code, first connect to the database through the mysqli class. Then get the evaluation information from the form and use the INSERT INTO statement to insert the evaluation information into the order evaluation table. Finally, the corresponding prompt information is returned according to the result of the insertion operation.

5. Summary
This article introduces the design method of the order evaluation table of the grocery shopping system in MySQL, and provides corresponding code examples. By designing an order evaluation table and associating it with the order table, the function of users in the grocery shopping system to evaluate purchased goods can be realized. I hope this article will help you understand and implement the order evaluation function.

The above is the detailed content of Design method of order evaluation table for food shopping system in MySQL. For more information, please follow other related articles on the PHP Chinese website!