PHP MSSQL : How to display output when query return no row

Posted by vamps on Stack Overflow See other posts from Stack Overflow or by vamps
Published on 2012-06-21T06:58:12Z Indexed on 2012/06/25 3:16 UTC
Read the original article Hit count: 178

Filed under:

php

|

sql-server

|

query

i have a problem with my PHP-MSSQL query. i have a join table that need to give a result something be like this:

    Department        Group A              Group B           Total A+B
                 WORKHOUR A OTHOUR A    WORKHOUR B OTHOUR B      WORKHOUR  OTHOUR
    HR             10         15     25   0               35       15
    IT              5          5                               5        5
    Admin                                    12   12              12       12

the query will count how many employee as per given date (admin will enter data and once submitted, the query will give the above result).

The problem is, the final output is a mess when there's no row to be displayed. the column is shifted to the right.

i.e: only Group A in IT only Group B in Admin

Department        Group A              Group B           Total A+B
             WORKHOUR A OTHOUR A WORKHOUR B OTHOUR B      WORKHOUR  OTHOUR
HR      10       15       25     0               35      15
IT       5        5        5         5
Admin   12       12       12        12

my question is, how to prevent this to happen? i've tried everything with While.... if else.. but the result is still the same. how to display output "0" if no rows to return? echo "0";

this is my QUERY:

    select DD.DPT_ID,DPT.DEPARTMENT_NAME,TU.EMP_GROUP, sum(DD.WORK_HOUR) AS  WORK_HOUR,      
    sum(DD.OT_HOUR) AS OT_HOUR
    FROM DEPARTMENT_DETAIL DD
    left join DEPARTMENT DPT
    ON (DD.DEPT_ID=DPT.DEPT_ID)
    LEFT JOIN TBL_USERS TU
    ON (TU.EMP_ID=DD.EMP_ID) 
    WHERE DD_DATE>='2012-01-01'
    AND DD_DATE<='2012-01-31'
    AND TU.EMP_GROUP!=2
    GROUP BY DD.DEPT_ID, DPT.DEPARTMENT_NAME,TU.EMP_GROUP
    ORDER BY DPT.DEPARTMENT_NAME

this is one of the logic that i've used, but doesn't return the result that i want::

    while($row = mssql_fetch_array($displayResult))
{


if ((!$row["WORK_HOUR"])&&(!$row["OT_HOUR"]))

{
echo "<td >";
echo "empty";
echo "&nbsp;</td>";

echo "<td >";
echo "empty";
echo "&nbsp;</td>";

}
else 

{
echo "<td>";
echo $row["WORK_HOUR"];
echo "&nbsp;</td>";

echo "<td>";
echo $row["OT_HOUR"];
echo "&nbsp;</td>";

}



}

please help. i've been doing this for 2 days. @__@

Developer IT