[SOLVED] trouble with query

[jakebur01]

My table for displaying members has been working ok when querying all members. I have been trying to add an extra loop that will make the table only display members that are friends of a specific member.

 

My problem is that it is grabbing the same member twice. For example if Fred is UserId1 and Tom is UserId2, then row 2 Joe is UserId1 and Fred is UserId2. It will display Fred twice. Also, I cannot get my <tr>'s to work correctly.

 

echo '<table width="100%" border="0">'; 
$getimages = mysql_query("SELECT UserId1, UserId2 FROM life_approval WHERE (`UserId2`='$q' OR `UserId1`='$q') AND `Approved` = 'Approved'", $db); 
if(!$getimages) die("Cannot execute query. " . mysql_error()); 
$countRows = mysql_num_rows($getimages); 
$i = 0; 
if ($countRows > 0) 
{ 
while ($rowimages = mysql_fetch_assoc($getimages)) 
{ 




				$result = mysql_query("SELECT Username, Company, Country, State, Link FROM life_useraccount WHERE (`Username`='$rowimages[userId1]' OR `Username`='$rowimages[userId2]') ", $db) or die(mysql_error());



					if ($i % 3 == 0) echo ($i > 0? '</tr>' : '') . '<tr>'; 

			while ($myrow = mysql_fetch_array($result)){
			//$id=$myrow["Id"];

echo '<td><center><div class="product"><a href="profile.php?q='.$myrow['Username'].'"><img src="'.$myrow['Link'].'" width="145" border="0" /></a></div><b>'.$myrow["Company"].'</b><br />'.$myrow["State"].'<br />'.$myrow["Country"].'</center></td>'; 

			}

if ($i == $countRows - 1) 
echo '</tr>'; 
$i++; 


} 
} 
echo '</table>'; 

[jakebur01]

I tried using DISTINCT, but that did not do the trick.

 

SELECT DISTINCT UserId1, UserId2 FROM life_approval WHERE (`UserId2`='$q' OR `UserId1`='$q') AND `Approved` = 'Approved'

 

 

 

[fenway]

Why not just JOIN them?

[kickstart]

Hi

 

Not quite sure what you are trying to do.

 

Can you explain exactly the database structure and what you are trying to get.

 

Seems strange to have 2 userids on one table. Unless the first table is a list of all the friends (ie, UserId1 would be Fred, while there are then multiple rows each with a different UserId2).

 

All the best

 

Keith

[jakebur01]

Hi,

 

I am trying to query the friend table (life_approval)columns(Id, UserId1, UserId2, and Approve) and see who all is a friend of the variable $q. I am trying to do this by checking both columns, if $q is a friend to someone..... he/she could be in either column. When a friend invite is stored in this table, the person the invite is sent to is stored in UserId1. {which my attempt appears to be pulling everyone out of the table in columns UserId1 and UserId2}

 

The second query is supposed to be collecting information on the friend. I was trying to do that by using ( WHERE `Username` = 'UserId1 from the first query OR `Username` = 'UserId2' from the first query.

 

Thanks,

 

Jake

 

 

 

 

 

[kickstart]

Hi

 

Think you can do that in one piece of SQL

 

SELECT  Username, Company, Country, State, Link 
FROM life_useraccount 
WHERE Username IN
(SELECT UserId1 as UserId  FROM life_approval WHERE `UserId2`='$q' 
UNION
SELECT UserId2 as UserId  FROM life_approval WHERE `UserId1`='$q' )

 

(not tested).

 

All the best

 

Keith

[jakebur01]

Man, you are amazing. It works perfect. Thank you.

 

Where are some good places to learn more about MySQL queries?

[kickstart]

Hi

 

Not sure to be honest. With me more a case of having a fair grounding in SQL (been using it since 1990 with DB2), and then just trying to think of alternative ways of doing things. Sometimes you land up barking up the wrong tree or find someone had a far better idea.

 

All the best

 

Keith

[jakebur01]

Thanks Keith.