MySQL COUNT -> PHP Arrays

[devilindisguise]

Hi everyone

 

I have a requirement to create some reports and outputting these to a PDF. I'm using FPDF which seems to be rather user-friendly. I was hoping someone could help with me a problem I have regarding MySQL COUNT and PHP arrays.

 

I have a table named 'faults' and within this a row called 'level'. There are three 'levels' = P1, P2, P3 that could populate this row. I wish to display the output into a PDF so it looks something like this:

 

Total number of faults;

 

P1           P2           P3

 

1              2              3

 

Where I'm falling short is how to perform the COUNT and then grab the data stick it into an array and output it to the PDF. The MySQL statement you see below, if put directly into phpMyAdmin, seems to do the trick, so I'm hoping I'm on the right track. So far I have this:

<----OUTPUT OMITTED---->

$result=mysqli_query($con, 


"SELECT
 COUNT(IF(level='P1',1, NULL)) 'P1', 
 COUNT(IF(level='P2',1, NULL)) 'P2', 
 COUNT(IF(level='P3',1, NULL)) 'P3' 
 FROM faults");


//initialize counter
$i = 0;


//Set maximum rows per page
$max = 25;


while($row = mysqli_fetch_array($result))
{
//If the current row is the last one, create new page and print column title
if ($i == $max)
{
$pdf->AddPage();


//print column titles for the current page
$pdf->SetY($y_axis);
$pdf->SetX(25);
$pdf->Cell(50,6,'Total P1s',1,0,'L',1);
$pdf->Cell(50,6,'Total P2s',1,0,'L',1);
$pdf->Cell(50,6,'Total P3s',1,0,'L',1);


//Go to next row
$y_axis = $y_axis + $row_height;


//Set $i variable to 0 (first row)
$i = 0;
}


//This bit definitely ain't right
$p1 = $row['level'];
$p2 = $row['level'];
$p3 = $row['level'];




$pdf->SetY($y_axis);
$pdf->SetX(25);
$pdf->Cell(50,6,$p1,1,0,'L',1);
$pdf->Cell(50,6,$p2,1,0,'L',1);
$pdf->Cell(50,6,$p3,1,0,'R',1);


//Go to next row
$y_axis = $y_axis + $row_height;
$i = $i + 1;
}


//Send file
$pdf->Output();


?>

Appreciate it must look a mess, but would appreciate someone cleverer than me pointing me in the right direction.

 

Thanks for this and for your help so far!

[Ch0cu3r]

Your query is fine. The problem is here

//This bit definitely ain't right
$p1 = $row['level'];
$p2 = $row['level'];
$p3 = $row['level'];

You need to use P1, P2, or P3 in place of level. Example

$p1 = $row['P1'];
$p2 = $row['P2'];
$p3 = $row['P3'];

[devilindisguise]

Ch0cu3r

 

If you were a fine young blonde I'd offer to take you out for a drink for this, alas, I'll just have to say thanks. That was the ticket.

 

Many thanks

[Barand]

Those COUNTs need to be SUMs

SELECT
 SUM(IF(level='P1',1, NULL)) 'P1', 
 SUM(IF(level='P2',1, NULL)) 'P2', 
 SUM(IF(level='P3',1, NULL)) 'P3' 
 FROM faults

[devilindisguise]

Thanks Barand

Made the amendment, works great.

 

Thank you