MySQL GROUP with or with PHP?
I have a problem that seems calm, but I would just like to ask how you solve it:
The following structure exists in the MySQL table:
provider artist
a 1
a 2
a 3
b 4
Now you need an echo list in HTML, for example:
provider a
1
2
3
provider b
4
I am stuck at the point where I would like to group the results and output them using loops for-each
and while
.
The basic idea is simple:
<?php $query = mysqli->query("SELECT * FROM `table` GROUP by provider");
foreach GROUP {?>
echo some styling HTML-code for each group of headline;
<?php while ($data= $query->fetch_assoc()){?>
echo some styling HTML-code for each list-item;
<?php};?>
Thanks in advance.
UPDATE:
Thanks for answering.
The solution for RiggsFolly seems to work fine. There is only a small problem with HTML. There is a surrounding div tag that will not be closed when HTML is added to this line:
echo 'provider '. $data->provider;
The problem is that the while loop must be in a div. There is no closing div tag for each
if ( $current_provider != $data->provider ) {
Original HTML Code:
<?php
$service = $db->query("SELECT * FROM `system` ORDER BY provider, artist");
$current_provider = NULL;
while ($data = $service->fetch_object()) {
if ( $current_provider != $data->provider ) {// new provider?>
<div class="service">
<p class="lower">
<?php echo $data->provider;?>
</p>
<?php
$current_provider = $data->provider;
}?>
<a href='?artist=<?php echo $data->artist;?>'><?php echo "/".$data->artist;?</a><br/>
<?php };?>
</div><!--service -->
List items will show well. But when looking at the source code, you can see that the closing div tag is missing. Thanks to
Sincerely.
source to share
It would seem easier not to use GROUP BY
, especially if it doesn't provide you with the data you need. So instead just select them all and sort them by provider and possibly artist as a subfield like this
<?php
$result = $mysqli->query("SELECT * FROM `table` ORDER BY provider, artist");
$current_provider = NULL;
while ($data = $result->fetch_object()){
if ( $current_provider != $data->provider ) {
// new provider
echo 'provider '. $data->provider;
$current_provider = $data->provider;
}
echo $data->artist;
}
?>
AFTER UPDATE:
<?php
$service = $db->query("SELECT * FROM `system` ORDER BY provider, artist");
$current_provider = NULL;
while ($data = $service->fetch_object()) {
if ( $current_provider != $data->provider ) {
if ( $current_provider !== NULL ) {
echo '</div>';
}
echo '<div class="service">';
echo '<p class="lower">' . $data->provider . '</p>';
$current_provider = $data->provider;
}
echo '<a href="?artist=' . $data->artist '">' .
$data->artist . '</a><br/>';
}
echo '</div>';
source to share
How about this.
<?php $query = mysqli->query("SELECT * FROM `table`");
$current_group = array();
while ($data= $query->fetch_assoc()){
if(in_array($data['provider'],$current_group)){
echo "<h1>New Group" . $data['provider'] ."</h1>";
$current_group[] = $data['provider']
}
echo $data['artist'] . "<br/>";
}
source to share