I have an excell file generated from php request. SO what i have inside excel file is: username, project, process. Here is first table username:
uid| username| salary|
---+---------+-------+
1 | bob | 0 |
2 | barikan | 0 |
3 | beny | 0 |
Second table process:
projectNo| process | proc_leader|
---------+---------+------------+
1610004 | ANM BLD | barikan |
1610004 | BGD CUP | barikan |
1610005 | ANM BLD | bob |
1610006 | BGD CUP | beny |
And project table:
projectNo| title|
---------+------+
1610004 | TMNT |
1610005 | LEON |
1610006 | MAT |
Inside my excel file i want to print something like this:
username| TMNT | LEON | MAT |
--------+------------------+---------+---------+
barikan | ANM BLD, BGD CUP | N/A | N/A |
beny | N/A | N/A | BGD CUP |
bob | N/A | ANM BLD | |
I am able to print project and username:
$objPHPExcel = new PHPExcel();
$F = $objPHPExcel->getActiveSheet();
$F->setCellValue('A1', "Username");
$request="SELECT * FROM project";
$result=$conn->query($request);//get the result (ressource)
$Letter='B';
while($row = $result->fetch_assoc())
{//extract each record
$projectNo=$row["projectNo"];
$F->setCellValue($Letter.'1', ' '.$projectNo.' ');
$F->getRowDimension('1')->setRowHeight(20);
$F->getColumnDimension($Letter)->setWidth(30);
++$Letter;
}
$request2="SELECT * FROM user WHERE username='barikan'";
//$request2="SELECT * FROM user ORDER BY username ASC";
$result2= $conn->query($request2);//get the result (ressource)
$Number=2;
while($row2 = $result2->fetch_assoc())
{//extract each record
//$projectNum=$row2["projectNo"];
$username=$row2["username"];
$F->setCellValue('A'.$Number, $row2["username"]);
$F->getColumnDimension('A')->setWidth(20);
++$Number;
}
And still working on how to print processes for each username, but for now i just printing it for one name only:
$request3="SELECT process, projectNo FROM proc_leader INNER JOIN user pl ON (proc_leader = pl.username)
WHERE
proc_leader.proc_leader = 'barikan' ";
//proc_leader.proc_leader = '$username' AND proc_leader.projectNo = '$projectNo'";
$result3= $conn->query($request3);//get the result (ressource)
$Let='B';
while($row3 = $result3->fetch_assoc())
{//extract each record
$prNo=$row3["projectNo"];
$F->setCellValue($Let.'2', ''.$prNo.' '.$row3["process"].'');
++$Let;
//++$Number;
}
So how to put process with the same id in same cell? Thank you
EDIT
Alright, thanx for idea to use group_concat, but when im running sql i dont get all names. Look, here is my proc_leader table: proc_leader
And what i got after running query:
SELECT projectNo, group_concat(process) AS processes, proc_leader
FROM proc_leader
GROUP BY projectNo
Sousername "candy" is missing. Why? Thanks for help