php - 使用SQL以不同方式输出表

Question

我不确定这是否可能，但我真的很想知道 SQL 是否可以解决这个问题，或者我应该继续使用 PHP 来处理它。

我有一个表格，其中包含来自表单的信息。进行设置，以便 submission 列标识表单条目，field 列表示输入字段的 name 属性，然后是 data 是发布的信息。

看起来像这样:

+---------------------------------------------------------------+
|id   |submission|ref       |field       |data                  |
+---------------------------------------------------------------+
|1    |1         |hox23     |name        |John Doe              |
+---------------------------------------------------------------+
|2    |1         |hox23     |address     |Sesame Street 12      |
+---------------------------------------------------------------+
|3    |1         |hox23     |phone       |5555-1234             |
+---------------------------------------------------------------+
|4    |1         |hox23     |email       |john@doe.ex           |
+---------------------------------------------------------------+
|5    |2         |hox23     |name        |Josh Smith            |
+---------------------------------------------------------------+
|6    |2         |hox23     |address     |Any Street 34         |
+---------------------------------------------------------------+
|7    |2         |hox23     |phone       |5555-5678             |
+---------------------------------------------------------------+
|8    |2         |hox23     |email       |josh@smith.ex         |
+---------------------------------------------------------------+
|9    |3         |hox23     |name        |Jane Summer           |
+---------------------------------------------------------------+
|10   |3         |hox23     |address     |Last Street 4         |
+---------------------------------------------------------------+
|11   |3         |hox23     |phone       |5555-9012             |
+---------------------------------------------------------------+
|12   |3         |hox23     |email       |jane@summer.ex        |
+---------------------------------------------------------------+
|13   |4         |hox23     |name        |Patrick Thom          |
+---------------------------------------------------------------+
|14   |4         |hox23     |website     |www.thom.ex           |
+---------------------------------------------------------------+
|15   |4         |hox23     |phone       |555-1235              |
+---------------------------------------------------------------+
|16   |4         |hox23     |email       |patrick@thom.ex       |
+---------------------------------------------------------------+
|17   |5         |hox23     |name        |Hillary Good          |
+---------------------------------------------------------------+
|18   |5         |hox23     |website     |www.good.ex           |
+---------------------------------------------------------------+
|19   |5         |hox23     |phone       |5555-8365             |
+---------------------------------------------------------------+
|20   |5         |hox23     |email       |hillary@good.ex       |
+---------------------------------------------------------------+
|21   |6         |hox23     |name        |Toby Chalk            |
+---------------------------------------------------------------+
|22   |6         |hox23     |email       |toby@chalk.ex         |
+---------------------------------------------------------------+
|23   |6         |hox23     |website     |www.chalk.ex          |
+---------------------------------------------------------------+
|24   |7         |hox23     |name        |Kat Buo               |
+---------------------------------------------------------------+
|25   |7         |hox23     |email       |kat@buo.ex            |
+---------------------------------------------------------------+
|26   |7         |hox23     |website     |www.buo.ex            |
+---------------------------------------------------------------+
|27   |8         |hox23     |name        |Mill Green            |
+---------------------------------------------------------------+
|28   |8         |hox23     |email       |mill@green.ex         |
+---------------------------------------------------------------+
|29   |8         |hox23     |website     |www.green.ex          |
+---------------------------------------------------------------+
|30   |9         |hox23     |phone       |555-6123              |
+---------------------------------------------------------------+
|31   |9         |hox23     |address     |Some other place 7    |
+---------------------------------------------------------------+
|32   |9         |hox23     |name        |Carl Stuff            |
+---------------------------------------------------------------+

如您所见，每个条目的行数不同，也没有相同的顺序或相同的字段。目前我的 PHP 脚本获取表内容，根据不同的 field 变量创建一个 KEY 数组。然后我再次遍历所有条目，将它们合并到另一个数组中，最终得到一个最终数组，其中包含每个 submission 的一行，每个字段都有数据。

我想知道的是，SQL(目前我正在使用 MySQL)是否可以为我做这件事。有没有办法制作一个动态的 select 语句，它可以输出这样的表:

+---------------------------------------------------------------------------------+
|submission|name        |address           |phone    |email          |website     |
+---------------------------------------------------------------------------------+
|1         |John Doe    |Sesame Street 12  |5555-1234|john@doe.ex    |-           |
+---------------------------------------------------------------------------------+
|2         |Josh Smith  |Any Street 34     |5555-5678|josh@smith.ex  |-           |
+---------------------------------------------------------------------------------+
|3         |Jane Summer |Last Street 4     |5555-9012|jane@summer.ex |-           |
+---------------------------------------------------------------------------------+
|4         |Patrick Thom|-                 |555-1235 |patrick@thom.ex|www.thom.ex |
+---------------------------------------------------------------------------------+
|5         |Hillary Good|-                 |5555-8365|hillary@good.ex|www.good.ex |
+---------------------------------------------------------------------------------+
|6         |Toby Chalk  |-                 |-        |toby@chalk.ex  |www.chalk.ex|
+---------------------------------------------------------------------------------+
|7         |Kat Buo     |-                 |-        |kat@buo.ex     |www.buo.ex  |
+---------------------------------------------------------------------------------+
|8         |Mill Green  |-                 |-        |mill@green.ex  |www.green.ex|
+---------------------------------------------------------------------------------+
|9         |Carl Stuff  |Some other place 7|555-6123 |-              |-           |
+---------------------------------------------------------------------------------+

我当前的选择语句正在寻找 ref 作为它的标识符。我只想获取与此 ID 相关的所有条目。注意列field不一致，不知道每个提交id是3行还是4行。您不知道名称字段是每个条目的第一行还是最后一行，或者名称字段是否存在。我自己发现很难解决这个问题，但我真的很想知道，是否有一种方法可以让 SQL 管理这个设置？

编辑

至少我已经大大减少了我的 PHP 脚本。目前它看起来像这样:

<?php

$conn = new PDO(
    'mysql:host=localhost;dbname=data_loop',
    "username",
    "password",
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

// Reference variable. Can be a posted variable to look up.
$ref = 'hox23';

$sql = $conn->prepare('SELECT * FROM data_loop WHERE ref = :ref GROUP BY field');
$sql->bindParam(':ref', $ref, PDO::PARAM_STR);
$sql->execute();
$res = $sql->fetchAll(PDO::FETCH_ASSOC);

$que = 'SELECT A.submission';

$ry = 'FROM data_loop AS A ';

$i = 'B';

foreach ($res as $key => $value) {
    
    $que .= ', '.$i.'.Data AS '.$value['field'].' ';
    $ry .= 'LEFT JOIN data_loop AS '.$i . ' ON A.submission = '.$i.'.submission '. 'AND '.$i.".field = '".$value['field']."' ";

    $i++;
}

$query = $que.$ry."WHERE A.ref = '".$ref."'";

$stmt = $conn->prepare($query);
$stmt->execute();
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($row as $key => $value) {
    echo $value['name'] . '<br>' . $value['address'] . '<br>' . $value['email'] . '<br>' . $value['phone'] . '<br>' . $value['website'] . '<br>';
}

?>

希望它可以帮助其他人。

Answer 1

我能想到的最简单的方法是自行加入表格。

所以...

    Select A.Submission,
    B.Data AS Name,
    C.Data as Address,
    D.Data as Phone,
    E.Data as Email,
    F.Data as Website
    FROM TableData AS A
    LEFT JOIN TableData AS B
    ON A.Submission = B.Submission
    AND B.Field = "Name"
     LEFT JOIN TableData AS C
    ON A.Submission = C.Submission
    AND C.Field = "Address"
    LEFT JOIN TableData AS D
    ON A.Submission = D.Submission
    AND D.Field = "Phone"
   LEFT JOIN TableData AS E
    ON A.Submission = E.Submission
    AND E.Field = "Email"
   LEFT JOIN TableData AS F
    ON A.Submission = F.Submission
    AND F.Field = "Website"

按照下面的建议更改为左连接(来自 kristof)，您还可以在 select 语句周围放置一些 isnull 以放置“-”代替 NULL (ISNULL(e.Data, "-")) 或 Coalese (感谢克里斯托夫)

编辑:添加到网站中，我没有足够的评论点数(50!)所以是的，你可以动态创建它，我会做的方式需要更多的工作。您需要创建一个文本字符串，该字符串根据您拥有的变量数量构建一个选择语句，然后在构建后执行该字符串。这有点麻烦，但当它工作时它很可爱。我怀疑根据问题的级别，这可能有点太复杂了，所以我建议只在它们出现时添加额外的字段，并在它们不存在时生成“-”