Page 1 of 1

sorting temporary table to generate report

Posted: Thu Jan 26, 2006 6:22 am
by kilikitix
hi everybody. this is my first topic.
i want to make a report from multiple tables.
the report should look a little like this

Code: Select all

-------------------------------------------------------------
no | code |             f1           |                f2           |
                ----------------------------------------------
     |         |      L1    |   I  |  J  |       L2    |   I  |  J   |  
-------------------------------------------------------------
 1  |         |              |      |     |               |      |      |  
     |         |              |      |     |               |      |      |
  n |         |              |      |     |               |      |      |
--------------------------------------------------------------
so you see there are nested field,
first for record (no , code) say it loop set A and second (f1,L1, I...J) called it loop set B

the data source come from tree table with similar structure table1, table2 and table3.
so I decide doing union operation on those table and create one temporary table called 'temptable'
than the 'temptable' will be sorted via multiple nested loop.

first i determine the same record base on no and code, (loop set A) from temptable, than sorting per field by loop set B. each query will be contain in array which will be print out as structured report.

The problem is, from 44 record in temptable it s only produce exactly 6 sorted field ini php isapi mode and only first 14 sorted field ini php cgi mode.

please i need some help because i'm new to this query and loop kind.

my code its a bit look like these,

maybe there is some line / statement missing but the point is i want to show more of its logic structure in simplest way .
.
i hope you know what i mean . thank you

Code: Select all

<?php 
include ("connection.php");

//look up table

$f_SQL 	= 'SELECT f FROM f_table' ;
$ij_SQL   = 'SELECT i FROM ij_table';


$result_f	= mysql_query($f_SQL);
$result_ij	= mysql_query ($ij_SQL );


$num1 = mysql_num_rows ($result_f);
$num2 = mysql_num_rows ($result_ij);

//report list

$senderSQL	= 'SELECT no ,code FROM  list ';
$result_rep1	= mysql_query($senderSQL);
$num_reps1	= mysql_num_rows($result_rep1);


$result_num	= mysql_query($sql);
$num_rows = mysql_fetch_row($result_num);

$mfacingSQL = 
'CREATE TEMPORARY TABLE temptable SELECT * FROM ('
        .'  SELECT DISTINCT * '
        . ' FROM table1
	  . ' UNION '
       .'  SELECT DISTINCT * '
        . ' FROM table2
	  . ' UNION '
         .'  SELECT DISTINCT * '
        . ' FROM table3)';
	  

mysql_query($mfacingSQL);

$tmpSQL = 'SELECT * FROM temptable';
$mainresult = mysql_query ($tmpSQL);
$num_rows= mysql_num_rows($mainresult);

for($a=0;$a<$num_rows;$a++)//loop record
{
mysql_data_seek($mainresult, 0);

for ($b=0;$b<$num_reps1;$b++)// loop list
{ 
  
   $rep_row=mysql_fetch_array($result_rep1);
  $no=$rep_row[no];
  $code=$rep_row

Code: Select all

;
 
  mysql_data_seek($result_ij, 0);
	
   		for($c=0;$c<$num1;$c++)//loop 
  		{
	  		
			 $sortir_fieldSQL =  ' SELECT * FROM temptable WHERE '
						 .' no = "'.$no.'" AND  code = "'.$code.'"'
						 .' AND ij = "'.$ij.'" LIMIT 1';
			  	    
					$mysort= mysql_query ($sortir_fieldSQL);
					$sort = mysql_num_rows($mysort);
					  
					  $myrow		= mysql_fetch_array($mysort);
		
	 	  				// ----> array operation
					  
	
					
					if ($sort)//check
					{  echo ("<br>THE SORT</br>"); $wildtango = $tango++;
						echo ("<br>OK BABY!!! TANGO NO. $wildtango </br>");
						
						$delSQL= ' DELETE'
								.' FROM temptable'
								.' WHERE '
						 		.' no = "'.$no.'" AND  code = "'.$code.'"'
						                .' AND ij = "'.$ij.'" LIMIT 1';
						
						mysql_query($delSQL);
						
					
					}
					else {
					  		// everything &nbsp
						 	
						 }  	
			 	
			  	}
		 }
	}
	mysql_query ('DROP TABLE temptable');
	mysql_free_result($mainresult); 
?>