gpt4 book ai didi

php - 如何使用 PHP 在平面 HTML 表中显示规范化的 MySQL 数据?

转载 作者:行者123 更新时间:2023-11-29 13:48:01 27 4
gpt4 key购买 nike

我想以表格格式显示这些数据,例如

<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>City</th>
<th>State</th>
<th>Phone</th>
</tr>

但是我的表格,按列,就像

userdbelemnts_id     userdbelements_field_name      userdbelements_field_value  

180 user_first_name Demo
181 user_last_name Agent
183 City Mumbai
184 zip 400000
185 state xyz
189 phone 123456

如何展平标准化数据以在上述表格结构中显示?

最佳答案

您可以对查询中的输出数据进行非规范化,如下所示(对 userdb_id 进行分组):

SELECT
MAX(CASE WHEN userdbelements_field_name = 'user_first_name'
THEN userdbelements_field_value ELSE NULL END) AS first_name,
MAX(CASE WHEN userdbelements_field_name = 'user_last_name'
THEN userdbelements_field_value ELSE NULL END) AS last_name,
MAX(CASE WHEN userdbelements_field_name = 'City'
THEN userdbelements_field_value ELSE NULL END) AS city,
MAX(CASE WHEN userdbelements_field_name = 'state'
THEN userdbelements_field_value ELSE NULL END) AS state,
MAX(CASE WHEN userdbelements_field_name = 'zip'
THEN userdbelements_field_value ELSE NULL END) AS zip,
MAX(CASE WHEN userdbelements_field_name = 'phone'
THEN userdbelements_field_value ELSE NULL END) AS phone,
FROM userdbelemnts
GROUP BY userdb_id

然后在您的 php 中,就像在平面表格中一样循环遍历结果。

<thead>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>City</th>
<th>State</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
<?php while ($row = mysqli_fetch_assoc($result)) { ?>
<tr>
<td><?=$row['first_name']?></td>
<td><?=$row['last_name']?></td>
<td><?=$row['city']?></td>
<td><?=$row['state']?></td>
<td><?=$row['phone']?></td>
</tr>
<?php } ?>
</tbody>

编辑:根据下面的评论给出您的新表架构:

SELECT
orodha_en_userdb.*,
MAX(CASE WHEN userdbelements_field_name = 'user_first_name'
THEN userdbelements_field_value ELSE NULL END) AS first_name,
MAX(CASE WHEN userdbelements_field_name = 'user_last_name'
THEN userdbelements_field_value ELSE NULL END) AS last_name,
MAX(CASE WHEN userdbelements_field_name = 'City'
THEN userdbelements_field_value ELSE NULL END) AS city,
MAX(CASE WHEN userdbelements_field_name = 'state'
THEN userdbelements_field_value ELSE NULL END) AS state,
MAX(CASE WHEN userdbelements_field_name = 'zip'
THEN userdbelements_field_value ELSE NULL END) AS zip,
MAX(CASE WHEN userdbelements_field_name = 'phone'
THEN userdbelements_field_value ELSE NULL END) AS phone,
FROM orodha_en_userdbelements
INNER JOIN orodha_en_userdb
ON orodha_en_userdbelements.userdb_id = orodha_en_userdb.userdb_id
WHERE orodha_en_userdb.userdb_id = $id
GROUP BY orodha_en_userdb.userdb_id

关于php - 如何使用 PHP 在平面 HTML 表中显示规范化的 MySQL 数据?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17123721/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com