1,105,332 Community Members

Horizontal Display from Mysql

Member Avatar
rpv_sen
Posting Whiz in Training
260 posts since Mar 2011
Reputation Points: 14 [?]
Q&As Helped to Solve: 21 [?]
Skill Endorsements: 1 [?]
 
0
 

Hi

I am trying to display the records Horizontally from my 3 different table

1. place table - n number of place
2. Item name
3. Item in different place

i want to display the above report as in the attachment.

please help me to resolve the issue

<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <thead>
    <tr>
      <th width="56" height="30" align="center">S.No</th>
      <th width="445">Description</th>
      <th width="102">Units</th>
      <?php $qsite = "select * from sitename order by sitename"; $rsite = mysql_query($qsite,$conn); while($msite = mysql_fetch_array($rsite)){ $assite = $msite['sitename'];?>
      <th width="91"><?php echo $msite['sitename']; ?></th>
      <?php } ?>
      <th width="244">Total</th>
    </tr>
    </thead>
    <tbody>
    <?php 
			$qsite = "select *,count(sitename) from sitename"; 
			$rsite = mysql_query($qsite,$conn); 
			while($msite = mysql_fetch_array($rsite))
			
			{ 
			
				$assite = $msite['sitename'];
				$asscount = $msite['count(sitename)'];
			$qasset = "select * from asset_master_subcat";
			$i=1;
		  //echo $qasset;	
		  $rasset = mysql_query($qasset,$conn);
		  while($masset = mysql_fetch_array($rasset))
		   {
			  $asscode = $masset['ass_sub_cat_code'];
			  
			  $asite = "select *,sum(no_of_qty),sum(transfer_qty) from assetinsite where ass_sub_cat_code='$asscode' and sitename='$assite'";
			  //echo $asite;
			  $asiter = mysql_query($asite,$conn);
			  
	?>
    <tr class="light">
      <td height="30" align="center" valign="middle"><?php echo $i;?></td>
      <td width="445"><?php echo $masset['ass_sub_cat_name'];  ?></td>
      <td width="30"><?php echo $masset['unit']; ?></td>
      <td>
	  <?php while($asitem = mysql_fetch_array($asiter))
			  	{ 
					
					$qtyno = $asitem['sum(no_of_qty)'];
			  		$qtytrans = $asitem['sum(transfer_qty)'];
			  
			  $total_qty = $qtyno + $qtytrans;?><?php echo $total_qty."\t&nbsp;&nbsp;&nbsp;&nbsp;"; } ?></td>
       <td></td>
       <td></td>
       <td></td>
       <td></td>
       <td></td>
       <td></td>
       <td></td>
       <td></td>
       <td></td>       
      <?php $asstotalq = "select sum(no_of_qty),sum(transfer_qty) from assetinsite where ass_sub_cat_code='$asscode'";
	  		//echo $asstotalq;
			$asstotalr = mysql_query($asstotalq,$conn);
			while($asstotalm = mysql_fetch_array($asstotalr))
			{
				$totalqty = $asstotalm['sum(no_of_qty)'];
				$totaltrans = $asstotalm['sum(transfer_qty)'];
	  ?>
      <td><?php echo $total = $totalqty+$totaltrans;}?></td>
      </tr><?php $i++;} }?>
    </tbody>
  </table>
Attachments report.jpg 58.06KB
Member Avatar
HITMANOF44th
Posting Whiz in Training
284 posts since Apr 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 34 [?]
Skill Endorsements: 0 [?]
 
0
 

not sure if this is the way you want to try it but give it a look

<?php

//===========================================================
// grab all data
//===========================================================
 $qsite = "select * from sitename order by sitename"; $rsite = mysql_query($qsite,$conn); while($msite = mysql_fetch_array($rsite)){ $assite = $msite['sitename'];
     
     
     
			$qsite = "select *,count(sitename) from sitename"; 
			$rsite = mysql_query($qsite,$conn); 
			while($msite = mysql_fetch_array($rsite))
			
			{ 
			
				$assite[] = $msite['sitename'];
				$asscount[] = $msite['count(sitename)'];
			$qasset = "select * from asset_master_subcat";

		  //echo $qasset;	
		  $rasset = mysql_query($qasset,$conn);
		  while($masset = mysql_fetch_array($rasset))
		   {
			  $asscode[] = $masset['ass_sub_cat_code'];
			  
			  $asite = "select *,sum(no_of_qty),sum(transfer_qty) from assetinsite where ass_sub_cat_code='$asscode' and sitename='$assite'";
			  //echo $asite;
			  $asiter = mysql_query($asite,$conn);
			  
while($asitem = mysql_fetch_array($asiter))
			  	{ 
					
					$qtyno[] = $asitem['sum(no_of_qty)'];
			  		$qtytrans[] = $asitem['sum(transfer_qty)'];
			  
			  $total_qty[] = $qtyno + $qtytrans;
			  
$asstotalq = "select sum(no_of_qty),sum(transfer_qty) from assetinsite where ass_sub_cat_code='$asscode'";
	  		//echo $asstotalq;
			$asstotalr = mysql_query($asstotalq,$conn);
			while($asstotalm = mysql_fetch_array($asstotalr))
			{
				$totalqty[] = $asstotalm['sum(no_of_qty)'];
				$totaltrans[] = $asstotalm['sum(transfer_qty)'];
				}
				
				
$countstuff = count($assite);	



//===================================
// make table and header
//==================================		
echo "<table>
<tr>
	<td>header 1</td>
	<td>header 2</td>
	<td>header 3</td>
	<td>header 4</td>
</tr>";

//===================================
// make all data appear in table
//==================================
				for ($counter = 0 ; $counter <= $countstuff; $counter += 1) {
				
echo "<tr>
	<td>$counter</td>
	<td>".$totalqty[$counter]."</td>
	<td>".$totaltrans[$counter]."</td>
	<td>".$asscode[$counter]."</td>
</tr>";
					
					}
					
					
					echo "</table>";

?>
Member Avatar
rpv_sen
Posting Whiz in Training
260 posts since Mar 2011
Reputation Points: 14 [?]
Q&As Helped to Solve: 21 [?]
Skill Endorsements: 1 [?]
 
0
 

Hi Friend

Thanks for your support.

I am getting an Error message as below

Fatal error: [] operator not supported for strings in C:\xampp\htdocs\indent\admin\report_test.php on line 22

Member Avatar
HITMANOF44th
Posting Whiz in Training
284 posts since Apr 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 34 [?]
Skill Endorsements: 0 [?]
 
0
 

what is line 20 thru 23 in your script or is that the whole thing above ?

Member Avatar
rpv_sen
Posting Whiz in Training
260 posts since Mar 2011
Reputation Points: 14 [?]
Q&As Helped to Solve: 21 [?]
Skill Endorsements: 1 [?]
 
0
 

Hi Friend

Thanks for your support,

At last i got the result what i expect.

Below is the final output code

<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <thead>
    <tr>
      <th width="56" height="30" align="center">S.No</th>
      <th width="445">Description</th>
      <th width="102">Units</th>
      <?php 
	  		$qsite = "select *,count(sitename) from sitename"; 
			$rsite = mysql_query($qsite,$conn); 
			while($msite = mysql_fetch_array($rsite))
			{ 
				$acount = $msite['count(sitename)'];
				$asiteid = $msite['id'];
			
			
			for($i=1;$i<=$acount;$i++)
			{
				//echo $i;
			
			$qsi = "select * from sitename where id='$i'";
			$qsir = mysql_query($qsi,$conn);
			while($qsim=mysql_fetch_array($qsir))
				{
					$assite = $qsim['sitename'];
	  ?>
      <th width="91"><?php echo $qsim[sitename]; ?></th>
      <?php } }?>
      <th width="244">Total</th>
    </tr>
    </thead>
    <tbody>
    <?php 
	
			$ass_ma_cat = "select * from asset_master_cat";
			$ass_mat_r = mysql_query($ass_ma_cat,$conn);
			while($ass_mat_m = mysql_fetch_array($ass_mat_r))
			{
				$ass_master_code =  $ass_mat_m['ass_cat_code'];
				
	?>
    <tr class="light">
    <td height="30" colspan="<?php echo $i+3;?>"><b><?php echo $ass_mat_m['ass_cat_name'];?></b></td>
    </tr>
	<?php 
		$ass_code = "select * from asset_master_subcat where ass_cat_code='$ass_master_code' order by ass_sub_cat_code";
		$ass_coder = mysql_query($ass_code,$conn);
		$j = 1;
		while($ass_codem = mysql_fetch_array($ass_coder))
		{	
			$ass_co = $ass_codem['ass_sub_cat_code'];
	
			$ass_name = $ass_codem['ass_sub_cat_name'];
		?>
    <tr class="dark">
      <td height="30" align="center" valign="middle"><?php echo $j;?></td>
      <td width="445"><?php echo $ass_name;?></td>
      <td width="30" align="center"><?php echo $ass_codem['unit'];?></td>
      <?php 
			for($i=1;$i<=$acount;$i++)
			{
				$asssite = "select * from sitename where id='$i'";
				$asssiter = mysql_query($asssite,$conn);
				while($asssitem = mysql_fetch_array($asssiter))
				{
					$assn = $asssitem['sitename'];
			
			$as_in_site = "select *,sum(no_of_qty),sum(transfer_qty) from assetinsite where sitename='$assn' and ass_sub_cat_code='$ass_co'";
	//echo $as_in_site;
	$as_in_r = mysql_query($as_in_site,$conn);

	while($as_in_m = mysql_fetch_array($as_in_r))
		{
			$ass_in_ste = $as_in_m['sitename'];
			$ass_in_code = $as_in_m['ass_sub_cat_code'];
			$ass_qty[$assite] = $as_in_m['sum(no_of_qty)'];	
			$ass_trans[$assite] = $as_in_m['sum(transfer_qty)'];
			$ass_in_site_total = $ass_qty[$assite]+ $ass_trans[$assite];
			
			$ass_trasf = "select * from asset_transfer where asset_from='$ass_in_ste' and ass_sub_cat_code='$ass_in_code'";
			$ass_transfr = mysql_query($ass_trasf,$conn);
			$ass_transfm = mysql_fetch_array($ass_transfr);

					$ass_trans_qty = $ass_transfm['no_of_item'];
					
					$ass_in_fine_total= $ass_in_site_total - $ass_trans_qty
					
	  ?>	  
      <td align="center"><?php echo $ass_in_fine_total; } } } ?></td>
      <td><?php $ftotal = "select *,sum(no_of_qty),sum(transfer_qty) from assetinsite where ass_sub_cat_code='$ass_co'";
	  			//echo $ftotal;
	  			$ftoalr = mysql_query($ftotal,$conn);
				$ftotalm = mysql_fetch_array($ftoalr);
				$tqftotal = $ftotalm['sum(transfer_qty)'];
				$sqtotal = $ftotalm['sum(no_of_qty)'];
				
				$rtotal = "select sum(no_of_item) from asset_transfer where ass_sub_cat_code='$ass_co'";
				//echo $rtotal;
				$rtotalr = mysql_query($rtotal,$conn);
				$rtotalm = mysql_fetch_array($rtotalr);
				
				$rqtotal = $rtotalm['sum(no_of_item)'];
				
				$rrtotal = $tqftotal - $rqtotal;
				
				echo $sttotal = $sqtotal+$rrtotal;
				
	  ?></td>
      </tr><?php $j++;} } }?>
    </tbody>
  </table>
Question Answered as of 2 Years Ago by HITMANOF44th
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: