[RESOLVED] Query based upon previous query

[IceRegent]

Hello. I am trying to create, I guess what would be a 'nested'? query?. I need to retreive a fieldset from one table, based upon one of the fields in my first query. The first query has been proven to work, and I get the results back ok. Here is what the first query looks like:

$query = 'SELECT `ProjID` , `UserID` , `OrigUserID` , `Proj_Name` , `Proj_Original` , `Proj_Type` , `Proj_Desc` , `Proj_File` , `Proj_Date` FROM `jos_projects` WHERE `Proj_Name` = `Proj_Original` LIMIT 0, 30 ';

// prepare the query in the database connector
$database->setQuery( $query );

// retrieve the rows as objects
$row = $database->loadObjectList();
foreach ($row as $row) {

That query retreives one entire row from a table in the database called jos_projects. The fieldset 'OrigUserID' is a integer, and I need to use the result to retreive the resultant text username from a table called jos_users. The fieldset rows of the table jos_user is set , the first three, : id, name, username...
There is more, but I only need the first 3, actually, 1 and 3. The 'OrigUserID' is a integer, and the 'id' is an autonumber integer. How do I set this into my loop, so that I retreive the correct records from the jos_user table, and substitue the alpha 'username' in place of the integer 'OrigUserID'? Here si the code of the loop that displays the information, after the 'foreach ($row as $row) {' :

$whodonit = $row->OrigUserID;
//	$query = 'SELECT `id` , `username` FROM `jos_users` WHERE `id` = `$row->OrigUserID` LIMIT 0, 30 ';
	$query = 'SELECT `id` , `username` FROM `jos_users` WHERE `id` = "$whodonit" LIMIT 0, 30 ';
	$database->setQuery( $query );
	$row1 = $database->loadObjectList();

 	echo '<table width="100%" border="2" cellspacing="2" cellpadding="2" bgcolor="#84BBD9" bordercolor="#400000">';
	echo '		<tr>';
	echo '			<td width="25%"><font face="Arial" color="#000000"> ' . $row->Proj_Name . ' </td>';
	echo '			<td width="25%"><font face="Arial" color="#000000"> ' . $row->OrigUserID . ' </td>';
	echo '			<td width="25%"><font face="Arial" color="#000000"> ' . $row->Proj_Date . ' </td>';
	echo '			<td width="10%"><font face="Arial" color="#000000"> ' . $whodonit . ' </td>';
	echo '			<td width="15%"><font face="Arial" color="#000000"> ' . $ouid->username . ' </td>';
	echo '    </tr>';
	echo '</table>' ;  
 }   	    
?>

The table displays correctly, except that the $ouid->username portion stays blank. I dont know quite how to verify the content is being grabbed from the second table. I depreately need to get this working, could someone please help?!??!

Thanks;
Ice

[Roger_Ramjet]

You do this with JOINS in the first query, not with multiple queries. Join jos_projects to jos_users twice using userid:id and origuserid:id

[IceRegent]

I am reading the statements where you sent me, but it is very difficult for me to get a clear cut understanding from all of that. It is like, overly documented with lots of technicalities etc. I will probably be old and grey before I come to grips with it based upon reading that. Can you maybe show me an example of how that would be done so I can try and analyze and compare, and maybe see the light at the end of the tunnel??

Thanks
Ice

[Roger_Ramjet]

"SELECT t1.col1, t1.col2, t2.col1, t3.col1 FROM table1 as t1 INNER JOIN table2 as t2 ON(t1.x_id=t2.id) INNER JOIN table2 as t3 ON(t1.y_id=t3.id)"

[Bunkermaster]

Slightly off topic but you should factorize your code a tad more. Copy paste is your enemy.
See the following code:

    	echo '			<td width="25%"><font face="Arial" color="#000000"> ' . $row->Proj_Name . ' </td>';
    	echo '			<td width="25%"><font face="Arial" color="#000000"> ' . $row->OrigUserID . ' </td>';
    	echo '			<td width="25%"><font face="Arial" color="#000000"> ' . $row->Proj_Date . ' </td>';
    	echo '			<td width="10%"><font face="Arial" color="#000000"> ' . $whodonit . ' </td>';
    	echo '			<td width="15%"><font face="Arial" color="#000000"> ' . $ouid->username . ' </td>';

could easily become

function tablerow( $cellcontent , $width = '25%' , $font = 'Arial' , $color = '#000000' ){
    return '			<td width="' . $width . '"><font face="' . $font . '" color="'  . $color . '"> ' . $cellcontent . ' </td>';
}
echo tablerow( $row->Proj_Name );
echo tablerow( $row->OrigUserID );
echo tablerow( $row->Proj_Date );
echo tablerow( $whodonit , '10%' );
echo tablerow( $ouid->username  , '15%' );
// etc.

the day you want to modify the HTML code you have minimal modifications to make.

[IceRegent]

Ok, I guess thats where the documentation doesnt make it clear enough: What types of joins are used for what purposes. Such as, inner joins vs outter joins?

So in your example, am I to understand that:

t1.col1 refers to my first table I want to compare, the first field I want to use, etc...

and

So, this I understand would be the first part of the code:

$query = 'SELECT `ProjID` , `UserID` , `OrigUserID` , `Proj_Name` , `Proj_Original` , `Proj_Type` , `Proj_Desc` , `Proj_File` , `Proj_Date` FROM `jos_projects` 

Now, the 'as t1' .... I dont understand what that is doing.

Let's see...

next code should be attached as:

INNER JOIN jos_users

Once again, there is that 'as t2'. What am I supposed ot be putting in its place? Or, do I place those exact wording in there, or does it stand for something?

ARRGGHHHH!

Ok, the next segment... On(t1.x_id=t2.id)

Ok, are those inside the parenthesis substitutions I need to make, or are the exact syntax?

I am so sorry to be this frustrating to you, I understand it probably all looks pretty basic and simple to you, but I read the manual and it doesnt really seem to get thru to me, s it doesnt explain what the syntax actually stands for - are they substitions I need to make, or are they exact syntax that nees to be placed in there for a reason?

[IceRegent]

OOps, I was posting as you made your post Bunkermaster. I have never seen the syntax you prescribe before, I will see if that works for me, it sure would make it simpler!

Ice

[Roger_Ramjet]

as t2 means ALIAS the table name to t2 - you need to do this as you are going to join to it twice, once for the 'origuser' and once for the 'whodunnit'. Aliasing t1 just saves typing and i like saving keystrokes.

As to join types, 85% of all joins are inner joins, 14% are left or right joins and the last 1% are all types of outer joins. Try using them and you'll soon learn about them from looking at the output you get.

[Bunkermaster]

one more question for you. Is ProjID in same table as Proj_Name , Proj_Original , Proj_Type , Proj_Desc , Proj_File , Proj_Date?
If so why not Proj_ID? Looks like you have a naming convention established. Just curious.

[IceRegent]

Thank you for your kindly and informative help Roger_Ramjet. I am leaving work now to go do other things, and will try to attack this from working with the join statements as soon as I can. I have never had to use the join statements before to get anything to work, thusly, I have not learned about them. I do appreciate the guidance, verily truly

Thanks;
Ice

[IceRegent]

Bunkermaster : I actually dont know why I have two different naming conventions going on in that table. I honestly dont know why I did that. Yes, those fields are all in the same table.

I will correspind more, and I really really really do appreciate both of yoru responses.

Thanks;
Ice

[IceRegent]

Hello;

Sorry to be getting back like this, but I just got a referral to another kind of statement that gives me the results I need with a very short change to my current coding. Here is the original query string:

$query = 'SELECT `ProjID` , `UserID` , `OrigUserID` , `Proj_Name` , `Proj_Original` , `Proj_Type` , `Proj_Desc` , `Proj_File` , `Proj_Date` FROM `jos_projects` WHERE `Proj_Name` = Proj_Original`LIMIT 0, 30 ';

Here is what it was suggested I change it to:

$query = 'SELECT u.name, p.ProjID ,p.UserID , p.OrigUserID , p.Proj_Name , p.Proj_Original , p.Proj_Type , p.Proj_Desc , p.Proj_File , p.Proj_Date FROM #__projects p , #__users u WHERE p.Proj_Name = p.Proj_Original AND p.UserID = u.id LIMIT 0, 30 ';

Then all I had to do is reference $row->name for the username to show up. I verified it that it does indeed work! I am trying to understand the whole thing, it was explained to me how it works, I just cant explain it myself, so I will paraphrase:

"FROM #__users u" is giving jos_users an "alias", so you don't have to keep typing the full name of the table. It's used in the query by putting the prefix, then a period (dot) before the field name - so you know which table the field name is coming from (avoids confusion when two tables have the same field name(s)).

With only one table in the FROM clause, the alias isn't needed, though many often use it, with multiple tables in the FROM clause, the aliases save typing.

So, after modifying the code only slightly, it performs like I wanted it to!

Thanks guys!

Ice

[bradgrafelman]

Don't forget to mark this thread resolved.