php - MySql遍历一列以形成串联字符串

我是SQL和PHP的新手。我有一个要求最终用户以HTML形式输入文本，我将其存储在MySQL数据库中。稍后，我查询数据库中具有用户数据的所有表，以获取串联的字符串。

为了更清楚地了解，这里是我的数据和所需结果的图像：Click for Image



现在，我只能通过此查询基于特定的ID连接一个字符串：

select group_concat(text order by T.did separator ' ') as TTT
from (
  select l.dimgroupid as did, d.*,  l.dimlevelid as id, l.dimlevelvalue,
     concat(d.dimensiontext," ",l.dimlevelvalue) as text
  from dimensionlevel l
  join dimension d on d.dimensionid = l.dimid
  join dimensiongroup g on g.groupid = l.dimgroupid) as T
where T.id=0;

这给了我结果

| TTT                          |
--------------------------------
| A 20 year old Man is Fresher |

我知道我可以在ID上使用循环，但是问题是ID没有固定。

注意：可以有“ n”个尺寸和级别。

这是我要查询的数据的SQL Fiddle：SQL Fiddle

最佳答案

我已编辑您的查询以获取您想要的。

    select did as groupid, group_concat(text order by dimensionid separator ' ' ) as TTT
    from (
           select l.dimgroupid as did, d.*,  l.dimlevelid as id, l.dimlevelvalue, concat(d.dimensiontext," ",l.dimlevelvalue) as text
           from dimensionlevel l
           join dimension d on d.dimensionid = l.dimid
           join dimensiongroup g on g.groupid = l.dimgroupid
         ) as T
    group by id;

我希望这可以帮助你...

更新：
使用PHP ..

$sql = "SELECT `dimensionid`, `dimensiontext`, `dimensiondescr` FROM `dimension`";
$dimensions = mysqli_fetch_all (mysqli_query($con, $sql));

$sql = "SELECT `dimlevelvalue` FROM `dimensionlevel` WHERE dimid IN (SELECT dimensionid FROM dimension WHERE dimensiondescr='age') ";
$ages = mysqli_fetch_all (mysqli_query($con, $sql));

$sql = "SELECT `dimlevelvalue` FROM `dimensionlevel` WHERE dimid IN (SELECT dimensionid FROM dimension WHERE dimensiondescr='sex') ";
$sexes = mysqli_fetch_all (mysqli_query($con, $sql));

$sql = "SELECT `dimlevelvalue` FROM `dimensionlevel` WHERE dimid IN (SELECT dimensionid FROM dimension WHERE dimensiondescr='experience') ";
$experiences = mysqli_fetch_all (mysqli_query($con, $sql));

foreach ($ages as $age) {
    foreach ($sexes as $sex) {
        foreach ($experiences as $exp) {

            foreach ($dimensions as $dimension) {
                $putage = "";
                $putsex = "";
                $putexp = "";
                if($dimension[2]=="Age"){
                    $putage = $age[0];
                }elseif($dimension[2]=="Sex"){
                    $putsex = $sex[0];
                }elseif($dimension[2]=="Experience"){
                    $putexp = $exp[0];
                }
                echo $str = $dimension['1']." ".$putage.$putsex.$putexp." " ;
            }
            echo "<br>";
        }
        echo "<br>";
    }
}

关于php - MySql遍历一列以形成串联字符串，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45255534/