douzhun8615
2015-07-24 06:53
浏览 60

SQL中PIVOT的PHP脚本

I have a View Emp_PRJ_Art in my SQL Server as below.

UsedArticleNo | EmpName | Project | EmpRol  
PART0001    |    Tintin | PRJ1    |   PL  
PART0002    |   Haddok  | PRJ2    |   TL  
PART0003    |   Poppey  | PRJ3    |   GET  
PART0004    |   Archie  | PRJ4    |   PM  
PART0005    |   Tintin  | PRJ1    |   PL  
PART0006    |   Archie  | PRJ4    |   PM  
PART0007    |   Tintin  | PRJ3    |   PL  
PART0008    |   Haddok  | PRJ3    |   TL  
PART0009    |   Poppey  | PRJ1    |   GET  

I want to get a result like this. The numbers under PRJ columns are total number of Article used by the employee for that particular Project.

EmpName |PRJ1 | PRJ2 |  PRJ3 | PRJ4 |  EmpRol  
Archie  | 0   |  0   |   0   |  2   |  PM  
Haddok  | 0   |  1   |   1   |  0   |  TL  
Poppey  | 1   |  0   |   1   |  0   |  GET  
Tintin  | 2   |  0   |   1   |  0   |  PL  

I used the SQL query like this and getting the result as required.

SELECT *   
FROM ( SELECT EmpName, UsedArticleNo, Project  
        FROM Emp_PRJ_Art)  
PIVOT(COUNT(UsedArticleNo) FOR (Project) IN ('PRJ1','PRJ2','PRJ3',’PRJ4’))  
ORDER BY EmpName;

Now my problem is, I am not able to use PHP to get this query run and finally display the same table as webpage, as I am getting in SQL Developer Console. Below is the PHP code not working.

    <?php

$backcol="bgcolor=#9fffa1"; // Light Green
$colgreen1="bgcolor=#5EE060"; // Light Green

$maxitems=1000; // Maximum items found to display

$s=" <small> ";

$user='odbc';
$pass='abcd';
$sid ='server1.myComp.com';

$adr = "http://server1/WebEditor";

$conn = oci_connect($user, $pass, $sid);
if (!$conn) {
    echo "Unable to connect: " .  var_dump(OCIError()  );
    die();
}

}

$cmd="SELECT * 
FROM ( SELECT EmpName, Used ArticleNo, Project  FROM Emp_PRJ_Art)
PIVOT(COUNT(ArticleNo) FOR (Project) IN ('PRJ1','PRJ2','PRJ3','PRJ4'))
ORDER BY Emp Name";
$stid = oci_parse($conn,$cmd); oci_execute($stid);
echo     "<tr>
            <th $backcol> $s Employee Name
            <th $backcol> $s Project-1
            <th $backcol> $s Project-2
            <th $backcol> $s Project-3
            <th $backcol> $s Project-4
            <th $backcol> $s EmpRol
             ";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {

    echo "<tr>
";
    echo "<td> $s " .$row['EmpName']
        ."<td> $s " .$row['PRJ1']
        ."<td> $s " .$row['PRJ2']
        ."<td> $s " .$row['PRJ3']
        ."<td> $s " .$row['PRJ4']
        ."<td> $s " .$row['EmpRol']
        ;
} 
echo "</table>
";
echo "<br><font color='blue'>This is a test</font>";
exit;
?>

The EmpName has 700 entries and Project has 70 entries. UsedArticle can be more than 10k.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanhong1985 2015-07-24 08:48
    已采纳

    There are numerous errors in your code that stop it working correctly. I've modified it as follows and tested it successfully against my setup (Oracle 11gR2, PHP 5.6.x). Most important is aliasing the IN values so they aren't referenced with surrounding single quotes '.

    <?php
    $backcol = "bgcolor=#9fffa1"; // Light Green    
    //$colgreen1 = "bgcolor=#5EE060"; // Light Green
    
    //$maxitems = 1000; // Maximum items found to display
    
    $s=" <small> ";
    
    $user = 'odbc';
    $pass = 'abcd';
    $sid = 'server1.myComp.com';
    
    //$adr = "http://server1/WebEditor";
    
    $conn = oci_connect($user, $pass, $sid);
    if (!$conn) {
        echo "Unable to connect: " .  var_dump(OCIError());
        die();
    }
    
    //}
    
    $cmd = "SELECT *
    FROM ( SELECT EmpName, UsedArticleNo, Project, EmpRol  FROM Emp_PRJ_Art)
    PIVOT(COUNT(UsedArticleNo) FOR (Project) IN ('PRJ1' as prj1, 'PRJ2' as prj2, 'PRJ3' as prj3, 'PRJ4' as prj4))
    ORDER BY EmpName";
    
    $stid = oci_parse($conn,$cmd);
    oci_execute($stid);
    
    echo     "<table>
              <tr>
                <th $backcol> $s Employee Name </th>
                <th $backcol> $s Project-1 </th>
                <th $backcol> $s Project-2 </th>
                <th $backcol> $s Project-3 </th>
                <th $backcol> $s Project-4 </th>
                <th $backcol> $s EmpRol </th>
              </tr>";
    while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    
        echo "<tr>
    ";
        echo "<td> $s " . $row['EN'] . "</td>"
            ."<td> $s " . $row['PRJ1'] . "</td>"
            ."<td> $s " . $row['PRJ2'] . "</td>"
            ."<td> $s " . $row['PRJ3'] . "</td>"
            ."<td> $s " . $row['PRJ4'] . "</td>"
            ."<td> $s " . $row['ER'] . "</td>"
            ."</tr>"
        ;
    }
    echo "</table>
    ";
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题