Displaying friends on profile page

[enzo420]

Hi all, i've recently made a friends script that adds the id of user who requested the friend (user) and the requestee (req), with a confirmed column, 0 if pending, 1 if accepted.

i was looking for a way to search through the friends table for all friends of a certain id, i would need to search user column and req column where confirmed =1, then put all the values into an array and list them on the profile page. but im stuck,

$connect = mysql_connect("localhost", "root", "") or die (mysql_error());
$connect;
mysql_select_db ("phplogin");
$userfriend = mysql_query ("SELECT req FROM friends WHERE user='$profileid' AND confirmed=1");
$userfriendarray = mysql_fetch_array($userfriend);
$userfriendreq = $userfriendarray['req'];


$userreq = mysql_query ("SELECT user FROM friends WHERE req='$profileid' AND confirmed=1");
if ($userreq != 0)
{
$userreqarray = mysql_fetch_array($userreq);
$userreq2 = $userreqarray['user'];
}
if ($userreqarray != 0 && $userfriendarray!= 0) // merges arrays if they both contain data
{
$friends = array_merge($userreqarray, $userfriendarray);
$friend1 = array_rand($friends);
}
elseif ($userreqarray != 0)
{
var_dump ($userreqarray);
foreach($userreqarray as $userreq2);
	{
	echo "$userreq2";
	}
}
elseif($userfriendarray != 0)
{
foreach($userfriendarray as $req5 => $reqno) // there is one entry in the table yet this returns 2 values? i dont know why?
	{
	 echo "$reqno";
	}
}
echo '<br></center></a></div>';



?>

$rs = mysql_query("SELECT * FROM friends WHERE (reg = '$id' AND confirmed = '1') OR (user ='$id' AND confirmed = '1')");

while($data = mysql_fetch_array($rs)){
   if($data['user'] != $id){
      $friendarray[] = $data['user'];
   }else{
      $friendarray[] = $data['req'];
   }
}

This should place all the friends 'ids' the user stipulated as 'id' into an array called $friendarray.

If you want the actual usernames in the array, just use an INNER JOIN to get this data from the users table. I gotta go for a minute. Will be back later with this.

OK

$rs = mysql_query("SELECT friends.*,users2.username,reqs.username AS requsername
from friends
LEFT JOIN users users2 ON friends.user = users2.id
LEFT JOIN users reqs ON friends.req = reqs.id WHERE confirmed = 1 AND (req = 1 OR `user` = 1)");

This should give you the following fields:

id
user
req
confirmed
username
requsername

while($data = mysql_fetch_array($rs)){
   if($data['user'] != $id){
      $friendarray[] = $data['username'];

   }else{
      $friendarray[] = $data['requsername'];
   }
}

OR:

while($data = mysql_fetch_array($rs)){
   if($data['user'] != $id){
      $friendarray[] = array($data['user'],$data['username']);

   }else{
      $friendarray[] = array($data['req'], $data['requsername']);
   }
}

You now have an array of arrays that link the id of the friend to the friend username. Useful if you want to create hyperlinks of the friend username, e.g. to go to the friend's public profile page, or to send a PM. You can also sort the arrays alphabetically, if required.

[enzo420]

OK

$rs = mysql_query("SELECT friends.*,users2.username,reqs.username AS requsername
from friends
LEFT JOIN users users2 ON friends.user = users2.id
LEFT JOIN users reqs ON friends.req = reqs.id WHERE confirmed = 1 AND (req = 1 OR `user` = 1)");

is user2 the users table? also is reqs supposed to be plural or do i copy the column name exactly?

cant get it to work :S

OK

$rs = mysql_query("SELECT friends.*,users2.username,reqs.username AS requsername
from friends
LEFT JOIN users users2 ON friends.user = users2.id
LEFT JOIN users reqs ON friends.req = reqs.id WHERE confirmed = 1 AND (req = 1 OR `user` = 1)");

is user2 the users table? also is reqs supposed to be plural or do i copy the column name exactly?

cant get it to work :S

users2 is an alias for your 'users' table
reqs is another alias for your 'users' table

I have taken that your 'users' table has the following fields (amongst others):

id (an autoincrement integer primary key)
username (varchar field holding user's handle)

Your 'friends' table, I have taken to have the following fields:

id (just an autoincrement integer primary key)
user (foreign key on id in the 'users' table)
req (foreign key on id in the 'users' table)
confirmed (0 = pending, 1 = confirmed)

The purpose of the sql is to link the 'friends.user' field (which is an user id) to the 'users.id' field and also link 'friends.req' field (which is also an user id) to the 'users.id' field. Thus we create two 'new' fields called 'username' and 'requsername'

For example:

users

id    username
1     ardav
2     filo34
3     canzo
4     peeps456
5     costas
6     triage
7     carchaser
8     rugbyboy20

friends

id    user     req     confirmed
1     1          8        1
2     1          6        1
3     2          1        1
4     3          1        0
5     2          3        1
6     4          8        1

if user '1' (ardav) is the focus, the recordset should output

id    user    req    confirmed   username   requsername
1     1         8       1          ardav          rugbyboy20 
2     1         6       1          ardav          triage
3     2         1       1          filo34          ardav

Your array (if taking the last example I posted), should be:

array(
  array(8, 'rugbyboy20'),
  array(6, 'triage'),
  array(2, 'filo34')
)

I may have named some of your db fields or tables incorrectly, otherwise it should work.

for each array you could make links like this:

echo "<a href=\"profile.php?id={$fieldarray[$i][0]}\">{$fieldarray[$i][1]}</a>";

where $i is the counter in the loop.

[enzo420]

everything is bang on apart from i dont have foreign keys set up?

how do i do that in phpmyadmin? ive googled it a couple of times but nothing useful comes up?

You don't actually need to explicitly define foreign keys. Purists may scoff at this as you may end up with invalid data in the friends table. However, if you take care not to delete users, the friends records should remain valid.

However, defining indices should speed up querying, as long as you don't define indices on fields that do not appear in WHERE clauses.

ALTERNATIVELY
If you want to set constraints (actually define the foreign keys), see this blog page: http://www.mytechmusings.com/2008/04/using-foreign-keys-in-mysql.html

It's one of the best examples (including video tutorials) of how to set up FKs in phpmyadmin.

If you are using InnoDB tables, this should be straightforward. If using MyISAM tables, you'll probably need to convert them to InnoDB.

[enzo420]

when i said bangon i meant the information you put up about tables was correct... i still cant get it to work, the query returns false

OK, if you want to send me an exported *.sql file of your DB, I can import it and give it a whirl, see what we get. The above code works fine with a duplicate DB I produced (from the info you gave). Just attach the file to your next post.

[enzo420]

-- Table structure for table `friends`
--

CREATE TABLE IF NOT EXISTS `friends` (
`user` varchar(30) NOT NULL,
`req` varchar(30) NOT NULL,
`confirmed` tinyint(1) NOT NULL DEFAULT '0',
KEY `req` (`req`),
KEY `req_2` (`req`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `friends`
--

INSERT INTO `friends` (`user`, `req`, `confirmed`) VALUES
('7', '10', 1),
('8', '7', 1),
('7', '5', 1),
('8', '9', 1);

thats it there man cheers

Sorry to be a pain - send your users table too - this will give me an idea as to why the LEFT JOIN may not be working.

OK your problem is that the user and req fields are actually TEXT instead of integer. Change the data type to "integer" for these fields and try the query again.

[enzo420]

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(25) NOT NULL,
  `password` varchar(45) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

changed the user and req values to int but it still doesn't work.

i do get this message now in phpmyadmin
"The indexes req and req_2 seem to be equal and one of them could possibly be removed."

Well, I just added 10 users to your empty users table (with ids between 1 and 10) and then run the following query:

SELECT friends.*,users2.username,reqs.username AS requsername
from friends
LEFT JOIN users users2 ON friends.user = users2.id
LEFT JOIN users reqs ON friends.req = reqs.id WHERE confirmed = 1 AND (req = 7 OR `user` = 7)

I got exactly 3 records - the ones expected.

Just remember the (req = 7 OR `user` = 7) is hard-coded in the example I supplied. This value (7) should be a variable passed via $_GET/$_POST or more appropriately, $_SESSION.

When the user logs in, a $_SESSION or similar can be set with the logged in user's id. At the appropriate places in your pages, you can do this:

$id = $_SESSION['user_id'];
$query = mysql_query("SELECT friends.*,users2.username,reqs.username AS requsername
from friends
LEFT JOIN users users2 ON friends.user = users2.id
LEFT JOIN users reqs ON friends.req = reqs.id WHERE confirmed = 1 AND (req = $id OR `user` = $id)");
...etc...

[enzo420]

the query works when i run it in phpmyadmin, i dont know whats wrong with the code that i cant get all the values of $friendarray,

while($data = mysql_fetch_array($rs))
{   

if($data['user'] != $profileid)
{      
$friendarray[] = array($data['user'],$data['username']);
}

else
{
$friendarray[] = array($data['req'], $data['requsername']);  
}
echo "$friendarray[$i][0]";
$i++;

}

should i put a foreach statement before the first if statement? i need to walk through each value in the $rs query, does $data contain all the rows or just the top row as an array?

the query works when i run it in phpmyadmin, i dont know whats wrong with the code that i cant get all the values of $friendarray,

$i = 0; 
while($data = mysql_fetch_array($rs)){   
   if($data['user'] != $profileid){      
      $friendarray[] = array($data['user'],$data['username']);
   }else{
      $friendarray[] = array($data['req'], $data['requsername']);  
   }
      echo $friendarray[$i][0];
      $i++;
   }

should i put a foreach statement before the first if statement? i need to walk through each value in the $rs query, does $data contain all the rows or just the top row as an array?

The $rs object contains the all the records filtered and fields declared by the query:

To find out the number of records returned and actual fieldnames, do this:

$num = mysql_num_rows($rs); //gets the number of records returned by the query
echo $num . "<br /><br />";

$data = mysql_fetch_array($rs); //just get one record for now.
print_r($data); //will display the array elements

Remember that this: echo $friendarray[$i][0]; will only print out the 'id' of each 'friend'. If you wan the username: echo $friendarray[$i][1];

[enzo420]

i have 3 rows returned by $rs but when i run that code it skips the 1st row and starts on the 2nd row?

this data comes back from $rs in phpmyadmin...

user req confirmed username requsername
8 7 1 enzo george
8 9 1 enzo frank
11 8 1 elvis enzo

$num = mysql_num_rows($rs) outputs 3 rows, however when i run the code you mentioned it only outputs 9 and 11 as the id's? where is number 7?? surely the 1st row is essentially the same as the second row and should be displayed?

If you've done a mysql_fetch_array($rs) before your loop, it means that the "pointer" has moved on to the second record. You should only have one mysql_fetch_array() function for each recordset object, so just have your while loop.

[enzo420]

man your a legend thanks for all the help...

i got it solved :)

[enzo420]

its works perfectly man im really glad you helped me, ive just got some after-questions (not really worth a new thread)

1. if i have an avatar followed by text, is there css code so that the text is vertically centered beside the image? i tried vertical-align values but none of them make a difference.

2. how do i order the friends alphabetically by username?

3. if someone has like 100 friends and i can only display 20 per page on the friends list, how can i make the friends list have multiple pages? (eg in this thread you can only have like 10 posts per page so new pages are made for the new friends)

1.
Image elements can't be set to display:block as far as I know. Forum post pages can be placed in a table. OK, semantically, perhaps this isn't proper use of a table, but trying to code it through CSS alone and making it compatible with each browser (past and present) can be a real pain. There's often a trade-off between the lofty purist outlook of semantic HTML/CSS and reality.

I haven't messed with 'new' CSS for a while (still using my old boilerplates), so a bit rusty, but a <td> element will definitely allow you to vertically center an image and associated text.
A <div> should allow the same functionality.

2.
Hmm, look up some array functions in the php manual. Can't remember which one off top of my head.
If you can't find an answer, you could change the array to create something a bit more useful.

Thinking about it, we could probably use a conditional command in the SQL query to create a "friend" field which would just have the name of the friend (whether the friend is in username or requsername). If we can do this, we could cut down on quite a bit of work. Leave it with me for an hour or so.

3.
Yes, you certainly can. This is called "pagination". There have been "hundreds" of threads in the php forum on this. I suggest you search for these. Some of the solutions are beautifully succinct - much better than anything I could suggest. However, the main thrust of each solution is the LIMIT clause in the query.

If you want to re-post this (question 1,3 anyway - question 2 would involve repeating the whole rigamarole), fire away. Nobody (much) is going to look at this thread now as it is SOLVED.

What a fool I've been! Could have used the UNION method. This allows ordering via query!

$profile_id = $_SESSION['user_id']; //or similar - this is the current logged in user id
$page = $_GET['page']; //get the page number from the querystring

$x = ($page - 1) * $num_per_page; //start at record number 
$y = $num_per_page; //number of records to display

$rs = mysql_query("(SELECT id, username FROM users INNER JOIN friends ON users.id = friends.`user` WHERE friends.req =$profile_id AND confirmed=1)
UNION
(SELECT id, username FROM users INNER JOIN friends ON users.id = friends.req WHERE friends.user =$profile_id AND confirmed=1)
ORDER BY username LIMIT($x,$y)");

... check number of records at this point...

$output = "<ul>";
while($data = mysql_fetch_array($rs)){
  $output .= "<li><a href=\"profile.php?id={$data['id']}\">{$data['username']}</a></li>";
}
$output .= "</ul>";

...etc...

echo $output;

There are holes here - you need to check whether somebody is logged in ($_SESSION). You need to see if the page number from the querystring is valid - if someone types 20000 into the querystring if only 2 pages exist - what happens? Check number of records - if none exist, don't echo anything etc.

Hope that helps - sorry for missing the simple - however it is a little slower that the other method.

[enzo420]

man its ok honest ive got it doing what i want. ive used this pagination script.

<?php class display {	function pagination($rows, $per_page, $current_page, $page_link) {		global $core,$C; 		// Create a Page Listing		$this->pages = ceil($rows / $per_page); 		// If there's only one page, return now and don't bother		if($this->pages == 1) {			return;		} 		// Pagination Prefix                $output .= "<!-- Pagination by Dennis Pedrie. Used by Permission -->";		$output = "Pages: "; 		// Should we show the FIRST PAGE link?		if($current_page > 2) {			$output .= "<a href=\"". $page_link ."?page=1/\" title=\"First Page\"><<</a>";		} 		// Should we show the PREVIOUS PAGE link?		if($current_page > 1) {			$previous_page = $current_page - 1;			$output .= " <a href=\"". $page_link .">page=". $previous_page ."/\" title=\"Previous Page\"><</a>";		} 		// Current Page Number		$output .= "<strong>[ ". $current_page ." ]</strong>"; 		// Should we show the NEXT PAGE link?		if($current_page < $this->pages) {			$next_page = $current_page + 1;			$output .= "<a href=\"". $page_link ."?page=". $next_page ."/\" title=\"Next Page\">></a>";		} 		// Should we show the LAST PAGE link?		if($current_page < $this->pages - 1) {			$output .= " <a href=\"". $page_link ."?page=". $this->pages ."/\" title=\"Last Page\">>></a>";		} 		// Return the output.		return $output;	}} $display = new display;echo $display->pagination("45", "15", "1", "http://mysite.com/index.php");?>

however when i use the code

while($data = mysql_fetch_array($rs))
{   
   if($i < 5)
   {

when i want 5 results on each page, the pointer jumps back to the start of the $rs query on each page.. how do i make it continue from its previous place?

You need to set this in the LIMIT clause. See the example I provided.

Your starting record will be the (page number - 1) X num of records per page. This is true if your page numbers start on 1.