将行添加到html表
问题描述:
我得到一个html表,从数据库获取信息并在水平和垂直方向上显示它。 我设法只显示用户名,但我需要添加全名和ID也。 这是我走到这一步:将行添加到html表
查询:
$conn = mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME);
// 1.Get data
// data for final table
// format is [username][projectNo] => [process1, process2, ..., processN]
$result = [];
// map project no to its title
$projectNoToTitle = [];
$sql = '
SELECT uid, username, staff_id, longname
FROM `user`
ORDER BY username
';
$query = mysqli_query($conn, $sql);
// for each user
while ($data = mysqli_fetch_assoc($query)) {
$sql2 = '
SELECT a.* FROM
(
(
-- select pairs project - leader
SELECT p.projectNo, p.title, CONCAT(pr.process, "(ld)") AS process
FROM project p
LEFT JOIN proc_leader pr ON p.projectNo = pr.projectNo
AND pr.proc_leader = "' . mysqli_real_escape_string($conn, $data['username']) . '"
)
-- union all means we union result of queries, which have structure
-- and don\'t remove duplicates (it\'s faster than UNION and
-- more logical because in our sittuation it won\'t be any duplicates)
UNION ALL
(
-- select pairs project - checker
SELECT p.projectNo, p.title, CONCAT(pch.process, "(chk)") AS process
FROM project p
LEFT JOIN proc_checker pch ON p.projectNo = pch.projectNo
AND pch.proc_checker = "' . mysqli_real_escape_string($conn, $data['username']) . '"
)
UNION ALL
(
-- select pairs project - checker
SELECT p.projectNo, p.title, CONCAT(pch.process, "(staff)") AS process
FROM project p
LEFT JOIN proc_staff pch ON p.projectNo = pch.projectNo
AND pch.proc_staff = "' . mysqli_real_escape_string($conn, $data['username']) . '"
)
) AS a
ORDER BY a.projectNo';
$query2 = mysqli_query($conn, $sql2);
// for each project => process pair of user
while ($data2 = mysqli_fetch_assoc($query2)) {
$staff_id = $data['staff_id'];
$longname = $data['longname'];
$username = $data['username'];
$projectNo = $data2['projectNo'];
$projectTitle = $data2['title'];
$process = $data2['process'];
$projectNoToTitle[$projectNo] = $projectTitle;
if (!isset($result[$username])) {
$result[$username] = [];
}
if (!isset($result[$username][$projectNo])) {
$result[$username][$projectNo] = [];
}
if ($process) {
$result[$username][$projectNo][] = $process;
}
}
}
而且表:
<table style="background-color:rgb(238, 238, 238)" id="dataTable4" class="tablesorter" class="tblD" border="0" cellpadding="0" cellspacing="1">
<?php
// 2. Output table
// create table header
// it's columns should contain all projects
if ($result) {
$header =
'<th>Staff ID</th>
<th>Full Name</th>
<th>Username</th>' .
array_reduce(array_values($projectNoToTitle), function ($p, $n) {
return $p . '<th>Project ' . htmlspecialchars($n) . '</th>';
});
// output body
$body = '';
foreach ($result as $username => $usernameData) {
$row = '<td>' . htmlspecialchars($username) . '</td>';
foreach ($projectNoToTitle as $projectNo => $projectTitle) {
$r = isset($usernameData[$projectNo])
? implode(', ', $usernameData[$projectNo])
: 'N/A';
$row .= '<td>' . htmlspecialchars($r) . '</td>';
}
$body .= "<tr>$row</tr>";
}
echo "<thead>$header</thead><tbody>$body</tbody>";
}// \2. Output table
?>
</table>
我如何添加$staff_id = $data['staff_id']; $longname = $data['longname'];
表?
答
你可以在你的文章中更新数据库结构。
在您的第一个代码块中,您需要将它们添加到您的'$ result [$ username]'数组中,即。 '$ result [$ username] ['staff_id'] = $ staff_id; $ result [$ username] ['longname'] = $ longname;'。然后在你的第二块你会得到它们在$ usernameData ['staff_id']'和'$ usernameData ['longname']' – Sean
@sean你是说我需要在这里添加它吗? 'if(!isset($ result [$ username])){ \t \t \t \t $ result [$ username] = []; \t \t \t \t $ result [$ username] ['staff_id'] = $ staff_id; \t \t \t \t $ result [$ username] ['longname'] = $ longname; \t \t \t} \t \t \t如果{ \t \t \t \t $结果[$用户名] [$ projectNo] = [](isset($结果[$用户名] [$ projectNo])!); \t \t \t} \t \t \t如果($过程){ \t \t \t \t $结果[$用户名] [$ projectNo] [] = $过程; \t \t \t}' –