Ad:

PHP Discussion Thread
Marked Solved
Views: 1623

Page 1
2
3
Next

You are currently viewing page 1 of this multi-page discussion thread

Nov 2nd, 2009

Displaying friends on profile page

Expand Post »

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,

PHP Syntax (Toggle Plain Text)
$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>';
 
 
 
?>
$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>';



?>

Similar Threads

PHP Friends request system! in PHP
Using PHP to upload an image into an sql database, and then displaying on a profile p in PHP
Profile Viewing Page suggestions/ideas in DaniWeb Community Feedback
Suggestion for new field in the public profile page in DaniWeb Community Feedback
Selecting info from multiple mysql tables and display in PHP

enzo420

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2009

Permalink

Nov 2nd, 2009

-1

Re: Displaying friends on profile page

PHP Syntax (Toggle Plain Text)
$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'];
   }
}
$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.

Last edited by ardav; Nov 2nd, 2009 at 7:17 pm.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

Permalink

Nov 2nd, 2009

-1

Re: Displaying friends on profile page

PHP Syntax (Toggle Plain Text)
$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)");
$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

PHP Syntax (Toggle Plain Text)
while($data = mysql_fetch_array($rs)){
   if($data['user'] != $id){
      $friendarray[] = $data['username'];
 
   }else{
      $friendarray[] = $data['requsername'];
   }
}
while($data = mysql_fetch_array($rs)){
   if($data['user'] != $id){
      $friendarray[] = $data['username'];

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

OR:

PHP Syntax (Toggle Plain Text)
while($data = mysql_fetch_array($rs)){
   if($data['user'] != $id){
      $friendarray[] = array($data['user'],$data['username']);
 
   }else{
      $friendarray[] = array($data['req'], $data['requsername']);
   }
}
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.

Last edited by ardav; Nov 2nd, 2009 at 8:03 pm.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

Permalink

Nov 3rd, 2009

Re: Displaying friends on profile page

[QUOTE=ardav;1034735]OK

PHP Syntax (Toggle Plain Text)
$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)");
$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

enzo420

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2009

Permalink

Nov 3rd, 2009

-1

Re: Displaying friends on profile page

[QUOTE=enzo420;1035568]

Click to Expand / Collapse Quote originally posted by ardav ...

OK
PHP Syntax (Toggle Plain Text)
$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)");
$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

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

PHP Syntax (Toggle Plain Text)
id    username
   ardav
   filo34
   canzo
   peeps456
   costas
   triage
   carchaser
   rugbyboy20
id    username
   ardav
   filo34
   canzo
   peeps456
   costas
   triage
   carchaser
   rugbyboy20

friends

PHP Syntax (Toggle Plain Text)
id    user     req     confirmed
   1          8        1
   1          6        1
   2          1        1
   3          1        0
   2          3        1
   4          8        1
id    user     req     confirmed
   1          8        1
   1          6        1
   2          1        1
   3          1        0
   2          3        1
   4          8        1

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

PHP Syntax (Toggle Plain Text)
id    user    req    confirmed   username   requsername
1     1         8       1          ardav          rugbyboy20 
2     1         6       1          ardav          triage
3     2         1       1          filo34          ardav
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:

PHP Syntax (Toggle Plain Text)
array(
  array(8, 'rugbyboy20'),
  array(6, 'triage'),
  array(2, 'filo34')
)
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:

PHP Syntax (Toggle Plain Text)
echo "<a href=\"profile.php?id={$fieldarray[$i][0]}\">{$fieldarray[$i][1]}</a>";
echo "<a href=\"profile.php?id={$fieldarray[$i][0]}\">{$fieldarray[$i][1]}</a>";

where $i is the counter in the loop.

Last edited by ardav; Nov 3rd, 2009 at 3:10 pm.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

Permalink

Nov 5th, 2009

Re: Displaying friends on profile page

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?

enzo420

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2009

Permalink

Nov 5th, 2009

-1

Re: Displaying friends on profile page

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...-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.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

Permalink

Nov 9th, 2009

Re: Displaying friends on profile page

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

enzo420

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2009

Permalink

Nov 9th, 2009

-1

Re: Displaying friends on profile page

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.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

Permalink

Nov 9th, 2009

Re: Displaying friends on profile page

-- 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

enzo420

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2009

Page 1
2
3
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: display confirmation after sending the mail?

Next Thread in PHP Forum Timeline: nested while loops

DaniWeb Message

Cancel Changes

User Name
Password