hisyamjainari 0 Newbie Poster
$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)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.