Relative Info from two Tables.. How Do I Do This ? :(
Posted: Tue Nov 04, 2003 4:48 am
I have two tables in my DB,
1 : auth
auth has fields called user_id & real_name
2: project
project has fields called user_id, project_id & project_name
user_id in project table is relative to user_id in auth, that is to determine which project belong to which user, so a user with user_id "1" can only see and access projects with user_id "1".
But when admin is logged in he can view all the project and whom they belong.
what I'm trying to do is while listing the content of project table I want to retrive real_name from auth table.
don't laugh.. but what I did previously was creating recordset in each loop of displaying record..
that is
this outputs the data I want
so is there any way where I can create a single recordset and retrive related value in loop?
one posibility I felt was creating array.... hmm... I dunno but how to go about it.... there could some other way also...
now the most important part of the story....
Can anyone help me out ? ...
1 : auth
auth has fields called user_id & real_name
2: project
project has fields called user_id, project_id & project_name
user_id in project table is relative to user_id in auth, that is to determine which project belong to which user, so a user with user_id "1" can only see and access projects with user_id "1".
But when admin is logged in he can view all the project and whom they belong.
what I'm trying to do is while listing the content of project table I want to retrive real_name from auth table.
don't laugh.. but what I did previously was creating recordset in each loop of displaying record..
that is
Code: Select all
<?php
mysql_select_db($db_conProj, $conProj);
$query_rsProj = sprintf("SELECT * FROM project");
$rsProj = mysql_query($query_rsProj, $connProj) or die(mysql_error());
$row_rsProj = mysql_fetch_assoc($rsProj);
do {
?>
Project Name : <?php echo $row_rsProj['project_name'] ?><br>
Project Owner : <?php echo $row_rsProj['user_id']." - "; // this gives me only user_id, so I built recordset here..
$uid = $row_rsProj['user_id'];
mysql_select_db($db_conProj, $conProj);
$query_rsUser = sprintf("SELECT realName FROM auth WHERE user_id = '$uid'");
$rsUser = mysql_query($query_rsUser, $connProj) or die(mysql_error());
$row_rsUser = mysql_fetch_assoc($rsUser);
echo $row_rsUser['realName'];
mysql_free_result($rsUser);
?>
<br><br>--------------------------------------<br><br>
<?php } while ($row_rsProj = mysql_fetch_assoc($rsProj)) ?>BUT.. but I feel it's not right to create recordset in every single record i'm fetching. It might not be that slow when I'm fecthing 10-20 records but what if records are more than 50 per page ?
Project Name : Aceteation using Sodium Acetate
Project Owner : 2 - Chemistry Batch
----------------------------------------------------------
Project Name : De-construction of methenol
Project Owner : 2 - Chemistry Batch
----------------------------------------------------------
Project Name : refraction index
Project Owner : 1 - Light (physics 1)
----------------------------------------------------------
Project Name : Mass, Motion & Gravitational Force
Project Owner : 4 - Solid Physics
so is there any way where I can create a single recordset and retrive related value in loop?
one posibility I felt was creating array.... hmm... I dunno but how to go about it.... there could some other way also...
now the most important part of the story....
Can anyone help me out ? ...