[Resolved] 2 queries, 1 result... if possible?

[spstieng]

I have two queries that, by themselves, works perfectly.

Is it possible to add these two results into one?

Here's my code:

function get_system_user($userName)
{	
  $result1 = mysql_query('SELECT userName, userPassword, systemUserID 
						 FROM passwordlist 
						 WHERE userName = "'.$userName.'"');
  $row1 = mysql_fetch_array($result1);
  $userID = $row["SystemUserID"];

  $result2 = mysql_query('SELECT tab1.*, tab2.*
						  FROM systemuser AS tab1, person AS tab2
						  WHERE tab1.systemUserID = "'.$userID.'" 
						  AND tab1.personID = tab2.personID');
  $row2 = mysql_fetch_array($result);

  return $row1.$row2;   <- or something like this
}

Note: I'm not familiar with LEFT and RIGHT joins.

Any help appreciated!

[drawmack]

function get_system_user($userName)
{	
    $sql  = "SELECT Tab0.userName,Tab0.userPassword,Tab0.systemUserID,tab1.*,tab2.* \n";
    $sql .= "FROM passwordlist as Tab0, systemuser AS Tab1, person AS Tab2 \n";
    $sql .= "WHERE Tab0.userName=\"$userName\" AND Tab1.systemUserID = Tab1.userID AND Tab1.personID = Tab2.perdonID";

$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
return $row;
}

[jdiver]

	$result1_=_mysql_query('SELECT tab1.*, tab2.* 
							FROM passwordlist AS p
							LEFT JOIN systemuser AS tab1 ON p.systemUserID = tab1.SystemUserID 
							LEFT JOIN person AS tab2 ON tab1.personID = tab2.personID
							WHERE p.userName = "'.$userName.'"');

Untested, but give it a try. 🙂

[spstieng]

Thanks jdiver, that ALMOST worked 😃

The result i get is:
systemUserID | personID | restriction | personID | fName | mName | lName | personTlf

Only 2 things.

personID is displayed TWICE. How do I go about just getting that ONCE? I tried using DISTINCT, but that didn't work.
Any suggestions?

The second thing is that it must include the results from passwordlist, but I'll include that with p.*.

drawmack
Are you sure you can fetch results from 3 tables without using LEFT or rIGHT joins? I've tried that before without success.
I played around with your suggestion, but couldn't get it working.

[jdiver]

I'll take "almost" with the way today has been going. 😉

You can limit the fields you want to pull in the query. So something like this:

$result1_=_mysql_query('SELECT tab1.personID, tab2.fName, tab2.mName, tab2.lName, p.* 
						FROM passwordlist AS p 
						LEFT JOIN systemuser AS tab1 ON p.systemUserID = tab1.SystemUserID 
						LEFT JOIN person AS tab2 ON tab1.personID = tab2.personID 
						WHERE p.userName = "'.$userName.'"');

I don't know if that's actually correct, since I don't know the exact structure of your tables, but that should give you a good idea on how to do it.

[spstieng]

Looks a bit embarrassed while kicking a few stones

I knew that. Really!
Sometime one just forgets the really BASIC stuff.
I thought that I had to select personID from both tables....
But one only needs to SELECT the items one wishes to display!

:rolleyes:

Thanks jdiver - a small light just went up for me now! :p

Now I just have to figure out the correct w..... YES!!!!!!!!!GOOOOOOOOOOAL! GOOOOOOOOOO SWEDEN!!!

Um... sorry - Sweden needs to beat Denmark to go into the next round of the European Championships. Unless Italy looses for Bulgarian.

As I was saying, gotta figgure out how JOINS work.
Maybe there are some GOOD tutorials on the web somewhere....