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 " </td>";
echo "<td >";
echo "empty";
echo " </td>";
}
else
{
echo "<td>";
echo $row["WORK_HOUR"];
echo " </td>";
echo "<td>";
echo $row["OT_HOUR"];
echo " </td>";
}
}
please help. i've been doing this for 2 days. @__@
