Categorising a large mysql result with php while?
- by Ryan
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
