解析多行php的一个col [英] Parsing one col of multiple rows php

问题描述

我有查询返回的以下数据，每个名称都是一个项目:

I have e.g. the following data returned from a query, each name is an item:

id      name      comment
1       FF        hey
1       FF        hey back!
2       LL         
3       PP        i think its great!
3       PP        me too
3       PP        I'm not sure
4       TT         
5       II      
6       KK        yesterday is the new tomorrow

当我显示它时，每个项目"都有一个ID，并使用LI在DIV中显示.

When I display it, each 'item' has an id and are displayed in DIVs use LI.

您可以看到，尽管有时针对一个项目"有多个注释，但每个注释都在单独的行中

As you can see though there are multiple comments sometimes for an 'item', each on a separate line

我想做的是显示每个项目，然后在每个项目下显示注释(如果有).因此，由于注释部分是唯一的，因此我无法在查询阶段进行任何分组，但需要在显示阶段进行分组

What I want to do is display each item and then show comments under each item if there are any. So, i can't group by anything at query stage as the comment section is unique, but need to group at display stage

目前有:

while ($row = mysql_fetch_array($result)){

echo '<li><div class=className><div class=itemName>'.$row[name].'</div>';

    if($row[comment]){ 

       echo '<div class=newRow>'.$row[comment].'</div>'; 

    }

echo '</div></li>';

}

现在，这不好，因为这将为同一项目产生多个显示，每个显示下有一个注释.

Now, this is no good because this will produce multiple displays for the same item with one comment under each.

我可以这样做还是应该以其他方式输入数据?

Can I do this or should I bring in the data differently?

理想的结果是

FF             LL             PP                       etc etc etc
hey                           i think its great!
hey back!                     me too
                              I'm not sure

推荐答案

您可以使用

You can use GROUP_CONCAT() on your mysql query to group all the comments together for each name

SELECT id, name
GROUP_CONCAT(comment) AS comment
FROM table
GROUP BY name;

然后在您的php代码中 explode() $row[comment]

then explode() the $row[comment] in your php code

while ($row = mysql_fetch_array($result)){

echo '<li><div class=className><div class=itemName>'.$row['name'].'</div>';

    if($row['comment'] != ""){

       $comments = explode(",",$row['comment']);
       foreach($comments as $comment){
               echo '<div class=newRow>'.$comment.'</div>';
       } 

    }

echo '</div></li>';

}

修改
感谢@CBroe，我现在知道GROUP_CONCAT()具有

Edit
Thanks to @CBroe, I now know that GROUP_CONCAT() has a group_concat_max_len default of 1024. You will want to increase this before running the GROUP_CONCAT() query -

SET [GLOBAL | SESSION] group_concat_max_len = 10240; // must be in multiples of 1024
SELECT id, name
GROUP_CONCAT(comment) AS comment
FROM table
GROUP BY name;

您还需要注意 max_allowed_packet ，因为这是您可以将var_group_concat_max_len设置为的限制.

you will also need to be aware of max_allowed_packet as this is the limit you can set var_group_concat_max_len to.

注意:mysql_query()不允许多个查询，因此您需要执行2个mysql_query()，并且可以使用SET SESSION ...，以便当前会话中的所有查询都具有该max_len.最好从已折旧的mysql_函数更改为 mysqli_ 或 PDO 提供多个查询选项.还要签出- http://php.net/manual/zh-cn/mysqlinfo.api .choosing.php

note: mysql_query() does not allow multiple queries, so you will need to do 2 mysql_query(), and you can use SET SESSION ... so that all queries in your current session have that max_len. It would be better to change from mysql_ functions (which are depreciated) and change to mysqli_ or PDO as they offer multiple query option. also check out - http://php.net/manual/en/mysqlinfo.api.choosing.php

这篇关于解析多行php的一个col的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！