MySQL COUNT -> PHP Arrays Pt.2

[devilindisguise]

Hello again

 

Sorry, I come back cap in hand to ask for one last bit of help. After this I should be done never to bother you again.

 

I posted just previously regarding outputting the results of a MySQL COUNT query to a PDF file. Staying on that subject to an extent, I've been wracking my brains on why this conundrum isn't working. As before, I wish to display the information from one table but unlike before there are multiple rows as opposed to multiple columns.

 

I have my table 'faults' and wish to query the top ten, or top most occurring 'site_id' in this table. The following MySLQ query as before works just fine in phpMyAdmin:

 

"SELECT `site_id` AS `site`, COUNT(*) AS `count`

FROM `faults`

GROUP BY `site`

ORDER BY count(*) DESC;"

 

Not much in the DB right now but it yields the following as I expect:

 

Site     Count

 

  1            7

  2            6

  3            6

 

 

I was hoping getting this into a PDF wouldn't be so tricky but as usual I've come unstuck. Essentially what is happening is that it's only displays the bottom row, not all. Here's my code:

 

<?php


require('pdf/fpdf/fpdf.php');
include('core/connect.php');


//Create new pdf file
$pdf=new FPDF();


$pdf->Open();


//Disable automatic page break
$pdf->SetAutoPageBreak(false);


//Add first page
$pdf->AddPage();


//Heading
$pdf->SetFillColor(255,255,255);
$pdf->SetFont('Arial','BU',12);
$pdf->SetY(25);
$pdf->SetX(25);
$pdf->Cell(25,6,'Top Site:',9,0,'L',1);


$result_topten = mysqli_query($con, 


"SELECT `site_id` AS `site`, COUNT(*) AS `count`
    FROM `faults`
    GROUP BY `site`
    ORDER BY count(*) DESC;");
  
while($row = mysqli_fetch_array($result_topten))
{


$site   = $row['site'];
$num_faults   = $row['count'];


}
$pdf->SetFillColor(192,192,192);
$pdf->SetFont('Arial','B',10);
$pdf->SetY(35);
$pdf->SetX(25);
$pdf->Cell(50,6,'Site',1,0,'C',1);
$pdf->Cell(50,6,'Number of faults',1,0,'C',1);


$pdf->SetFillColor(232,232,232);
$pdf->SetFont('Arial','',10);
$pdf->SetY(45);
$pdf->SetX(25);
$pdf->Cell(50,6,$site,1,0,'C',1);
$pdf->Cell(50,6,$num_faults,1,0,'C',1);


$i = $i++;


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

Resulting output in the PDF:

 

Top Site:

 

Site     Count

 

  3            6

 

Appreciate this may elementary and of course appreciate your help.

Thanks again!

 

 

 

 

[CroNiX]

It's because of these lines:

while($row = mysqli_fetch_array($result_topten))
{
  $site  = $row['site'];
  $num_faults  = $row['count'];
}

Think logically about what's going on here. You are rewriting/overwriting $site and $num_faults each time the loop runs. So at the end of the loop execution, $site and $num_faults will be the very last values they encountered of the result set.

 

So you need to add the code that is adding those values to the pdf to be within that while() loop instead of after, so that each one gets added.

[devilindisguise]

CroNIX

 

Thanks for your speedy reply. I think I know where you're coming from. Would it be something along the lines of:

 

<?php


require('pdf/fpdf/fpdf.php');
include('core/connect.php');


//Create new pdf file
$pdf=new FPDF();


$pdf->Open();


//Disable automatic page break
$pdf->SetAutoPageBreak(false);


//Add first page
$pdf->AddPage();


//Heading
$pdf->SetFillColor(255,255,255);
$pdf->SetFont('Arial','BU',12);
$pdf->SetY(25);
$pdf->SetX(25);
$pdf->Cell(25,6,'Top Site:',9,0,'L',1);


$result_topten = mysqli_query($con, 


"SELECT `site_id` AS `site`, COUNT(*) AS `count`
    FROM `faults`
    GROUP BY `site`
    ORDER BY count(*) DESC;");
  
while($row = mysqli_fetch_array($result_topten))
{


$site   = $row['site'];
$num_faults   = $row['count'];


$pdf->SetFillColor(192,192,192);
$pdf->SetFont('Arial','B',10);
$pdf->SetY(35);
$pdf->SetX(25);
$pdf->Cell(50,6,'Site',1,0,'C',1);
$pdf->Cell(50,6,'Number of faults',1,0,'C',1);


$pdf->SetFillColor(232,232,232);
$pdf->SetFont('Arial','',10);
$pdf->SetY(45);
$pdf->SetX(25);
$pdf->Cell(50,6,$site,1,0,'C',1);
$pdf->Cell(50,6,$num_faults,1,0,'C',1);


$i = $i++;


}


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

Thanks

 

[CroNiX]

Something like that. Does it display in the PDF like you want?

 

I'm not sure if you want this part repeating:

$pdf->SetFillColor(192,192,192);
$pdf->SetFont('Arial','B',10);
$pdf->SetY(35);
$pdf->SetX(25);
$pdf->Cell(50,6,'Site',1,0,'C',1);
$pdf->Cell(50,6,'Number of faults',1,0,'C',1);

If not maybe that one should go before the while() loop. It looks like it might be a header containing "Site" and "Number of faults" so probably only want to output it once while the data repeats in the loop below it.

[devilindisguise]

Hi there

 

I'm afraid not. No matter which way I spin it I always only ever get the last row. You are right that block of code should come out of the while loop as it simply provides the header.

 

Looking at my code I haven't initialised the variable $i. Do I perhaps need an If statement in there for good measure that checks $i against another value?

 

Thanks

Edited December 30, 2014 by devilindisguise

[Zane]

You have to update the variable, not reassign it every loop.

 

 

$site .= "The current loop";

This will concatenate the $site variable instead of overwriting it.

 

 

$site += 333

If you are using numbers then this will increment the value.

 

It's all in the manual.

[CroNiX]

I don't see anywhere that you are using $i, so what's it supposed to be doing? But yes it's hard to increment a value ($i++) if it doesn't initially have a value, so what's it supposed to increment? Maybe $i = 0; just before the loop but I still don't know what you're doing with it or if it's even needed.

 

It could be because of the X/Y coordinates where you are inserting the text within the loop. The coordinates never change so it will just write over itself for each entry. Probably the 

$pdf->SetY(35);

 where 35 should be incrementing by some number each time it goes through the loop so each entry will be placed on its own line.

Edited December 31, 2014 by CroNiX

[devilindisguise]

Hello all

 

Thanks for your input and apologies if it was frustrating. I managed to get it working. CroNIX your last post gave me food for thought. Here's the updated and working config:

<----OUTPUT omitted---->

$pdf->SetFillColor(255,255,255);
$pdf->SetFont('Arial','BU',12);
$pdf->SetY(155);
$pdf->SetX(25);
$pdf->Cell(25,6,'Top 5 Sites:',9,0,'L',1);


//Next Set of Results//
$pdf->SetFillColor(192,192,192);
$pdf->SetFont('Arial','B',10);
$pdf->SetY(165);
$pdf->SetX(25);
$pdf->Cell(50,6,'Site',1,0,'C',1);
$pdf->Cell(50,6,'Number of faults',1,0,'C',1);


$y_axis = $y_axis + $row_height;


$result_topten = mysqli_query($con, 


"SELECT site_details.sitename AS `sitename`, COUNT(*) AS `count`
FROM site_details
LEFT JOIN faults ON site_details.site_id = faults.site_id
GROUP BY `sitename`
ORDER BY COUNT(*) DESC
LIMIT 5;");


$y_axis = 171;


$i = 0;


$max = 25;


$row_height = 6;
  
while($row = mysqli_fetch_array($result_topten))
{
if ($i == $max)
{


$pdf->AddPage();


$pdf->SetFillColor(192,192,192);
$pdf->SetFont('Arial','B',10);
$pdf->SetY(165);
$pdf->SetX(25);
$pdf->Cell(50,6,'Site',1,0,'C',1);
$pdf->Cell(50,6,'Number of faults',1,0,'C',1);


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


}


$site   = $row['sitename'];
$num_faults   = $row['count'];


$pdf->SetFillColor(232,232,232);
$pdf->SetFont('Arial','',10);
$pdf->SetY($y_axis);
$pdf->SetX(25);
$pdf->Cell(50,6,$site,1,0,'C',1);
$pdf->Cell(50,6,$num_faults,1,0,'C',1);


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


}


?>

 

Again, many thanks.