[Resolved] MySQL Query and Array help

[ethought]

I am using MySQL 4.0 so I can't use subqueries or this would be really easy. Also, postgresql is not an option. What I need to do is get the data from the following information into a single array and I am not figuring it out.

I have 2 tables: system_table and user_table.

system_table looks like:
system | tech1 | tech2

Where system is the name of the system and tech1 and tech2 or the userID's from user_table.

user_table looks like:
TID | realname

Where TID is the techID and realname is, well, the real name.

So I need to perform a query that gets the following results:

system | tech1_realname | tech2_realname

I have built the folllowing code to start with...

$system_query = "SELECT * FROM system_table ORDER BY system";
$system_result = mysql_query($system_query, $anykey) or die ("System Query Error: " . mysql_error());
$system_array = array();
$tech1_array = array();
$tech2_array = array();
$i = 0;
while ($rows = mysql_fetch_array($system_result, MYSQL_ASSOC))
{
   $system_array[$i] = $rows['system'];
   $query = "SELECT realname FROM user_table WHERE TID = " .  $rows['tech1'];
   $result = mysql_query($query, $anykey) or die ("Tech1 Fetch Error: " . mysql_error());
   $row = mysql_fetch_array($result, MYSQL_ASSOC);
   $tech1_array[$i] = $row['realname'];
   mysql_free_result($result);
   $query = "SELECT realname FROM user_table WHERE TID = " . $rows['tech2'];
   $result = mysql_query($query, $anykey) or die ("Tech2 Fetch Error: " . mysql_error());
   $row = mysql_fetch_array($result, MYSQL_ASSOC);
   $tech2_array[$i] = $row['realname'];
   mysql_free_result($result);
   $i++;      

} 

But I don't know if I need to write this differently, or how I need to combine the arrays to get what I want. Thanks for any help.

[DonChris]

mysql> select * from test3;
+------+-------+-------+
| sys | tech1 | tech2 |
+------+-------+-------+
| sys1 | 1 | 2 |
| sys2 | 2 | 2 |
| sys3 | 3 | 3 |
| sys4 | 1 | 3 |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> select * from test4;
+------+----------+
| id | realname |
+------+----------+
| 1 | chris |
| 2 | john |
| 3 | mark |
+------+----------+
3 rows in set (0.00 sec)

mysql> (select test3.sys, test4.realname from test3,test4 where test3.tech1=test4.id) union all (select test3.sys, test4.realname from test3,test4 where test3.tech2=test4.id) order by sys;
+------+----------+
| sys | realname |
+------+----------+
| sys1 | chris |
| sys1 | john |
| sys2 | john |
| sys2 | john |
| sys3 | mark |
| sys3 | mark |
| sys4 | chris |
| sys4 | mark |
+------+----------+
8 rows in set (0.00 sec)

hth
chris

[ethought]

That helps. The only thing is I really need it in the following format

+-----+---------------+----------------+
| sys | tech1_real | tech2_real |
+------+--------------+----------------+
| sys1 | John Smith| Betty Henry |
+------+--------------+----------------+

Because of the way I am displaying it on the web page. Thanks for the suggestion.

[DonChris]

[...]
$query1="select test3.sys, test4.realname from test3,test4 where test3.tech1=test4.id";
$query2="select test3.sys, test4.realname from test3,test4 where test3.tech2=test4.id";

$arr1=db_easy_result($db_handle,$query1);
$arr2=db_easy_result($db_handle,$query2);

$z=0;
for($x=0;$x<count($arr1);$x++)
{
	$sys1=$arr1[$i][0];
	$name1=$arr1[$i][1];
	for($y=0;$y<count($arr2);$y++)
	{
		$sys2=$arr2[$y][0];
		$name2=$arr2[$y][1];
		if($sys1==$sys2)
		{
			$new_arr[$z][0]=$sys1;
			$new_arr[$z][1]=$name1;
			$new_arr[$z][2]=$name2;
			$z++;
			break;
		}
	}
}

// $new_arr now contains data as you want it
// works only if your system-name are unique!

[...]

function db_easy_result($db_handle,$query)
{
	$result=mysql_query($query, $db_handle);
	if ($result)
	{
		while($row=mysql_fetch_array($result,MYSQL_NUM))
		{
			$array[]=$row;
		}
		mysql_free_result ($result);
	}
	return $array;
}

[Lars_Berg]

select s.system,t1.realname,t2.realname
from user_table t1 inner join 
system_table s on t1.tid = s.tech1 
inner join user_table t2 on
s.tech2 = t2.tid

[DonChris]

COOL!

[ethought]

Originally posted by Lars Berg

select s.system,t1.realname,t2.realname
from user_table t1 inner join 
system_table s on t1.tid = s.tech1 
inner join user_table t2 on
s.tech2 = t2.tid

[/B]

Kick Ass! Thanks. That is exactly what I have been fumbling around with for hours.