how to reuse mysql result array in php

[bhrmalik]

Hello,

 

I need some help in retrieve and display data from MySQL table where, I had a table name “employees”

contents :

---------------------------------------------------------

id|emp_name|report_to

---------------------------------------------------------

01|Scott|00

02|John|01

03|Martin|01

04|Smith|02

05|Sam|00

06|Henry|02

 

---------------------------------------------------------

 

"00" report_to should be report direct/null.

 

One more thing is it possible to display the ID with leading zero.

 

If I want to see the "01" the display should be.

---------------------------------------------------------

MID|Manager|TLID|TeamLeader|EmpID|Employees

---------------------------------------------------------

01|Scott|02}John|04|Smith

01|Scott|02|John|06|Henry

01|Scott|03|Martin|--|------

 

If I want to see the "02" the display should be.

---------------------------------------------------------

TLID|TeamLeader|EmpID|Employees

---------------------------------------------------------

02|John|04|Smith

02|John|06|Henry

---------------------------------------------------------

 

I am using the this code in php :

 

PHP CODE :

$sql="select t1.id, t1.emp_name from employees as t1 where report_to='$_POST[text]' ";

$data=mysql_query($sql) or die(mysql_error());

echo    "<table border='2'>";

while ($rec=mysql_fetch_row($data))

echo "<tr><td>$rec[0]</td><td>$rec[1]</td></tr>";

echo " </table>";

?>

<br />

<form method="post" action="">

Username:<input type="text" name="text" />

<input type="submit" value="Submit" />

</form>

 

 

Hope to meet some expert here

 

Any help will be appreciated.

 

Thanks in advance

 

[jcbones]

I would try something along the lines of:

 

<?php
if(isset($_POST['submit'])) {
include('config.php');
$sql="SELECT
			t3.id as mID, 
			t3.emp_name as manager, 
			t2.id as tID, 
			t2.emp_name as teamleader, 
			t1.id, 
			t1.emp_name 
		FROM
			employees as t1 
		LEFT JOIN 
			employees as t2  
		ON 
			(t2.id = t1.report_to) 
		LEFT JOIN 
			employees as t3 
		ON 
			(t3.id = t2.report_to) 
		WHERE t2.id='" . mysql_real_escape_string($_POST['text']) . "'";

$data=mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($data) == 0) {
	$sql = str_replace('WHERE t2.id','WHERE t1.id',$sql);		
		$data = mysql_query($sql) or die(mysql_error());
}
if(mysql_num_rows($data) > 0) {
	echo    "<table border='2'>";
		$headers = false;
		while ($rec=mysql_fetch_assoc($data)) {
			foreach($rec as $k => $v) {
				if(empty($v)) { unset($rec[$k]); }
			}
			if(!$headers) {
				echo '<tr><th>';
				echo (isset($rec['mID']) || isset($rec['tID'])) ? 'MID</th><th>Manager</th><th>' : NULL;
				echo (isset($rec['tID']) && isset($rec['mID'])) ? 'TLID</th><th>TeamLeader</th><th>' : NULL;
				echo 'EmpID</th><th>Employee</th></tr>';
				$headers = true;
			}
			echo '<tr><td>';
			echo (isset($rec['mID'])) ? $rec['mID'] . '</td><td>' . $rec['manager'] . '</td><td>' : NULL;				
			echo (isset($rec['tID'])) ? $rec['tID'] . '</td><td>' . $rec['teamleader'] . '</td><td>' : NULL;
			echo $rec['id'] . '</td><td>' . $rec['emp_name'] . '</td></tr>';   
		}
	echo '</table>';
}
else {
	echo 'Data not found!';
}
}
?>
<br />
<form method="post" action="">
Username:<input type="text" name="text" />
<input type="submit" name="submit" value="Submit" />
</form>