asked on

how to store result of mysql query in 2D array and want to display that retrieved data in one html table using tcpdf ???

I am working on php mysql.I have used tcpdf to generate pdf.

I have developed code for displaying tables HAVING details of number of employees category wise.
there are four categories
DRDS (under drds again sub categories are there like Sc. B, Sc. D etc)
DRTC (under drtc again sub categories are there like TO. B , TO. C etc)
ADMIN (under Admin again sub categories are there like Sc. B, Sc. D etc)
ALLIED (under allied again sub categories like halwai, vehicle operator etc)

i am using two while loops

first while loop is for category (each tim e loop will run for one category)

second while loop is for sub categories

in this manner when a outerloop is run for first time it will display one table.(also calculating count of employees in that category)
loop will run for 4 times as there are 4 categories and in this way four tables will be displayed.

at the end total number of employees of all categories is displayed

In my tcpdf i want to display all these data into one table only instead of displaying separate tables.

currently it is displaying four tables
I want that query result from all four loops should be stored in one array so that it can be displayed in one table

generated pdf is attached

tcpdf php code is here

<?php
ob_start();
extract($_GET);
include("../../config.php");
require_once('tcpdf_include.php');
$pdf = new TCPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

$pdf->SetPrintHeader(false);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
if(@file_exists(dirname(__FILE__).'/lang/eng.php')) 
{
    require_once(dirname(__FILE__).'/lang/eng.php');
    $pdf->setLanguageArray($l);
}
		
$pdf->SetFont('TIMES', '', 15);
$html = '';

$pdf->AddPage();
$html = '<h2 style="text-align:center;">Man Power </h2>';
$pdf->writeHTML($html, true, false, true, false, '');

$html= '<style>'.file_get_contents(_BASE_PATH.'held.css').'</style>';
$pdf->writeHTML($html, true, false, true, false, '');

$pdf->SetFont('TIMES', '', 12);
 include_once 'config.php';
           $overall_tot=0;
          $sel  = "select * from employeecategorymaster" ;
          $result  = mysql_query($sel);
              while($fetch1=mysql_fetch_array($result))
              {
                  $category = $fetch1['CategoryId'];
                  
                 $sql_query = "SELECT COUNT(*) AS held , d.DesignationDescription from employeemaster e LEFT OUTER JOIN  designationmaster d ON 
                 d.DesignationId= e.Designation WHERE d.Category = ".$category." GROUP BY d.DesignationDescription ORDER BY d.Sno";  
                   
                   
                    $row=mysql_query($sql_query);
                    $count= mysql_num_rows($row);
               if($count>0)
                {
                   // echo 'returning something !'; 
                   // echo $count."  no of rows";
                }
             else {echo 'query not working';} 
       
          $cnt=1; 
        
       $html = '
                 
               <table border="1" style="width:30% ; align:center;"><tr><td colspan="3" style="text-align:center;"><b>'.$fetch1['CategoryDescription'] .'</b></td></tr>
                  <tr >
                    <b><th width="15%">S.No</th></b>
                    <b><th width="60%">RANK</th> </b>
                    <b><th width=" 25%">HELD</th></b>
                  </tr>
                 ' ;
    
               $tot= 0;
             
              while ($fetch = mysql_fetch_array($row)) 
               
                 {
                    
                 $html.= '<tr nobr="true">
                           <td>'.$cnt++.'</td> 
                           <td>'.$fetch['DesignationDescription'].'</td> 
                           <td>'.$fetch['held'].'</td> 
                         </tr>';
                  $total=$cnt-1;
                  $tot= $tot + $fetch['held'];
                 
                 }
               
                $overall_tot = $overall_tot + $tot;
               
              $html.= '               
                          <tr>
                           <td colspan="2" style="text-align: left;">Total</td>
                          
<td colspan="2">'. $tot .'</td>
                          </tr>
                        </table>
                         ';
               
                 $pdf->writeHTML($html, true, false, true, false, '');
               
                  
              }
  
      $html =  '  <table border="1" style="width:50%;">
                      <tr>
                          <td><b>Total(overall)</b></td>
                          <td>'. $overall_tot .'</td>
                      </tr>
                  </table> ';
      
 $pdf->writeHTML($html, true, false, true, false, '');
$pdf->lastPage();
ob_end_clean(); // helpful when getting error output cannot be send
$pdf->Output('example_006.pdf', 'I');


//============================================================+
// END OF FILE
//============================================================+

Open in new window

pdf.pdf
desired-output.PNG

NerdsOfTech

What you are asking for involves multiple fixes. Consider creating an Expert Exchange 'gig' for this.

First, let's nest tables to create the multi-column output you want and fix the TH from breaking (due to misplaced <b> tags, we will do bold in the CSS instead later, see CSS notes after this code):

<?php
ob_start();
extract($_GET);
include("../../config.php");
require_once('tcpdf_include.php');
$pdf = new TCPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

$pdf->SetPrintHeader(false);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
if(@file_exists(dirname(__FILE__).'/lang/eng.php')) 
{
    require_once(dirname(__FILE__).'/lang/eng.php');
    $pdf->setLanguageArray($l);
}
		
$pdf->SetFont('TIMES', '', 15);
$html = '';

$pdf->AddPage();
$html = '<h2 style="text-align:center;">Defence Laboratory, Jodhpur</h2>';
$pdf->writeHTML($html, true, false, true, false, '');

$html= '<style>'.file_get_contents(_BASE_PATH.'held.css').'</style>';
$pdf->writeHTML($html, true, false, true, false, '');

$pdf->SetFont('TIMES', '', 12);
include_once 'config.php';
$overall_tot=0;
$sel  = "select * from employeecategorymaster" ;
$result  = mysql_query($sel);
$i=0;

$html='<table border="0"><tr>';
$pdf->writeHTML($html, true, false, true, false, ''); // start multicolumn
$html ='';
while($fetch1=mysql_fetch_array($result))
{
	$pdf->writeHTML('<td>', true, false, true, false, '');	// create new column
	$category = $fetch1['CategoryId'];

	$sql_query = "SELECT COUNT(*) AS held , d.DesignationDescription from employeemaster e LEFT OUTER JOIN  designationmaster d ON 
	d.DesignationId= e.Designation WHERE d.Category = ".$category." GROUP BY d.DesignationDescription ORDER BY d.Sno";  


	$row=mysql_query($sql_query);
	$count= mysql_num_rows($row);
	if($count>0)
	{
		// echo 'returning something !'; 
		// echo $count."  no of rows";
	}
	else {echo 'query not working';} 

	$cnt=1; 
	
	$html = '
	<table border="1" style="width:30% ; align:center;"><tr><td colspan="3" style="text-align:center;"><b>'.$fetch1['CategoryDescription'] .'</b></td></tr>
	<tr >
	<th width="15%">S.No</th>
	<th width="60%">RANK</th> 
	<th width=" 25%">HELD</th>
	</tr>
	' ;

	$tot= 0;
	
	while ($fetch = mysql_fetch_array($row)) 

	{

		$html.= '<tr nobr="true">
			   <td>'.$cnt++.'</td> 
			   <td>'.$fetch['DesignationDescription'].'</td> 
			   <td>'.$fetch['held'].'</td> 
			 </tr>';
		$total=$cnt-1;
		$tot= $tot + $fetch['held'];

	}

	$overall_tot = $overall_tot + $tot;

	$html.= '               
		  <tr>
		   <td colspan="2" style="text-align: left;">Total</td>
		  
	<td colspan="2">'. $tot .'</td>
		  </tr>
		</table>
		 ';

	$pdf->writeHTML($html, true, false, true, false, '');
	$pdf->writeHTML('</td>', true, false, true, false, '');	// end column

}
$html='</tr><table>'; // end multicolumn
$pdf->writeHTML($html, true, false, true, false, '');
$html ='';

$html =  '  <table border="1" style="width:50%;">
  <tr>
	  <td><b>Total(overall)</b></td>
	  <td>'. $overall_tot .'</td>
  </tr>
</table> ';
  
$pdf->writeHTML($html, true, false, true, false, '');
$pdf->lastPage();
ob_end_clean(); // helpful when getting error output cannot be send
$pdf->Output('example_006.pdf', 'I');


//============================================================+
// END OF FILE
//============================================================+

Open in new window

After you alter the output to the desired level, you'll want to then add CSS identifiers (ID's or Classes) to the html code.

Subsequently, edit the CSS file to reflect the colors, etc. you desire to the identifiers you created.

A few examples:

For the alternating row coloration you might want to use ODD / EVEN CSS rules e.g.

tr:nth-child(even) {background: #EEE}
tr:nth-child(odd) {background: #FFF}

Open in new window

And, for the header background color, font color, and boldness:

th {background: #066; color: #FFF; font-weight: bold;}

Open in new window

v s

ASKER

OUTPUT is not in one table.

It is displaying in four different tables as mine.

v s

ASKER

how these four tables can be merged in one table ???

NerdsOfTech

You might have to output the tables into <div>'s and then "float" the <div>'s

v s

ASKER

i have already tried this. but float property is not working in tcpdf code

v s

ASKER

i want to collect all retreived data in an array and want to use that array to display result in one html table

NerdsOfTech

I see that tcpdf requires a separate function for multi-column output:

writeHTMLCell($w, $h, $x, $y, $html='', $border=0, $ln=0, $fill=0, $reseth=true, $align='', $autopadding=true)

Open in new window

https://tcpdf.org/examples/example_007/

v s

ASKER

but how to use this to get desired output

ASKER CERTIFIED SOLUTION

NerdsOfTech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

v s

ASKER

thank you ... its easier to take all tables inside td rather than using 2d array to store results.

After some formatting your code i got the ouput (image attached).

if you will se this image , it is shown that total of all categories is not in same line.. as depends on the number of fields in table.

what can we do to make this proper aligned...

thank you
PDFOUTPUT.PNG

NerdsOfTech

Thank you for testing that solution. Glad you were able to change a few things to correct the output.

The alignment question would be considered another question in this forum.

Here are two possible solutions:

1.) the totals could be stored opposed to outputted initially (into say a totals array), then outputted last in their own single-row table (8 columns); somewhat like the overall total single-line table. (LOGIC outside of LOOP)

2.) if you want the total columns to line up, however, you would first find the MAXIMUM rows the data would produce in any given category, then blank rows or a filler cell (rowspan) could be added to fill in gaps on shorter catagories, based on this MAXIMUM row count. (LOGIC inside of LOOP)

Probably the latter is more visually appealing.

Also, can you paste the final code that worked here?

As my comment #42573453 was the basis of this solution, please choose it as "best solution" and provide a score.

P.S. Thank you for the follow and I would like to participate in your next question if possible. After closing this question, please paste the link of your new question here.

v s

ASKER

@NerdsOfTech

Sure i can paste the final code of my output

<?php
ob_start();
extract($_GET);
include("../../config.php");
require_once('tcpdf_include.php');
$pdf = new TCPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

$pdf->SetPrintHeader(false);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
if(@file_exists(dirname(__FILE__).'/lang/eng.php')) 
{
    require_once(dirname(__FILE__).'/lang/eng.php');
    $pdf->setLanguageArray($l);
}
		
$pdf->SetFont('TIMES', '', 15);
$html = '';

$pdf->AddPage();
$html = '<h2 style="text-align:center;">Defence Laboratory, Jodhpur</h2>';
$pdf->writeHTML($html, true, false, true, false, '');

$html= '<style>'.file_get_contents(_BASE_PATH.'held.css').'</style>';
$pdf->writeHTML($html, true, false, true, false, '');

$pdf->SetFont('TIMES', '', 12);
include_once 'config.php';
$overall_tot=0;
$sel  = "select * from employeecategorymaster" ;
$result  = mysql_query($sel);
$i=0;

$html =''; // reset html
$html ='<div style="margin-top:70px;"><table border="0" cellspacing="0"><tr>'; // start multicolumn
while($fetch1=mysql_fetch_array($result))
{
	$html .='<td>';	// create new column
	$category = $fetch1['CategoryId'];

	$sql_query = "SELECT COUNT(*) AS held , d.DesignationDescription from employeemaster e LEFT OUTER JOIN  designationmaster d ON 
	d.DesignationId= e.Designation WHERE d.Category = ".$category." GROUP BY d.DesignationDescription ORDER BY d.Sno";  


	$row = mysql_query($sql_query);
	$count = mysql_num_rows($row);
	if($count>0)
	{
		// echo 'returning something !'; 
		// echo $count."  no of rows";
	}
	else {echo 'query not working';} 

	$cnt = 1; 
	
	$html .= '
	<table border="1"><tr><td colspan="3" style="text-align:center;"><b>'.$fetch1['CategoryDescription'] .'</b></td></tr>
	<tr >
	<th width="30%" style="text-align:center;">S.No</th>
	<th width="50%" style="text-align:center;">RANK</th> 
	<th width=" 20%" style="text-align:center;">HELD</th>
	</tr>
	' ; //add column

	$tot = 0;
	
	while ($fetch = mysql_fetch_array($row)) 

	{

		$html .= '<tr nobr="true">
			   <td style="text-align:center;">'.$cnt++.'</td> 
			   <td style="text-align:center;">'.$fetch['DesignationDescription'].'</td> 
			   <td style="text-align:center;">'.$fetch['held'].'</td> 
			 </tr>';
		$total=$cnt-1;
		$tot= $tot + $fetch['held'];

	}

	$overall_tot = $overall_tot + $tot;
          
	$html .= '               
		  <tr>
		   <td colspan="2" style="text-align: left;">Total</td>
		  
	<td colspan="2" style="text-align:center;">'.$tot.'</td>
		  </tr>
		</table>
		 ';
	$html .='</td>';	// end column

}


$html .='</tr>'; // end 1st row containing tables
$pdf->writeHTML($html, true, false, true, false, ''); 


$html = '</table></div>' ;

$pdf->writeHTML($html, true, false, true, false, ''); 



$html =  '  <table border="1" style="width:75%;">
  <tr>
	  <td><b>Total(overall)</b></td>
	  <td>'. $overall_tot .'</td>
  </tr>
</table> '; // prepare overall total
  
$pdf->writeHTML($html, true, false, true, false, ''); 
$pdf->lastPage();
ob_end_clean(); // helpful when getting error output cannot be send
$pdf->Output('example_006.pdf', 'I');


//============================================================+
// END OF FILE
//============================================================+

Open in new window

I have considered for second number solution for proper alignment of total's row.
got the logic of that solution..

can you please write the code for that ???

thank you in advance :)

Definitely i will be seekin for help from you for my next question

NerdsOfTech

Are you saying that the word total isn't aligned in the last row?

Regardless if this is the case or not, remove colspan="2" from line 88 from your code (as the colspans should equal 3, colspan 2 from the first column, 1 from the last column, in this row):

line 84-91 with line 88 change:

	$html .= '               
		  <tr>
		   <td colspan="2" style="text-align: left;">Total</td>
		  
	<td style="text-align:center;">'.$tot.'</td>
		  </tr>
		</table>
		 ';

Open in new window

NerdsOfTech

If you wanted all four tables to have a common final total row that spans horizontally this would need to be presented as a new question in EE.

I would store the data and output the data if this was the case, into one large table (where you would have more alignment control).

NerdsOfTech

Also, besides removing the colspan=2 from the second column, remove text-align from the first column:

lines 84-91:

	$html .= '               
		  <tr>
		   <td colspan="2">Total</td>
		  
	<td style="text-align:center;">'.$tot.'</td>
		  </tr>
		</table>
		 ';

Open in new window

v s

ASKER

@NerdsOfTech

I have marked your comment as a best solution.

Yes i want to represent total of all categories in a single row .
i will ask that in my next question. thank you :)

v s

ASKER

https://www.experts-exchange.com/pendingQuestions/52446/How-could-I-align-Total-column-of-my-all-tables-different-tables-in-a-single-common-row-for-better-representation.html

this is the link to my new question..
can you please help me ??

v s

ASKER

@NerdsOfTech have you gone through my new question ?