There is an html table that displays data from the database vertically and horizontally. Only the nickname is displayed, you need to add the full name as well as ID. What is available at the moment: Query:

$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; } } }

And the table itself:

  <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>

How can I add $staff_id = $data['staff_id']; $longname = $data['longname']; $staff_id = $data['staff_id']; $longname = $data['longname']; to the table?

    1 answer 1

    Something like that:

      <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($longname) . '</td>' . '<td>' . htmlspecialchars($staff_id) . '</td>' . '<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>

    • in this case, the system prints the last name for each row of the table. that is, username and project as necessary. But longname, staff_id is taken from the last name in the database and printed for the entire table. - Yevgeniy Bagackiy