从MySQL查询中“分组”PHP结果

Question

I'm having difficulty achieving this one, so thought I'd ask..

I have a table which contains some field names. Each field entry in the table has a type, a name and an ID as per the norm.

I'd like to be able to group my SQL results so that I can 'print' each type (and the type's associated members) into a table. I tried the following:

$query = "SELECT id,type,opt_name FROM fields ORDER BY type";  
$resource = $pdo->query($query);
$results = array();
while ( $row = $resource->fetch() ) {   
$type[$row['type']] = $row['opt_name'];
$o_id = clean($row['id'], 'text');
$o_name = clean($row['opt_name'], 'text');
}

foreach ($type AS $o_type => $o_name) {
    echo $o_type;
    echo "<br>";

    foreach (($type) AS $opt_name) { 
        echo $opt_name;
        echo "<br>";
         }
 }

Please try not to worry too much about the HTML output, it's just for this example to show context.

When the code is executed, it will only print one row. As a result, I have 'print_r' tested both:

$type[$row['type']] = $row['opt_name'];

Which does indeed show only one row in the array. Creating:

$results = $row;

Shows all the data, but too much of it and not in the way I think will work with the 'foreach'.

I'm probably making a rookie mistake here, but can't see it.

Help!

Answer 1

You're going to be overwriting the value of $type['type'] instead of pushing more values onto it with:

$type[$row['type']] = $row['opt_name'];

You most likely want

$type[$row['type']][] = $row['opt_name'];

Then you'll need to fix your loop so you loop through the options, not the types twice.

foreach ($type AS $o_type => $options) {
    echo $o_type;
    echo "<br>";

    foreach ($options AS $opt_name) { 
        echo $opt_name;
        echo "<br>";
    }
}