如何在PHPExcel中的一个单元格中插入具有相同ID的值

Question

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

result

Sousername "candy" is missing. Why? Thanks for help

Answer 1

If I understand correctly what you want, then you could change your query of the project table to :

SELECT projectNo, group_concat(process) AS processes, proc_leader
FROM project
GROUP BY projectNo;

depending on version of MySQL that may need to be

SELECT projectNo, group_concat(process) AS processes, ANY_VALUE(proc_leader)
FROM project
GROUP BY projectNo;

the group_concat function will group all processes & comma separate them like you show in your required excel sheet - the column can be accessed with the name "processes". http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

Later versions of MySQL do not allow non-aggregated columns to be selected - unless you specifically tell it you do not mind which row the value comes from, for example by using the ANY_VALUE function. http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

If you may have different proc_leader for a particular projectNo - then the query could be :

SELECT projectNo, group_concat(process) AS processes, proc_leader
FROM project
GROUP BY projectNo, proc_leader;

Another way would just be to search your excel sheet for the row with matching username & then search for the correct column & append to the matching cell.