How to echo UNION of mysql statement?

[zac1987]

$query120 = "SELECT frenusername FROM friendship WHERE username='{$username2}' UNION SELECT username FROM friendship WHERE frenusername='{$username2}' ";
$result120 = mysql_query($query120,$connection) or die (mysql_error());
confirm_query($result120); 
while($userinfo120 = mysql_fetch_array($result120)){
$frenusername= $userinfo120['frenusername'];
$frenusername2= $userinfo120['username']; // why this hold empty value?
echo $frenusername2;
}

May I know why $frenusername2 is holding empty value?

[zac1987]

$query120 = "SELECT frenusername FROM friendship WHERE username='{$username2}' UNION SELECT username FROM friendship WHERE frenusername='{$username2}'";
$result120 = mysql_query($query120,$connection) or die (mysql_error());
confirm_query($result120); 
while($userinfo120 = mysql_fetch_array($result120)){
$frenusername= $userinfo120['frenusername'];
$frenusername2= $userinfo120['username']; // why this hold empty value?
echo $frenusername2;
}

May I know why $frenusername2 is holding empty value?

 

Here is my table records :

username | frenusername

---------------------------------

zac1987 | qq

zac1987 | bb

uu | zac1987

oo | zac1987

 

The value of $username2 is "zac1987". Supposedly $frenusername2 hold value of "uu" and "oo". I don't know why it fail to hold the value, it is holding empty value now.

[jcbones]

Why not just run your query like?

$query120 = "SELECT frenusername, username FROM friendship WHERE username='{$username2}' OR frenusername='{$username2}' ";

[zac1987]

because it will include "zac1987" on the output. For example, if $username2 = "zac1987",  "SELECT frenusername, username FROM friendship WHERE frenusername='{$username2}' ";, the output will include "zac1987" as you SELECT frenusername.

[wildteen88]

What are you trying to do? From your code it looks like you're wanting to get all users that have "zac1987" in frenusername column? If thats the case then your query just needs to be

$query120 = "SELECT username FROM friendship WHERE frenusername='{$username2}' ";
$result120 = mysql_query($query120,$connection) or die (mysql_error());

 

Then loop through the results to get all "zac1987"'s friends

$friends = array();
while($row = mysql_fetch_assoc($result120))
    $friends[] = $row['username'];

The above code will add each friend to the $friends array.

 

How you'd display the results from the query

echo "$username2 is friends with " . implode(', ', $friend);

[zac1987]

Both field frenusername and username have "zac1987". So I need to use UNION to SELECT frenusername WHERE username="zac1987" and SELECT username WHERE frenusername="zac1987". But the UNION can't give the output that I want. I guess I need to read the rules of using UNION.

[wildteen88]

What is it exactly are you trying to do?

[jcbones]

$query120 = "SELECT frenusername, username FROM friendship WHERE username='{$username2}' OR frenusername='{$username2}' ";
$result = mysql_query($query120) or trigger_error(mysql_error());
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) {
   if($row['frenusername'] == $username2) {
     $friends[] = $row['username'];
   }
   elseif($row['username'] == $username2) {
    $friends[] = $row['frenusername'];
   }
  }
}

echo $username2 . ' has ' . count($friends) . ' friends.  They are ' . implode(', ',$friends);

 

Surely there is a better way to design your friends system.

[zac1987]

$query120 = "SELECT frenusername, username FROM friendship WHERE username='{$username2}' OR frenusername='{$username2}' ";
$result = mysql_query($query120) or trigger_error(mysql_error());
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) {
   if($row['frenusername'] == $username2) {
     $friends[] = $row['username'];
   }
   elseif($row['username'] == $username2) {
    $friends[] = $row['frenusername'];
   }
  }
}

echo $username2 . ' has ' . count($friends) . ' friends.  They are ' . implode(', ',$friends);

 

Surely there is a better way to design your friends system.

 

WOW, I never knew can codes like that, nice work. Some people said UNION is faster than OR. That's why I use UNION.