How can I put rows of MySQL data under the appropriate titles using PHP?

Question

I have the following MySQL table structure:

num field company phone     website

1   Gas   abcd    123456789 abcd.com
2   Water efgh    987654321 efgh.com
3   Water ijkl    321654987 ijkl.com
4   Heat  mnop    987654321 mnop.com
5   Gas   qrst    123789654 qrst.com
...

Is it possible with PHP (maybe using some mixture of GROUP_BY and ORDER_BY) to echo the data to the screen in the following format:

Gas:
abcd       qrst
123456789  123789654
abcd.com   qrst.com

Water:
efgh       ijkl
987654321  321654987
efgh.com   ijkl.com

Heat:
mnop
321654987
mnop.com

The exact format of it isn't important. I just need for the different rows of data to be listed under the appropriate field with none of the fields repeated. I've been trying to figure this out for a while now, but I'm new to PHP and I can't seem to figure out how to do this, if it's even possible, or if there's a better way to organize my data to make it easier.

Answer 1

To avoid performing a "Gas" query, a "Water" query and a "Heat" query, you could order the results by "field" and then handle the display in PHP...

SELECT
    Field,
    Company,
    Phone,
    Website
FROM
    tblYourTable
ORDER BY
    Field

In your PHP loop, you would need to keep tabs on your current "Field" and start a new list when it changes. For example:

$CurrentField = '';

... loop

if ($MyData->Field != $CurrentField) {
    $CurrentField = $MyData->Field;
    ....
}

... end loop

Answer 2

I will assume that you know how to retrieve MySQL data into an array... so, we have:

[0] {
    num => 1,
    field => "Gas",
    company => "abcd",
    phone => "123456789",
    website => "abcd.com"
    }
[1] ... (so on)

Then create a loop like:

foreach($data as $row) {
   $service = $row["field"]; //Water, Gas, etc...
   unset($row["field"]); //do not add this
   foreach($row as $key => $value) {
       $field[$service][$key][] = $value;
   }
}

The resulting array will be something like:

$field["Gas"]["company"][0] = "abcd";
$field["Gas"]["company"][1] = "qrst";
$field["Water"]["company"][0] = "efgh";
...
$field["Gas"]["phone"][0] = "123456789";
$field["Gas"]["phone"][1] = "123789654";
$field["Water"]["phone"][0] = "987654321";
...

In that way you can then generate the output:

foreach($field as $service => $infoarr) {
   echo $service."\n";
   foreach($infoarr as $info => $datarr) {
       foreach($datarr as $datum) {
          echo $datum."\t"; 
       }
       echo "\n";
   }
   echo "\n";
}

Theorically (untested) will output:

Gas 
abcd qrst 
123456789 123789654 

Water 
efgh ...

I hope you find it useful... There should be a better way, but I didn't thought too much about it...