Categorising a large mysql result with php while?

Question

I'm using the following to grab my large result set from a mysql db:

$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());

This returns a list of all items that the user owns. I'm then needing to categorise these items based on the value of the "topictype" column, which Im currently doing by using:

    <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);
    ?>

This works fine when I rinse and repeat the code, but as the site grows I expect I'll run into problems as the number of "topictype" options increases (expecting around 30 categories). I dont want to have to make seperate queries for each categorised group of discs either, as Id eventually have 30 queries being run as categories increase, so hoping to hear some suggestions or alternative approaches :)

Thanks

Answer 1

you can fetch that only once, group by categories and then display each one:

$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>";
    }
  }
}

edit: if $cur_img['topictype'] does not match your category h2 title, you can have a "hash-array" like this:

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

and replace echo '<h2>', $category, '</h2>'; with echo '<h2>', $categories_names[$category], '</h2>';

Answer 2

you might try adding " ORDER BY t.topictype" and then emitting the header when the category changes

$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 3

Code used in response to dar7yls suggestion is as shown below, and displaying only one disc per category:

$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>";
    }
}
}