Simple PHP - DISTINCT vs Second Query

[EternalSorrow]

I'm currently trying to create a script for my design table from scratch, and I've run into an early problem.  I have three different types for my designs, and each one has their own respective designs.  My problem is on the index page I want to use a single script to show all three types, with three of their designs listed beneath each header type.  It would appear something like this:

 

Type1

Type1_Design1 | Type1_Design2 | Type1_Design3

 

Type2

Type2_Design1 | Type2_Design2 | Type2_Design3

 

Type3

Type3_Design1 | Type3_Design2 | Type3_Design3

 

What I'm having problems with is the initial query.  I need to either group the types together, or use the DISTINCT command to group them together.  The first difficulty is that with the DISTINCT command, my second query which gathers together and lists the respective designs won't have anything but the type field from which to retrieve table information, so I get a random picture for all results shown.

 

The second difficulty is in using the GROUP BY command.  Grouping by the type means the second query will only see one design for each type, because all others have been eliminated because of the common type.

 

In summary, I need to group the designs under their respective types, but I can't figure out how to accomplish that with the code I have.  Does anyone have any suggestions on how to get around this problem?

 

Here's the code for reference:

<?php

$query = "SELECT * FROM design GROUP BY type ORDER BY type ASC";
$result = mysql_query($query) or die (mysql_error());

while ($row = mysql_fetch_array($result))
{
extract($row);

$select_single = mysql_query("SELECT * FROM design WHERE type = '$type' LIMIT 3");

$cat = "";
while ($row2 = mysql_fetch_array($select_single)) {
   $cat .= "<td><a href=\"graphics/preview.php?id=$id\"><img src=\"previews/$image\" class=\"tips\" title=\"$title|$series\" alt=\"\"></a>";
}

echo '<ul class="head">
<li class="header">'.$type.'</li>
<li class="last"><a href="type.php?type='.$type.'">View More?</a>
</ul>
<table align="center" cellpadding="6"><tr>
'.$cat.'
</table>';

}
?>

[Psycho]

I really can't follow where the designs come into play in your code - only the types. So, I'll just provide some "mock" code close to what you provided. As long as you don't have a very large number of records, just query all of them. Then use PHP to only show three from each type.

 

Example:

<?php

function displayTypeDesigns($typeStr, $designsAry)
{
    if (!$typeStr || count($designsAry)==0)
    {
        return false;
    }
    $typeOutput  = "<b><u>{$typeStr}</u></b><br />\n";
    $typeOutput .= implode(' | ', $designsAry) . "<br /><br />\n";
    return $typeOutput;
}

$query = "SELECT * FROM design ORDER BY type, series";
$result = mysql_query($query) or die (mysql_error());

$currentType = false;
$designList  = array();
$output = '';

while ($row = mysql_fetch_array($result))
{
    extract($row);

    if ($currentType!=$type)
    {
        //Create output for 1st 3 records of last type
        $output .= displayTypeDesigns($currentType, $designList);
        //Reset vars
        $currentType = $type;
        $designList  = array();
    }

    //Add series to current var if less than 3
    if (count($designList)<3)
    {
        $designList[] = $series;
    }
}

//Create output for 1st 3 records of last type
$output .= displayTypeDesigns($currentType, $designList);

echo $output;

?>