Yevgeniy Bagackiy Yevgeniy Bagackiy - 1 month ago 9
MySQL Question

how to print data horizontally and vertically in table

I am trying to create table with usernames and data assigned to each username.
I have projects and inside each project there are several processes. So want I want to to is to print table with usernames and projects displayed, and then assign each process to the username. Something like this:

username| project TMNT | project LEON | project MAT |
--------+--------------------+----------------+---------------+
barikan | ANM BLD, BGD CUP | N/A | N/A |
beny | N/A | N/A | BGD CUP |
bob | N/A | ANM BLD | N/A |


Where
ANM BLD, BGD CUP
are the processes from each project.

What I am able to do is to print projects as headers horizontally and usernames vertically:

<table class="table table-hover">
<thead>
<tr>
<th>Name</th>
<?php
$result = getRecord();
$count = 0;
if (mysqli_num_rows($result) > 0)
while($row = mysqli_fetch_array($result))
{
$projectNo = $row['projectNo'];
$title = $row['title'];
$code = $row['code'];

echo "<th>".$projectNo." ".$title." ".$code."</th>";
$count++;
}
?>
</tr>
</thead>
<tbody>
<?php

$result3 = getLeader();
if (mysqli_num_rows($result3) > 0)
while($row = mysqli_fetch_array($result3))
{
$username = $row['username'];

echo "<tr>
<td>".$username."</td>";
}
?>
</tbody>




And how my database tables look like:

user table

uid| username| salary|
---+---------+-------+
1 | bob | 0 |
2 | barikan | 0 |
3 | beny | 0 |


project table

projectNo| title|
---------+------+
1610004 | TMNT |
1610005 | LEON |
1610006 | MAT |


process table(where person is assigned to a process)

projectNo| process | proc_leader|
---------+---------+------------+
1610004 | ANM BLD | barikan |
1610004 | BGD CUP | barikan |
1610005 | ANM BLD | bob |
1610006 | BGD CUP | beny |


I know i can use
group_concat
function to concat all processes in for same project in one row but how can i print accordingly to each project?

Sql query i am using to concat processes:

"SELECT proc_leader.projectNo, group_concat(proc_leader.process) AS processes, username
FROM user
LEFT OUTER JOIN proc_leader ON user.username=proc_leader.proc_leader
GROUP BY username, proc_leader.projectNo";

Answer

Assume you have table structure like this (sqlfiddle):

CREATE TABLE `user` (
  `uid` INT(11) UNSIGNED NOT NULL PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  salary INT(11) UNSIGNED NOT NULL DEFAULT 0
);

CREATE TABLE project (
  projectNo INT(11) UNSIGNED NOT NULL PRIMARY KEY,
  title VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE process (
  projectNo INT(11) UNSIGNED NOT NULL,
  process VARCHAR(255) NOT NULL,
  proc_leader VARCHAR(255) NOT NULL,

  FOREIGN KEY(projectNo) REFERENCES project(projectNo),
  FOREIGN KEY(proc_leader) REFERENCES `user`(username)
);

INSERT INTO `user`(uid, username, salary)
VALUES
(1, "bob", 0),
(2, "barikan", 0),
(3, "beny", 0);

INSERT INTO project(projectNo, title)
VALUES
(1610004, "TMNT"),
(1610005, "LEON"),
(1610006, "MAT");

INSERT INTO process(projectNo, process, proc_leader)
VALUES
(1610004, "ANM BLD", "barikan"),
(1610004, "BGD CUP", "barikan"),
(1610005, "ANM BLD", "bob"),
(1610006, "BGD CUP", "beny");

Result snippet with some commets is below:

<?php
// root root :)
$conn = mysqli_connect('127.0.0.1', 'root', 'root', 'test3');

// 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
    FROM `user`
    ORDER BY username
';
$query = mysqli_query($conn, $sql);
// for each user
while ($data = mysqli_fetch_assoc($query)) {
    $sql = '
        SELECT p.projectNo, p.title, pr.process
        FROM project p
          LEFT JOIN process pr ON p.projectNo = pr.projectNo
            AND pr.proc_leader = "' . mysqli_real_escape_string($conn, $data['username']) . '"  
        ORDER BY p.projectNo
    ';
    $query2 = mysqli_query($conn, $sql);
    // for each project => process pair of user
    while ($data2 = mysqli_fetch_assoc($query2)) {
        $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;
        } else {
            $result[$username][$projectNo] = null;
        }

    }
}
// \1.Get data

// 2. Output table
// create table header
// it's columns should contain all projects
if ($result) {
    $header =
        '<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]) && $usernameData[$projectNo] !== null)
                ? implode(', ', $usernameData[$projectNo])
                : 'N/A';
            $row .= '<td>' . htmlspecialchars($r) . '</td>';
        }
        $body .= "<tr>$row</tr>";
    }

    echo "<table><thead>$header</thead><tbody>$body</tbody></table>";
}
// \2. Output table

Feel free to modify if you found some bugs :)