How to implement article classification function by creating article classification table in MySQL

MySQL creates an article classification table to implement the article classification function

With the rapid growth of information and the popularity of the Internet, various types of articles continue to emerge. When building a content-rich website, categorizing your articles is essential. This article will introduce how to use MySQL to create an article classification table, and demonstrate how to implement the article classification function through code examples.

1. Create an article classification table

First, we need to create an article classification table to store the article classification information. Assume that our article classification table is named "category" and contains the following fields:

1. id: category ID, set as an auto-increment primary key;
2. name: category name, used for identification Different article categories.

Use the following SQL statement to create the article classification table:

CREATE TABLE category (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

2. Insert article classification data

We can insert some test data into the article classification table, using for subsequent functional demonstrations. Suppose we have the following article categories: technology, life, entertainment. Use the following SQL statement to insert data:

INSERT INTO category (name) VALUES 
  ('科技'),
  ('生活'),
  ('娱乐');

3. Association between the article table and the classification table

Next, we need to add a foreign key field to the article table to associate the article with the classification table stand up. Assume that our article table is named "article" and contains the following fields:

1. id: article ID, set as an auto-increment primary key;
2. title: article title;
3. content: article content;
4. category_id: category ID, which is the category to which the article belongs.

Use the following SQL statement to create an article table and add a category foreign key:

CREATE TABLE article (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  category_id INT NOT NULL,
  FOREIGN KEY (category_id) REFERENCES category(id)
);

4. Query articles of a specific category

Through the above steps, we have succeeded The article classification table and article table were established and related. Now, we can query articles in a specific category through SQL.

Suppose we need to query articles classified as "Technology", use the following SQL statement to query:

SELECT article.title, article.content
FROM article
INNER JOIN category ON article.category_id = category.id
WHERE category.name = '科技';

The above statement associates the article table and article classification table through INNER JOIN, in the WHERE clause The category name is limited to "Technology", and the article titles and content of the corresponding category are retrieved.

5. Sample program

The following is a simple sample program that demonstrates how to use PHP and MySQL to implement the article classification function:

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "my_database";

// 创建数据库连接
$conn = new mysqli($servername, $username, $password, $dbname);

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

// 查询分类为"科技"的文章
$sql = "SELECT article.title, article.content
        FROM article
        INNER JOIN category ON article.category_id = category.id
        WHERE category.name = '科技'";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出每个文章的标题和内容
    while($row = $result->fetch_assoc()) {
        echo "标题：" . $row["title"]. "<br>";
        echo "内容：" . $row["content"]. "<br><br>";
    }
} else {
    echo "没有匹配的文章";
}

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

The above code creates the same function through the mysqli extension. Connect to the MySQL database and query articles classified as "Technology". Finally, the title and content of each article are output through a loop.

Through the above steps, we successfully created an article classification table using MySQL and implemented the article classification function using sample code. Through this method, we can classify and manage articles to better organize and display the content of the website.

The above is the detailed content of How to implement article classification function by creating article classification table in MySQL. For more information, please follow other related articles on the PHP Chinese website!