php - 更新查询以反射(reflect)规范化数据库

Question

我正在尝试制作一个文章网站，但我想规范化我当前的数据库。我有四个表，每个表都标记一个类别，除了一个之外。这四个表是:娱乐、生活方式、科学和文章。文章是娱乐、生活方式和科学的所有条目的组合。我想删除“娱乐”、“生活方式”和“科学”，只保留“文章”表，从而节省空间并提高性能。我面临的唯一问题是通过获取表的最后一个 ID，然后获取 6 和最大 ID 之间的随机数来生成随机文章的查询。

我的所有表格都具有以下结构:id(唯一)、category(文章类型，即娱乐)、title(文章标题)、image(文章图片网址)、link(文章网址)、Counter(文章数量)这篇文章的 View )和dateStamp(文章的发布日期)。这是我尝试根据规范化数据库更新的查询。

<?php
  //SELECT the MAX id from the Entertainment table
    $MAX_ID = $db->query("SELECT MAX(id) FROM Entertainment");

  //Get Max id value
    $MAX_ID = $MAX_ID->fetch_array();
    $MAX_ID = $MAX_ID[0];

   //Create random number variable
    $RAND_NUM;

  //If the Max ID is less than 6, make $MAX_ID the $RAND_NUM
    if ($MAX_ID < 6) {
        $RAND_NUM = $MAX_ID;

    } 

  //Else get a random value between 6 and the Max ID
    else {
        $RAND_NUM = mt_rand(6, $MAX_ID);

    }

  //Grab 6 articles by descending from the random number, example: If $RAND_NUM is 7, get all entries from 7-2
    $resultSet = $db->query("SELECT * FROM Entertainment WHERE id <= $RAND_NUM ORDER BY id DESC LIMIT 6");

    if ($resultSet->num_rows != 0) {
      //Booleans to check where we are at in the print off
        $conditional  = true;
        $conditional2 = true;
        echo "<div class='row'>";
        while ($rows = $resultSet->fetch_assoc()) {
            $image = $rows["image"];
            $title = $rows["title"];
            $link  = $rows["link"];
            $count = number_format($rows["Counter"]);

           //Print off these articles on the left
            if ($conditional == true && $conditional2 == true) {
                echo "<div class='left'><a href='$link'><img src=$image><p>$title</p></a><p id='article-views'><span class='glyphicon glyphicon-fire'></span> $count Views</p></div>";

                $conditional2 = false;
            }

           //Print off these articles on the right
            else {
                echo "<div class='right'><a href='$link'><img src=$image><p>$title</p></a><p id='article-views'><span class='glyphicon glyphicon-fire'></span> $count Views</p></div><hr>";

                $conditional2 = true;
            }


        }

        echo "</div>";
    }


?>

我应该如何更改/更新此查询才能从“文章”表中获取随机的娱乐文章？我也愿意接受任何可以提高查询性能的更改。

Answer 1

无需选择随机结束 ID，只需随机化所有行并从中选择 6 行即可。

SELECT *
FROM Articles
WHERE category = 'Entertainment'
ORDER BY RAND()
LIMIT 6

如果您需要避免 ORDER BY RAND() 因为它的性能不够好，请参阅 How can i optimize MySQL's ORDER BY RAND() function?