MySQL GROUP通过或使用PHP？ [英] MySQL GROUP by or using PHP?
问题描述
我有一个问题，似乎很安静，但我只想问你将如何解决它：
在MySQL表中有以下结构：
供应商艺术家
a 1
a 2
a 3
b 4
现在需要在HTML中回显一个列表，如下所示：
提供者a
1
2
3
提供者b
4
我坚持希望将结果分组并将结果与进行回显，每个和 while 循环。
主要想法很简单， p>
<？php $ query = mysqli->查询（SELECT * FROM`table` GROUP by provider）;
foreach GROUP {？>
为每组标题回复一些造型HTML代码;
<？php while（$ data = $ query-> fetch_assoc（））{？>
为每个list-item回显一些样式化的HTML代码;
<？php};？>
预先致谢。
更新：
感谢您的回答。
RiggsFolly的解决方案似乎工作正常。 HTML只有一个小问题。有一个周围的div标签，在添加该行的HTML代码时不会被关闭：
echo'provider'。 $数据 - >供应商;
问题是while循环需要在div中。每个
if（$ current_provider！= $ data-> provider）{
以下是原始HTML代码：
<？php
$ service = $ db-> query（SELECT * FROM`system` ORDER BY provider，artist）;
$ current_provider = NULL; （$ 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 - >
列表项目将很好地显示。但是在查看源代码时，可以看到关闭的div标记丢失。谢谢
亲切的问候。
尤其是使用 GROUP BY ，因为它不会为您提供所需的数据。所以相反，只需选择全部并按提供者进行排序，也可以将艺术家排序为子排序。
<？php
$ result = $ mysqli-> query（SELECT * FROM`table` ORDER BY provider，artist）;
$ current_provider = NULL;
（$ data = $ result-> fetch_object（））{
if（$ current_provider！= $ data-> provider）{
// new provider
回声'提供者'。 $数据 - >供应商;
$ current_provider = $ data-> provider;
}
echo $ data-> artist;
}
？>
后续更新：
<？php
$ service = $ db-> query（SELECT * FROM`system` ORDER BY provider，artist）;
$ current_provider = NULL;
（$ data = $ service-> fetch_object（））{
if（$ current_provider！= $ data-> provider）{
if （$ current_provider！== NULL）{
echo'< / div>';
}
echo'< div class =service>';
echo'< p class =lower>'。 $ data->供应商。 < / p为H.;
$ current_provider = $ data-> provider;
}
echo'< a href =？artist ='。$ data-> artist'>'。
$ data->艺术家。 < / A><峰; br />;
}
echo'< / div>';
I have an issue that seems to be quiet easy but I just would like to ask how you would solve it:
In a MySQL-table there is the following structure:
provider artist
a 1
a 2
a 3
b 4
Now it is necessary to echo a list in HTML like:
provider a
1
2
3
provider b
4
I stuck at the point where I would like to group the results and echo them out with for-each and while loop.
The main idea is quiet simple like:
<?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 fro RiggsFolly seems to work fine. There is just a small problem with the HTML. There is a surrounding div-tag that wont be closed when adding the HTML code in that line:
echo 'provider '. $data->provider;
The problem is that the while loop needs to be in the div. The closing div-tag is missing for every
if ( $current_provider != $data->provider ) {
Here is the 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 -->
The list-items will be shown nicely. But when looking into the Source code you can see that the closing div-tag is missing. Thanks
Kind regards.
It would seem simpler to not use a GROUP BY especially as it will not provide you with the data that you want. So instead just select them all and sort them by provider and maybe artist as a sub sort like so
<?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>';
这篇关于MySQL GROUP通过或使用PHP？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
