How to display query results under headings derived from another field

[vik1313]

Hi all,

Please I'm a newbie in this and have results from a GROUP BY (grouped by col2) query in the format.

col1 | col2
-----------
a1 | a
a2 | a
a3 | a
a4 | a
b1 | b
b2 | b
b3 | b


I wish to display the results in the format
----
a
----
a1
a2
a3
a4
----
b
----
b1
b2
b3

I'll appreciate any help on how to go about this.

[Old Pedant]

Display using what? PHP? ASP? Java? C++? What?

The basic answer is the same:

... get the records ...

priorCol2Value = ""

loop on the records 
{
    if currentCol2Value <> priorCol2Value
    {
        ... output a heading for the new currentCol2Value ...
    }
    priorCol2Value = currentCol2Value
    ... output col1 info ...

    next record
} // end of loop

[vik1313]

Originally Posted by Old Pedant

Display using what? PHP? ASP? Java? C++? What?

The basic answer is the same:

... get the records ...

priorCol2Value = ""

loop on the records 
{
    if currentCol2Value <> priorCol2Value
    {
        ... output a heading for the new currentCol2Value ...
    }
    priorCol2Value = currentCol2Value
    ... output col1 info ...

    next record
} // end of loop

Hi Pedant,

Thanks for your reply and sorry for my omission. I'm using PHP and MySQL.
So how do I implement it using PHP.

Cheers!

[Old Pedant]

I'm not a PHP person, but pretty much what I showed you:

$priorCol2Value = "";

while( ...there are more records... ) 
{
    $currentCol2value = ...get that column from your PHP record...;
    if ( $currentCol2Value != $priorCol2Value )
    {
        echo "----<br/>" . $currentCol2Value . "<br/>----<br/>\n";
        $priorCol2Value = $currentCol2Value;
    }
    $col1Value = ...get that column from your PHP record...;
    echo $col1Value . "<br/>\n";
}

As I recall, with PHP the code in the while( ... ) *will* get the next record, so that's about all you need.

[vik1313]

Originally Posted by Old Pedant

I'm not a PHP person, but pretty much what I showed you:

$priorCol2Value = "";

while( ...there are more records... ) 
{
    $currentCol2value = ...get that column from your PHP record...;
    if ( $currentCol2Value != $priorCol2Value )
    {
        echo "----<br/>" . $currentCol2Value . "<br/>----<br/>\n";
        $priorCol2Value = $currentCol2Value;
    }
    $col1Value = ...get that column from your PHP record...;
    echo $col1Value . "<br/>\n";
}

As I recall, with PHP the code in the while( ... ) *will* get the next record, so that's about all you need.

Hi Pendant,

Thanks for your assistance, I've tried to implement it with the layout above it worked BUT THEN seems to skip the first row in col1.

Outputs:
a
---
a2
a3
a4

b
---
b1
b2
b3

Here's my code:
$priorcol2 = "";
while($row = mysql_fetch_array($result))
{

$currentcol2 = $row['col2'];
if($currentcol2 != $priorcol2)
{
echo $row['col2'];
$priorcol2 = $currentcol2;
}
echo $row['col1'];
}

I guess it's from the loop structure but have not been able to fix it.

[vik1313]

Ok, sorry I've fixed it.

Thanks a lot.