Jump to content

PHP DISTINCT Function


FooKelvin

Recommended Posts

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:

post-179514-0-53223500-1446870047_thumb.jpg

$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>";
?>

post-179514-0-19183600-1446867152_thumb.jpg

Edited by FooKelvin
Link to comment
Share on other sites

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

 

This one.

post-179514-0-31891200-1446915484_thumb.jpg

 

 

Here is my Database Structure.

post-179514-0-68703900-1446916615_thumb.jpg

 

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.

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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:

post-179514-0-62034100-1446949580_thumb.jpg

Link to comment
Share on other sites

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:

attachicon.gifDistinct_sumScore.JPG

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.