PHP DISTINCT Function

[FooKelvin]

Hello,

 

Is there any Distinct function for php? 

 

• an employee can have more than 1 form
• an employee have one and only one position in a form
• 1 form have only one and only one Evaluator
• 1 form include more than 1 questions (in this topic have 4 questions for each form)

in this case, the forms, position,score and evaluator repeat 4 times due to there is 4 questions is a form. How to Distinct in to show only one time for forms, position,score and evaluator?

 

 

Output:

$sql = "SELECT EmpID,EmpName,FormName,Scoring,Position,addedBy FROM [Advisory_BoardDB].[dbo].[masterView] WHERE submissionStatus <> 'Draft' ORDER BY EmpName ASC,FormName ASC";    


$stmt = sqlsrv_query($conn, $sql);
$data = array();

while (list($eid, $ename, $fname,$score,$pos,$ab) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    if (!isset($data[$eid])) {
        // initialize array for employee
        $data[$eid] = array('emp' => $ename, 'total' => 0, 'items' => array());
    }
    // accumulate item data
    $data[$eid]['items'][] = array('form' => $fname,'score' => $score,'position'=>$pos,'addby'=>$ab);
    $data[$eid]['total'] += $score;
}
$pcent20 = ceil(count($data)*20/100);
//$pcent20 = ceil(count($data)*$finalPercentage);
//uasort($data, function($a,$b){return $b['total']-$a['total'];});

uasort($data, function($a, $b)
{
    $emp = $b['total'] - $a['total'];
    if($emp === 0)
    {
        return strcmp($a['emp'], $b['emp']);
    }
    return $emp;
});


$data = array_slice($data, 0, $pcent20, true);


//echo '<pre>',print_r($data, true),'</pre>';

//
//  Now output the array
//
echo <<<TABLE
    <table border="1" id="example">
        <thead>
        <tr>
            <th>Employee ID</th>
            <th>Employee Name</th>
            <th>Grand Total</th>
            <th>Form</th>
            <th>Score</th>
            <th>Position</th>
            <th>Evaluator</th>
        </tr>
       <thead>
TABLE;
echo "<tbody>";
foreach ($data as $eid => $edata) {
    // how many items
    $numItems = count($edata['items']);
    echo "<tr>
            <td rowspan='$numItems'>$eid</td>
            <td rowspan='$numItems'>{$edata['emp']}</td>
            <td rowspan='$numItems'>{$edata['total']}</td>";
    foreach ($edata['items'] as $k => $idata) {
        if ($k > 0) {
            echo "<tr class='hover-row'>";
        }
       echo "<td>{$idata['form']}</td><td>{$idata['score']}</td><td>{$idata['position']}</td><td>{$idata['addby']}</td></tr>";
    }
}
echo"</tbody>";
echo "</table>";
?>

Edited November 7, 2015 by FooKelvin

[Barand]

You show two different outputs. Which one do you want? Or do you want a third version and, if so, what?

 

What is your database structure?

Edited November 7, 2015 by Barand

[FooKelvin]

Oops.. Sorry.. i think i accidentally uploaded the second attachment.

 

This one.

 

 

Here is my Database Structure.

 

This is the table view query:

SELECT       dbo.FormTbl.FormID, dbo.FormTbl.FormName, dbo.CategoryTbl.CatName, dbo.QuestionTbl.questions, dbo.FormQuestionTbl.Points, 
                      dbo.FormQuestionTbl.Yes_No, dbo.empDB.EmployeeID AS EmpID, dbo.empDB.Name AS EmpName, dbo.[evaluation[Submit]]].Position, 
                      dbo.[evaluation[Submit]]].Scoring, dbo.FormQuestionTbl.tplID, dbo.grouptbl.mainGroupID, dbo.grouptbl.groupName, dbo.subgrouptbl.subgroupID, 
                      dbo.subgrouptbl.subgroupName, dbo.QuestionTbl.qID, dbo.FormTbl.startDate, dbo.FormTbl.endDate, dbo.grouptbl.id AS gid, dbo.FormTbl.fiscalYear, 
                      dbo.QuestionTbl.sltGroup, dbo.EmployeeDetails.submissionStatus, dbo.FormTbl.quarter, dbo.FormTbl.quarterTo, empDB_1.Name AS addedBy, dbo.empDB.type
FROM         dbo.[evaluation[Submit]]] INNER JOIN
                      dbo.empDB ON dbo.[evaluation[Submit]]].EmpID = dbo.empDB.EmployeeID INNER JOIN
                      dbo.FormTbl ON dbo.[evaluation[Submit]]].FormID = dbo.FormTbl.FormID INNER JOIN
                      dbo.QuestionTbl ON dbo.[evaluation[Submit]]].Question = dbo.QuestionTbl.qID INNER JOIN
                      dbo.FormQuestionTbl ON dbo.QuestionTbl.qID = dbo.FormQuestionTbl.sqID AND dbo.FormTbl.tplID = dbo.FormQuestionTbl.tplID INNER JOIN
                      dbo.CategoryTbl ON dbo.QuestionTbl.catID = dbo.CategoryTbl.CatID INNER JOIN
                      dbo.grouptbl ON dbo.[evaluation[Submit]]].gid = dbo.grouptbl.id INNER JOIN
                      dbo.subgrouptbl ON dbo.grouptbl.mainGroupID = dbo.subgrouptbl.maingroupID AND dbo.[evaluation[Submit]]].subGroup = dbo.subgrouptbl.subgroupID INNER JOIN
                      dbo.EmployeeDetails ON dbo.[evaluation[Submit]]].EmpID = dbo.EmployeeDetails.EmpID AND 
                      dbo.[evaluation[Submit]]].subGroup = dbo.EmployeeDetails.subGroupID AND dbo.[evaluation[Submit]]].FormID = dbo.EmployeeDetails.formID INNER JOIN
                      dbo.empDB AS empDB_1 ON dbo.FormTbl.addedBy = empDB_1.EmployeeID
WHERE     (dbo.FormQuestionTbl.Yes_No = 'Yes')
ORDER BY dbo.subgrouptbl.subgroupName

Thank You.

 

[benanamen]

Your DB structure is messed up. Why are you duplicating the empDB table?

Edited November 7, 2015 by benanamen

[Barand]

In that output you want the scores, which can all be different. If you only show the form once, which of those different scores would you show?

 

Also, in that output, it shows repeating values for position and evaluator. As your opening post says you don't want these repeating then the question still remains - what do you want the output to look like?

[FooKelvin]

Your DB structure is messed up. Why are you duplicating the empDB table?

I repeating the empDB is because i would like to get the name of the avaluator aka, addedby column.

[FooKelvin]

In that output you want the scores, which can all be different. If you only show the form once, which of those different scores would you show?

 

Also, in that output, it shows repeating values for position and evaluator. As your opening post says you don't want these repeating then the question still remains - what do you want the output to look like?

 

The show the form once and sum up the form score for each.

 

The exact output:

[Barand]

I repeating the empDB is because i would like to get the name of the avaluator aka, addedby column.

You would have a single Emp table but connect to it twice in the same query giving each instance a different table alias.

 

 

The show the form once and sum up the form score for each.

 

The exact output:

Distinct_sumScore.JPG

In which case you would group by form and select SUM(scoring)

[FooKelvin]

You would have a single Emp table but connect to it twice in the same query giving each instance a different table alias.

 

 

In which case you would group by form and select SUM(scoring)

 

I'm not planning to have any cases. mean when click to generate the report, it will generate exactly what i had attach. 

 

thank you.