How can I put rows of MySQL data under the appropriate titles using PHP?
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, 开发者_如何学Cor if there's a better way to organize my data to make it easier.
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
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...
精彩评论