Facebook

Databases
1

hi, i need something and i dont know how to develop it :frowning:

so,

we all use facebook, i hope

on the right, facebook has “friends of your friends that you may know”

can someone post the mysql code because i need that code.

now, i explain:

i know the A id and the B id;

this is the code that i use to extract common friends:

SELECT * 
FROM (
SELECT  * 
FROM  `friend` 
WHERE  `id-member-a` =  '".$_SESSION['profile-id']."'
)a
INNER JOIN (
SELECT  * 
FROM  `friend` 
WHERE  `id-member-a` =  '$id'
)b
ON  `a`.`id-member-b` =  `b`.`id-member-b`

it works.

but i need the friends of my friends, that are not friends with me.

this is the table:

website id-member-a id-member-b name-a name-b accept-a accept-b
tutispot-com 1 2 ionu csaba 1 1
tutispot-com 2 1 csaba ionu 1 1
tutispot-com 1 3 ionu id3 1 1
tutispot-com 3 1 id3 ionu 1 1
tutispot-com 2 4 csaba id4 1 1
tutispot-com 4 2 id4 csaba 1 1
tutispot-com 1 5 ionu id5 1 1
tutispot-com 5 1 id5 ionu 1 1
tutispot-com 2 5 csaba id5 1 1
tutispot-com 5 2 id5 csaba 1 1
tutispot-com 1 6 ionu id6 1 1
tutispot-com 6 1 id6 ionu 1 1
tutispot-com 1 7 ionu id7 1 1
tutispot-com 7 1 id7 ionu 1 1
tutispot-com 1 8 ionu id8 1 1
tutispot-com 8 1 id8 ionu 1 1
tutispot-com 1 9 ionu id9 1 1
tutispot-com 9 1 id9 ionu 1 1

so: if A is friend with B, then B is also friend with A; so i have 2 rows for 1 relationship

more info:

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- [http://www.phpmyadmin.net](http://www.phpmyadmin.net/)
--
-- Host: localhost
-- Generation Time: Nov 13, 2010 at 01:11 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `pixar`
--

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

--
-- Table structure for table `friend`
--

CREATE TABLE IF NOT EXISTS `friend` (
  `website` varchar(100) NOT NULL,
  `id-member-a` int(11) NOT NULL,
  `id-member-b` int(11) NOT NULL,
  `name-a` varchar(100) NOT NULL,
  `name-b` varchar(100) NOT NULL,
  `accept-a` int(11) NOT NULL,
  `accept-b` int(11) NOT NULL,
  UNIQUE KEY `website` (`website`,`id-member-a`,`id-member-b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `friend`
--

INSERT INTO `friend` (`website`, `id-member-a`, `id-member-b`, `name-a`, `name-b`, `accept-a`, `accept-b`) VALUES
('tutispot-com', 1, 2, 'ionu', 'csaba', 1, 1),
('tutispot-com', 2, 1, 'csaba', 'ionu', 1, 1),
('tutispot-com', 1, 3, 'ionu', 'id3', 1, 1),
('tutispot-com', 3, 1, 'id3', 'ionu', 1, 1),
('tutispot-com', 2, 4, 'csaba', 'id4', 1, 1),
('tutispot-com', 4, 2, 'id4', 'csaba', 1, 1),
('tutispot-com', 1, 5, 'ionu', 'id5', 1, 1),
('tutispot-com', 5, 1, 'id5', 'ionu', 1, 1),
('tutispot-com', 2, 5, 'csaba', 'id5', 1, 1),
('tutispot-com', 5, 2, 'id5', 'csaba', 1, 1),
('tutispot-com', 1, 6, 'ionu', 'id6', 1, 1),
('tutispot-com', 6, 1, 'id6', 'ionu', 1, 1),
('tutispot-com', 1, 7, 'ionu', 'id7', 1, 1),
('tutispot-com', 7, 1, 'id7', 'ionu', 1, 1),
('tutispot-com', 1, 8, 'ionu', 'id8', 1, 1),
('tutispot-com', 8, 1, 'id8', 'ionu', 1, 1),
('tutispot-com', 1, 9, 'ionu', 'id9', 1, 1),
('tutispot-com', 9, 1, 'id9', 'ionu', 1, 1);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2

I’m assuming you’re trying to do this in MySQL; so we throw out the EXCEPT syntax…

SELECT * FROM `friend` WHERE
`id-member-b` != $myID AND
`id-member-a` IN(SELECT `id-member-b` FROM friend WHERE `id-member-a` = $myID) AND
`id-member-a` NOT IN(SELECT `id-member-a` FROM friend WHERE `id-member-b` = $myID);

(I fully expect r987 to come along and do it better than this.)

3

something like:

SELECT `a`.`id-member-b`,`a`.`name-b` FROM `friend` a WHERE `a`.`id-member-a` IN 
(SELECT `b`.`id-member-b` FROM `friend` b WHERE `b`.`id-member-a`='1') 
HAVING `a`.`id-member-b`!='1' and `a`.`id-member-b`!='2'

this code works 60%

4

how do you define “me”?

what’s the difference between $_SESSION[‘profile-id’] and $id ?