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

Question

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. @__@

Answer 1

phew. finally, using @LiangLiang Zheng answer, i could get the final grand total and the Work Hours & OT Hours for each line (row in view). (seriously, i only know basic, simple PHP, and don't really know how to use array, etc. so i only code whatever in my knowledge).

The only thing left that i need to do right now is to display the grand total for EACH COLUMN: Total Work Hour Group A, Total OT Hour Group A,Total Work Hour Group B, Total OT Hour Group B. (anyone can help me on this?)

anyway, here how i got the Total Work Hour & Total OT Hour for each line/row and the final Grand Total for both Work Hours & OT hours (to display at the bottom of the table):

    <?php

    $total_workhours=0;
    $total_ot=0;
    // go through all possible combination of (dept, groups)
    foreach($allDepts as $deptId => $deptName) {

    $dept_workhour=0;
$dept_ot=0;

    echo "<tr><td>$deptName</td>\n";

    foreach($allGroups as $group) 
{
    $workHours = $results[$deptId][$group]['work'];
    printTd($workHours);
    $otHours = $results[$deptId][$group]['ot'];
    printTd($otHours);

    $total_workhours+=$workHours;
    $total_ot+=$ot_hour;

            $dept_workhour +=$workHours;
    $dept_ot +=$ot_hour;

    }

echo "<td> $dept_workhour</td>\n";
echo "<td>$dept_ot</td>\n";
    echo "</tr>\n";
    }
    ?>
    <tr align="center">
    <td>TOTAL</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td ><?php echo $total_workhours; ?></td>
    <td><?php echo $total_ot; ?></td>
    </tr>

    <?php
    function printTd( $value ) {
    $value = $value ? $value:"empty";
    echo "<td>$value</td>\n";
    }

Answer 2

I hope I didn't misunderstand your question. The reason you got some rows missing is that you didn't even get any tup for some cases.

For instance, if I have a simple table t1(key1 varchar(200), key2 varchar(200), val integer), and I wrote a query like select key1, key2, sum(val) from t1 group by key1, key2;

Assume that the values were -

apple week1 4
apple week2 5
pear  week1 3
pear  week1 3
apple week2 3
apple week2 4
apple week1 5

With the query, I would never get something like (pear, week2) => some value.

Similarly, you might not even trigger the conditional check like (!$row["WORK_HOUR"])&&(!$row["OT_HOUR"])

I hope the code below would help.

$results = array();

$allDepts = array();
$allGroups = array();

while($row = mssql_fetch_array($displayResult))
{
    $deptId = $row['DPT_ID'];
    $deptName = $row['DEPARTMENT_NAME'];
    $group = $row['EMP_GROUP'];
    $workHours = $row['WORK_HOUR'];
    $otHours = $row['OT_HOUR'];

    // set up a thorough list of depts & groups
    $allDepts[$deptId] = $deptName;
    $allGroups[$group] = $group;

    // store the values of hours
    $results[$deptId][$group]['work'] = $workHours;
    $results[$deptId][$group]['ot'] = $otHours;
}

// go through all possible combination of (dept, groups)
foreach($allDepts as $deptId => $deptName) {
    echo "<tr><td>$deptName</td>\n";
    foreach($allGroups as $group) {
        $workHours = $results[$deptId][$group]['work'];
        printTd($workHours);
        $otHours = $results[$deptId][$group]['ot'];
        printTd($otHours);
    }
    echo "</tr>\n";
}

function printTd( $value ) {
    $value = $value ? $value:"empty";
    echo "<td>$value</td>\n";
}

Answer 3

$arr = array();
while($r = mysql_fetch_assoc($rec))
{
   $arr[] = $r;
}

if(count($arr) == 0){
echo 0;
}else{
//do something else
}