Page 1 of 1

Relative Info from two Tables.. How Do I Do This ? :(

Posted: Tue Nov 04, 2003 4:48 am
by igoy
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

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)) ?>
this outputs the data I want

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
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 ?

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 ? ... :)

Posted: Tue Nov 04, 2003 5:30 am
by twigletmac
Maybe something like this:

Code: Select all

<?php

mysql_select_db($db_conProj, $conProj);

// don't need sprintf() and should specify exactly what you're trying to
// retrieve

// maybe a simple combined query will work (haven't got your dataset to 
// test it on though)
$sql = "SELECT t1.project_name, t1.user_id, t2.realName FROM project AS t1, auth AS t2 WHERE t1.user_id = t2.user_id";

$result = mysql_query($sql, $connProj) or die(mysql_error());

// now test to make sure there are results and then loop through them
if (mysql_num_rows($result) > 0) {

	// a while loop is simpler and easier to maintain than a do...while -
	// you can see exactly what it's doing without having to scroll to 
	// the bottom of the loop
	while ($row = mysql_fetch_assoc($result)) {
		
		// assign the variables and format them for display
		$project_name = htmlspecialchars($row['project_name']);
		$uid          = $row['user_id'];
		$user_name    = htmlspecialchars($row['reslName']);

		// show the results
		echo '<p>Project Name: '.$project_name.'<br />';
		echo 'Project Owner: '.$uid.' - '.$user_name.'</p>';
		echo '<hr />';
	}
} else {
	echo 'no current projects';
}

?>
Mac

Posted: Tue Nov 04, 2003 8:56 am
by JAM
If you prefer joins, the below is an option.

Code: Select all

$sql = "
SELECT
    project.project_name,
    project.user_id,
    auth.realName
FROM
    project
    inner join auth on project.user_id = auth.user_id
"

Posted: Tue Nov 04, 2003 9:41 am
by igoy
o_O

Cool... Even for a second it didn't occured to my mind that I can play with query... I kept fiddling Recordsets and Loops and Arrays and what not.....

It worked as Charm as Mac.. awsome.
A BIG thanks ... WOMman, you are the WOMMan !!

Okay.. and thanks to JAM too... okay I don't want to sway away from Topic.... but........................

What is technical difference between both the queries you guys posted ?

if don't mind taking lil' time explaining it to me.. :)

Edit: To please Mac, I added the bold text above ;) -- JAM

Posted: Tue Nov 04, 2003 11:28 am
by JAM
Actually, I have never used anything else but as I posted, so I cant fully comment the differences.

But JOIN's directly in the database I figure is easier to use if you are doing some serious linking between tables. I used INNER JOIN, but there is aswell LEFT, RIGHT, (OUTER/INNER), CROSS join's, all making the query work abit differently...

If you intend to look more into it, this is a good start.
http://www.devshed.com/Server_Side/MySQ ... page1.html (real good)
http://mysql.dataphone.se/doc/en/JOIN.html (the source)