[RESOLVED] Friend List Query

[middleman666]

Hey people,

I've wrote a query to get a list of friends that a user is friends with. The table is made up of these fields:

username
friendusername
confirmed

username and friendusername could both be the user I am trying to get the list for. confirmed contains a simple yes or no.

So what I'm trying to get the query to ask is, get the friends of the user where either the username is the user's name and confirmed is equal to yes or where the friendusername is the user's name and confirmed is equal to yes.

This is what I have:

$query="SELECT * FROM Friends 
              WHERE username = '$user' AND confirmed = 'yes' 
              OR friendusername = '$user' AND confirmed = 'yes' 
              order by rand() Limit 6"; 

This does return results, just not the correct ones.

Anyone know if my query is wrong for what I'm trying to do?

Thanks.

[NogDog]

I think you want:

$query="SELECT * FROM Friends
              WHERE (username = '$user' OR friendusername = '$user') 
              AND confirmed = 'yes'
              order by rand() Limit 6";

[middleman666]

Thanks for the reply, that code looks simpler to mine, but I'm still getting the wrong results. I think its the rest of my code...

This is the full readout:

<?php
$Fresult=mysql_query("SELECT * FROM Friends 
                      WHERE (username = '$user' OR friendusername = '$user') 
                      AND confirmed = 'yes' 
                      order by rand() Limit 6"); 
$numrows=mysql_num_rows($Fresult);

//If there are no rows
if ($numrows == 0) {
  $friends = 'There are no entries yet...';
}
else {
  while ($Friend=mysql_fetch_array($Fresult)) { 

$FFriendusername = $Friend['friendusername'];
$Fusername = $Friend['username'];

//Get user's displayname
$DNresult = mysql_query("select * from Profile 
                         where username = '$FFriendusername' 
						 or username = '$Fusername' limit 1");
$DNrow= mysql_fetch_array($DNresult);
$Fdisplayname = $DNrow['displayname'];
$userF = $DNrow['username'];

//Get Image ID
$IDresult=mysql_query("SELECT filename FROM Profile WHERE username = '$userF' LIMIT 1"); 
$IDrow=mysql_fetch_array($IDresult); 
$filename = $IDrow['filename'];

//Limit Blog Readout
if (strlen($displayname) > 8){
  $Fdisplayname = substr($Fdisplayname,0,6).'...';
}
else {
  $Fdisplayname = $Fdisplayname;
}

$friendname .= '<td class="Mcontent" align="center" valign="top"><a href="'.$PHP_SELF.'?menu=showblog&user='.$userF.'">
               <img border="0" src="Fingernails.php?id='.$filename.'"><br />'.$Fdisplayname.'</a></td>';
  }

  //Get Number of Friends
  $id = $Blog['id'];
  $Fresult=mysql_query("SELECT COUNT(*) FROM Friends 
	                      WHERE username = '$user' AND confirmed = 'yes' 
						  OR friendusername = '$user' AND confirmed = 'yes'"); 
  $num=mysql_fetch_row($Fresult); 
  $NoOfFriends = $num[0];

  $friends = $displayname.' has '.$NoOfFriends.' friends<br />
             <table border="0" cellspacing="2" cellpadding="4"><tr>
			 '.$friendname.'</tr></table>';
}


echo $friends;
?>

Basically, I'm trying to first get the friends usernames and then I get that friends display name and display image before reading it out.

Can you see where its going wrong?

Thanks.

[bradgrafelman]

Looks like you could easily combine all three queries into one using some JOINs... though I don't have enough time right now to work out the queries. I've moved this thread to the Databse forum as we have some excellent DB gurus around here...

Basically, though, the only information you really need to SELECT is the id, username, displayname, and filename columns from the Profile table, right?

[middleman666]

Sorry, I forgot to mention... I'm on a hosting plan that only has mysql version 4.0.27 which doesn't support JOINS - I know... it's rubbish hosting and I will change in the future, but for various reason, I can't at the moment

[Roger_Ramjet]

middleman666 wrote:

Sorry, I forgot to mention... I'm on a hosting plan that only has mysql version 4.0.27 which doesn't support JOINS - I know... it's rubbish hosting and I will change in the future, but for various reason, I can't at the moment

EH??? version 2 supported JOINS, let alone ver 4. What has changed with the versions is the types of join supported and the way they are parsed. JOIN SYNTAX

[middleman666]

Roger Ramjet wrote:

EH??? version 2 supported JOINS, let alone ver 4. What has changed with the versions is the types of join supported and the way they are parsed. JOIN SYNTAX

Sorry about that, was thinking about what you said in another thread 🙂 "sub queries are not supported before 4.1" - thats what I ment.

Do you think JOINs would actually solve the problem or just make my mess of a code look neater?

[bradgrafelman]

middleman666 wrote:

Do you think JOINs would actually solve the problem or just make my mess of a code look neater?

IMHO, both... it'd be more optimized, and you wouldn't have as many loops.

[middleman666]

Cheers, I'll try it out in the next few days and get back when I have something to show.

[middleman666]

hey people,

Sorry it's taken so long for a reply.

Basically, I decided not to use JOINs atm and I've just made a quick fix instead.

I add an if statement saying if the username was equal to the user then the friendusername must be the friend, otherwise the username must be the friend.

Heres the code:

<?php
//Get user's friends
$Fresult=mysql_query("SELECT * FROM Friends 
                      WHERE (username = '$user' OR friendusername = '$user') 
                      AND confirmed = 'yes' 
                      order by rand() Limit 6"); 

$numrows=mysql_num_rows($Fresult);

//If there are no rows
if ($numrows == 0) {
  $friends = 'There are no entries yet...';
}
else {
  while ($Friend=mysql_fetch_array($Fresult)) { 

$FFriendusername = $Friend['friendusername'];
$Fusername = $Friend['username'];

//ADDED IF STATEMENT ========================

if ($user == $FFriendusername){
  $ActualFriend = $Fusername;
}
else {
  $ActualFriend = $FFriendusername;
}

//========================================

//Get user's displayname
$DNresult = mysql_query("select * from Profile 
                         where username = '$ActualFriend' limit 1");
$DNrow= mysql_fetch_array($DNresult);
$Fdisplayname = $DNrow['displayname'];
$userF = $DNrow['username'];

//Get Image ID
$IDresult=mysql_query("SELECT filename FROM Profile WHERE username = '$userF' LIMIT 1"); 
$IDrow=mysql_fetch_array($IDresult); 
$filename = $IDrow['filename'];

//Limit Blog Readout
if (strlen($Fdisplayname) > 8){
  $Fdisplayname = substr($Fdisplayname,0,6).'...';
}
else {
  $Fdisplayname = $Fdisplayname;
}

$friendname .= '<td class="Mcontent" align="center" valign="top"><a href="'.$PHP_SELF.'?menu=showblog&user='.$userF.'">
               <img border="0" src="Fingernails.php?id='.$filename.'"><br />'.$Fdisplayname.'</a></td>';
  }

  //Get Number of Friends
  $id = $Blog['id'];
  $Fresult=mysql_query("SELECT COUNT(*) FROM Friends 
	                      WHERE username = '$user' AND confirmed = 'yes' 
						  OR friendusername = '$user' AND confirmed = 'yes'"); 
  $num=mysql_fetch_row($Fresult); 
  $NoOfFriends = $num[0];

  $friends = $displayname.' has '.$NoOfFriends.' friends<br />
             <table border="0" cellspacing="2" cellpadding="4"><tr>
			 '.$friendname.'</tr></table>';
}

  //Is username the owner of profile?
  if ($Owner == 'yes') {
    $friends = $friends;
  }
  else {
    //Show profile details?
    if ($showfriends == 'Yes') {
      $friends = $friends;
    }
    //friends only?
    else if ($showfriends == 'Friends Only') {
      //Is user a friend?
	  if (isset($_SESSION['valid_user'])) {
	    if ($friend == 'yes') {
          $friends = $friends;
        }
	    else {
	      $friends = 'You may have to be this user\'s friend to see their friends';
	    }
	  }
	  else {
	    $friends = 'You may have to be this user\'s friend to see their friends';
	  }
    }
    else {
      $friends = 'You may have to be this user\'s friend to see their friends';
    }
  }

echo $friends;
?>

Thanks for your suggestions.