使用 php while 对大型 mysql 结果进行分类？

Question

我使用以下命令从 mysql 数据库获取大型结果集：

$discresult = 'SELECT t.id, t.subject, t.topicimage, t.topictype, c.user_id, c.disc_id FROM topics AS t LEFT JOIN collections AS c ON t.id=c.disc_id WHERE c.user_id='.$user_id;
$userdiscs = $db->query($discresult) or error('Error.', __FILE__, __LINE__, $db->error());

这将返回用户拥有的所有项目的列表。然后，我需要根据“topictype”列的值对这些项目进行分类，我目前正在使用以下方法进行分类：

    <h2>Category 1</h2>
    <?php 

    while ($cur_img = mysql_fetch_array($userdiscs)) {
        if ($cur_img['topictype']=="cat-1") {   
            if ($cur_img['topicimage']!="") {
                echo "<div><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\"><img src=\"".$cur_img['topicimage']."\" style=\"width:60px; height: 60px\" /></a><br /><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\">".$cur_img['subject']."</a></div>";
            } else {
                echo "<div><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\"><img src=\"img/no-disc-art.jpg\" style=\"width:60px; height: 60px\" /></a><br /><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\">".$cur_img['subject']."</a></div>";
            }
        }
    }
    mysql_data_seek($userdiscs, 0);
    ?>
    <h2>Category 2</h2>
    <?php 

    while ($cur_img = mysql_fetch_array($userdiscs)) {
        if ($cur_img['topictype']=="cat-2") {   
            if ($cur_img['topicimage']!="") {
                echo "<div><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\"><img src=\"".$cur_img['topicimage']."\" style=\"width:60px; height: 60px\" /></a><br /><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\">".$cur_img['subject']."</a></div>";
            } else {
                echo "<div><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\"><img src=\"img/no-disc-art.jpg\" style=\"width:60px; height: 60px\" /></a><br /><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\">".$cur_img['subject']."</a></div>";
            }
        }
    }
    mysql_data_seek($userdiscs, 0);
    ?>

当我冲洗并重复代码时，这效果很好，但随着网站的增长，我预计我会遇到随着“topictype”选项数量的增加（预计大约 30 个类别），会出现问题。我也不想对每个分类的光盘组进行单独的查询，因为随着类别的增加，我最终会运行 30 个查询，所以希望听到一些建议或替代方法:)

谢谢

Answer 1

您可以尝试添加“ ORDER BY t.topictype”，然后在类别更改时发出标头

$category = "";

while ($cur_img = mysql_fetch_array($userdiscs)) 
{
    if ($cur_img['topictype'] != $category )
    {
        $category = $cur_img['topictype'];
        echo '<h2>', $category, '</h2>';
    }

    .... rest of output here
}

Answer 2

您只能获取一次，按类别分组，然后显示每个类别：

$userdiscs = mysql_fetch_array($userdiscs);
$categories = Array ();
foreach ($userdiscs as $cur_img) {
  $category = $cur_img['topictype'];
  if (isset ($categories[$category])) $categories[$category] = Array();
  $categories[$category][] = $cur_img;
}
foreach ($categories as $category => $imgs) {
  echo '<h2>', $category, '</h2>';
  foreach ($imgs as $cur_img) {  
    if ($cur_img['topicimage']!="") {
      echo "<div><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\"><img src=\"".$cur_img['topicimage']."\" style=\"width:60px; height: 60px\" /></a><br /><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\">".$cur_img['subject']."</a></div>";
    } else {
      echo "<div><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\"><img src=\"img/no-disc-art.jpg\" style=\"width:60px; height: 60px\" /></a><br /><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\">".$cur_img['subject']."</a></div>";
    }
  }
}

编辑：
如果 $cur_img['topictype'] 与您的类别 h2 标题不匹配，您可以使用如下所示的“哈希数组”：

$categories_names = Array (
  'cat-1' => 'Category 1',
  'cat-2' => 'Category 2',
  'cat-3' => 'Category 3'
)

并替换 echo '

', $category , '

'; with echo '

', $categories_names[$category], '

';

Answer 3

响应 dar7yls 建议所使用的代码如下所示，每个类别仅显示一张光盘：

$discresult = 'SELECT t.topictype, t.id, t.subject, t.topicimage, c.user_id, c.disc_id FROM topics AS t LEFT JOIN collections AS c ON t.id=c.disc_id WHERE c.user_id='.$user_id.' ORDER BY t.topictype';
$userdiscs = $db->query($discresult);

$category = "";

while ($cur_img = mysql_fetch_array($userdiscs)) {
    if ($cur_img['topictype'] != $category ) {
    $category = $cur_img['topictype'];
    echo '<h2>', $category, '</h2>';

    if ($cur_img['topicimage']!="") {
        echo "<div><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\"><img src=\"".$cur_img['topicimage']."\" style=\"width:60px; height: 60px\" /></a><br /><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\">".$cur_img['subject']."</a></div>";
    } else {
        echo "<div><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\"><img src=\"img/no-disc-art.jpg\" style=\"width:60px; height: 60px\" /></a><br /><a href=\"viewtopic.php?id=".$cur_img['id']."\" title=\"".$cur_img['subject']."\">".$cur_img['subject']."</a></div>";
    }
}
}