$pdf->Table('select @N:=@N+1 AS no,
A.department as "DEPARTMENT",
A.no as "ASSET NUMBER" ,
A.total as "ASSET TOTAL PRICE" ,
B.no as "INVENTORY NUMBER" ,
B.total as "INVENTORY TOTAL PRICE"
from
(select h.department , coalesce(count(h.id),0) as no,coalesce(sum(h.price),0) as total
from asset h
where h.department = "'.$department.'"
and year(h.date_accepted) = '.$year.'
GROUP BY year(h.date_accepted)) as A ,
(select coalesce(count(i.id),0) as no,coalesce(sum(i.price),0) as total
from inventory i
where i.department = "'.$department.'"
and year(i.date_accepted) = '.$year.'
GROUP BY year(i.date_accepted)) as B ',$prop);
I'm doing a PDF file that will generate table that read from SQL. The problem is when any row in database is empty the PDF Table will not generate any output even though there are data in other table .
e.g. for year 2009 there are only Assets accepted while there's none for Inventory. When echoing, the table generated is empty even though there's data in Asset table in the database.
I've tried everything from ISNULL, IFNULL, COALESCE to set the value of sum()/count() to 0(which I think the reason why the table generated is empty) but still can't generate the data.
Why did the COALESCE function not functioning and is it because of the use of alias?eg COALESCE(sum(i.price),0)) (as in i.variable)