PHP implements the tag cloud and topic aggregation functions in the knowledge question and answer website.

PHP implements the tag cloud and topic aggregation functions in the knowledge question and answer website

In the knowledge question and answer website, tag cloud and topic aggregation are two important functions. Tag cloud can help users quickly understand hot topics and common tags on the website, making it easier for users to browse and search for related questions and answers. Topic aggregation can group questions and answers with the same tags together, providing users with a more convenient way to view and discuss. Below we will use PHP to implement these two functions.

First, we need to create a database to store data for questions, answers, and tags. You can create a database named "qa" with three tables: questions, answers, and tags.

The structure of the questions table is as follows:

CREATE TABLE `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '',
  `content` text,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

answers The structure of the table is as follows:

CREATE TABLE `answers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) DEFAULT NULL,
  `content` text,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

tags The structure of the table is as follows:

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Next, we need Add tag selection boxes on the question and answer pages of the Q&A website, and associate the tags selected by the user with the questions or answers.

<!-- 提问页面 -->
<form action="post_question.php" method="post">
  <label for="title">问题标题:</label>
  <input type="text" name="title" id="title"><br>

  <label for="content">问题内容:</label>
  <textarea name="content" id="content"></textarea><br>

  <label for="tags">标签:</label>
  <select name="tags[]" id="tags" multiple>
    <option value="php">PHP</option>
    <option value="javascript">JavaScript</option>
    <option value="html">HTML</option>
    <!-- 其他标签选项 -->
  </select><br>

  <input type="submit" value="提交问题">
</form>

<!-- 回答页面 -->
<form action="post_answer.php" method="post">
  <label for="content">回答内容:</label>
  <textarea name="content" id="content"></textarea><br>

  <label for="tags">标签:</label>
  <select name="tags[]" id="tags" multiple>
    <option value="php">PHP</option>
    <option value="javascript">JavaScript</option>
    <option value="html">HTML</option>
    <!-- 其他标签选项 -->
  </select><br>

  <input type="submit" value="提交回答">
</form>

Then, we need to store the tag data associated with the question or answer into the database in the background question submission handler (post_question.php) and answer submission handler (post_answer.php).

// post_question.php
// 获取用户提交的问题数据
$title = $_POST['title'];
$content = $_POST['content'];
$tags = $_POST['tags'];

// 插入问题数据到 questions 表中
// 获取最后插入的问题的id
$question_id = mysqli_insert_id($conn);

// 插入标签数据到 tags 表中
foreach ($tags as $tag) {
  $sql = "INSERT INTO tags (title) VALUES ('$tag')";
  mysqli_query($conn, $sql);
  
  // 获取最后插入的标签的id
  $tag_id = mysqli_insert_id($conn);
  
  // 插入问题和标签的关联到 question_tag 表中
  $sql = "INSERT INTO question_tag (question_id, tag_id) VALUES ($question_id, $tag_id)";
  mysqli_query($conn, $sql);
}

// post_answer.php
// 获取用户提交的回答数据
$content = $_POST['content'];
$tags = $_POST['tags'];

// 插入回答数据到 answers 表中
// 获取最后插入的回答的id
$answer_id = mysqli_insert_id($conn);

// 插入标签数据到 tags 表中
foreach ($tags as $tag) {
  $sql = "INSERT INTO tags (title) VALUES ('$tag')";
  mysqli_query($conn, $sql);
  
  // 获取最后插入的标签的id
  $tag_id = mysqli_insert_id($conn);
  
  // 插入回答和标签的关联到 answer_tag 表中
  $sql = "INSERT INTO answer_tag (answer_id, tag_id) VALUES ($answer_id, $tag_id)";
  mysqli_query($conn, $sql);
}

Now, we have successfully associated the user-selected tags with questions and answers. Next, we will implement the tag cloud and topic aggregation functions.

First, we need to write a PHP function to get all tags and their number of occurrences.

function getTagsCloud() {
  global $conn;
  
  // 查询所有标签及其出现次数
  $sql = "SELECT title, COUNT(*) AS count FROM tags GROUP BY title";
  $result = mysqli_query($conn, $sql);
  
  $tagsCloud = array();
  while ($row = mysqli_fetch_assoc($result)) {
    $tagsCloud[$row['title']] = $row['count'];
  }
  
  return $tagsCloud;
}

Then, we can use this function on the front-end page to display the tag cloud.

<?php
// 获取标签云数据
$tagsCloud = getTagsCloud();
?>

<!-- 标签云 -->
<div class="tag-cloud">
  <?php foreach ($tagsCloud as $tag => $count): ?>
    <a href="search.php?tag=<?php echo $tag; ?>"><?php echo $tag; ?></a>
  <?php endforeach; ?>
</div>

Finally, we can implement the topic aggregation function, that is, query related questions and answers based on tags.

function getQuestionsByTag($tag) {
  global $conn;
  
  // 根据标签获取问题
  $sql = "SELECT q.id, q.title, q.date, COUNT(*) AS answersCount 
          FROM questions q 
          INNER JOIN question_tag qt ON q.id = qt.question_id 
          INNER JOIN tags t ON t.id = qt.tag_id 
          WHERE t.title = '$tag' 
          GROUP BY q.id";
  $result = mysqli_query($conn, $sql);
  
  $questions = array();
  while ($row = mysqli_fetch_assoc($result)) {
    $questions[] = $row;
  }
  
  return $questions;
}

function getAnswersByTag($tag) {
  global $conn;
  
  // 根据标签获取回答
  $sql = "SELECT a.id, a.content, a.date, q.title 
          FROM answers a 
          INNER JOIN answer_tag at ON a.id = at.answer_id 
          INNER JOIN tags t ON t.id = at.tag_id 
          INNER JOIN questions q ON q.id = a.question_id 
          WHERE t.title = '$tag'";
  $result = mysqli_query($conn, $sql);
  
  $answers = array();
  while ($row = mysqli_fetch_assoc($result)) {
    $answers[] = $row;
  }
  
  return $answers;
}

We can use the above two functions on the topic details page to display related questions and answers.

<?php
// 获取标签名称
$tag = $_GET['tag'];

// 获取话题相关的问题和回答数据
$questions = getQuestionsByTag($tag);
$answers = getAnswersByTag($tag);
?>

<!-- 话题相关的问题 -->
<?php foreach ($questions as $question): ?>
  <div class="question">
    <h3><?php echo $question['title']; ?></h3>
    <p>回答数量: <?php echo $question['answersCount']; ?></p>
    <!-- 其他问题信息展示 -->
  </div>
<?php endforeach; ?>

<!-- 话题相关的回答 -->
<?php foreach ($answers as $answer): ?>
  <div class="answer">
    <h4><?php echo $answer['title']; ?></h4>
    <p><?php echo $answer['content']; ?></p>
    <!-- 其他回答信息展示 -->
  </div>
<?php endforeach; ?>

Through the above code examples, we have successfully implemented the tag cloud and topic aggregation functions in the knowledge question and answer website, so that users can easily browse hot topics and related questions and answers. At the same time, we also provide corresponding database operation examples to allow readers to better understand the implementation ideas of the code. I hope this article will be helpful to readers in need.

The above is the detailed content of PHP implements the tag cloud and topic aggregation functions in the knowledge question and answer website.. For more information, please follow other related articles on the PHP Chinese website!