我有一个while循环,从MySQL数据库获取信息,通过php输出信息。我的代码是这样的:
while($row = mysql_fetch_array($result)) {
$output .= '<tr>' . "'r";
$output .= '<td>' . $row['lnID'] . '</td>' . "'r";
$output .= '<td>' . $row['lnLine'] . '</td>' . "'r";
$output .= '<td>' . $row['lnTitle'] . '</td>' . "'r";
$output .= '<td>' . $row['cLongName'] . '</td>' . "'r";
$output .= '</tr>' . "'r";
}
cLongName
在数据库中可以有多个条目,因此它以以下形式输出数据:
lnID | lnLine | lnTitle | cLongName
---------------------------------------
lnID1 | lnLine1 | lnTitle1 | cLongname1
lnID1 | lnLine1 | lnTitle1 | cLongname2
lnID1 | lnLine1 | lnTitle1 | cLongname3
lnID2 | lnLine2 | lnTitle2 | cLongname1
lnID2 | lnLine2 | lnTitle2 | cLongname2
lnID2 | lnLine2 | lnTitle2 | cLongname3
lnID2 | lnLine2 | lnTitle2 | cLongname4
lnID2 | lnLine2 | lnTitle2 | cLongname5
我想要这种格式的数据:
lnID | lnLine | lnTitle | cLongName
---------------------------------------------------------------------------------------
lnID1 | lnLine1 | lnTitle1 | cLongname1, cLongname2, cLongname3
lnID2 | lnLine2 | lnTitle2 | cLongname1, cLongname2, cLongname3, cLongname4, cLongname5
我试过在while循环中把foreach循环搞砸,但没有成功。任何想法或帮助都将不胜感激。
--编辑--
我的MySQL查询如下:
$result = mysql_query("
SELECT
pcdb_line.lnID, pcdb_line.lnLine, pcdb_line.lnTitle,
pcdb_colourways.cwLine, pcdb_colourways.cwColID1,
pcdb_colour_name.cID, pcdb_colour_name.cLongName, pcdb_colour_name.cHex
FROM pcdb_line
GROUP BY pcdb_line.lnID
LEFT JOIN pcdb_colourways
ON (pcdb_line.lnID = pcdb_colourways.cwLine)
LEFT JOIN pcdb_colour_name
ON (pcdb_colour_name.cID = pcdb_colourways.cwColID1)
LIMIT 50
");
根据ADW的回答,我只在cLongName
和所有pcdb_colour_name
上尝试了group_concat
,但它没有给我任何输出。
--编辑2-
将SELECT
查询更改为:
$result = mysql_query("
SELECT
pcdb_line.lnID, pcdb_line.lnLine, pcdb_line.lnTitle,
pcdb_colourways.cwLine, pcdb_colourways.cwColID1,
pcdb_colour_name.cID, group_concat(pcdb_colour_name.cLongName) AS cLongNames, pcdb_colour_name.cHex
FROM pcdb_line
LEFT JOIN pcdb_colourways
ON (pcdb_line.lnID = pcdb_colourways.cwLine)
LEFT JOIN pcdb_colour_name
ON (pcdb_colour_name.cID = pcdb_colourways.cwColID1)
GROUP BY pcdb_line.lnID
LIMIT 50
");
允许我在php上使用$output .= "'t't" . '<td>' . $row['cLongNames'] . '</td>' . "'r";
,为我提供了所需的输出。
您最好将数据库查询修改为以下内容:
select lnID,lnLine,lnTitle,group_concat(cLongName) from table group by lnID,lnLine,lnTitle;
作为@ADW答案的额外注释,如果您使用整数,您可能需要首先键入强制转换:
select lnID,lnLine,lnTitle,group_concat(CAST(cLongName as CHAR)) as cLongNames from table group by lnID,lnLine,lnTitle;
然后你会用它作为:
$result = mysql_query("
SELECT
pcdb_line.lnID, pcdb_line.lnLine, pcdb_line.lnTitle,
pcdb_colourways.cwLine, pcdb_colourways.cwColID1,
pcdb_colour_name.cID, group_concat( pcdb_colour_name.cLongName ) as cLongNames, pcdb_colour_name.cHex
FROM pcdb_line
GROUP BY pcdb_line.lnID
LEFT JOIN pcdb_colourways
ON (pcdb_line.lnID = pcdb_colourways.cwLine)
LEFT JOIN pcdb_colour_name
ON (pcdb_colour_name.cID = pcdb_colourways.cwColID1)
LIMIT 50
");
然后您将以$row['cLongNames']
的形式访问它