How to display query results under headings derived from another field

[Vikk]

Hi all,

Please I'm a newbie in PHP and MySQL, I 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.

[Atli]

Hey.

Try something like:

<?php
$dbLink = new mysqli('host', 'user', 'pwd', 'db');

$result = $dbLink->query("... Put your query here ...");
if($result)
{
    $data = array();
    
    // Loop through all the returned rows and group the values
    // into the $data array based on the 'col2' field.
    while($row = $result->fetch_assoc($result)
    {
        $data[$row['col2']][] = $row['col1'];
    }
    
    // Loop through the 'col2' values and print a list of
    // 'col1' values for each of them.
    foreach($data as $_col2 => $_col1_list)
    {
        echo "----\n{$_col2}\n----\n";
        foreach($_col1_list as $_col1)
        {
            echo $_col1, "\n";
        }
    }
}
else
{
    echo "Your query phailed! " . $dbLink->error;
}
?>

If you are trying to do this sort of formatting using MySQL alone... don't. MySQL is a database system, meant to store data and return it in a very basic way.
Formatting the data for output is a job for the front-end application. (PHP, in this case.)

[Vikk]

Hi Atli,

Thanks for your response.

I don't intend to format with MySQL, just used the '----' here for clarity purposes.

Cheers