PHP and SQL Social Operations

Question

I've got a table created using this query.

CREATE TABLE `tbl_friends` (
  `friend_id` int(11) NOT NULL auto_increment,
  `friend_status` tinyint(4) NOT NULL default '0',
  `friend_id1` int(11) NOT NULL default '0',
  `friend_id2` int(11) NOT NULL default '0',
  `friend_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `friend_confirm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`friend_id`)
) TYPE=MyISAM AUTO_INCREMENT=10 ;

I want to view the list of my friends, for that I only need to know other user's id - numeric value stored in friend_id1 or friend_id2 rows. My problem is that I don't know if friend_id1 or friend_id2 contains required data.

Please help me create a query to receive the number of another user if my number is $_SESSION['id']

Answer 1

I'm not sure what you exactly mean, but I think your looking for something like:

select distinct * from tbl_friends where friend_id1 = $_SESSION["id"] OR friend_id2 = $_SESSION["id"];

If this is not what you mean, please add some additional information.

Answer 2

Something like:

 Select distinct(id) from (
      select friend_id1 as id from friends where friend_id2 = :my_id
      union
      select friend_id2 as id from friends where friend_id1 = :my_id
 )

There real problem here is keeping the table from getting weird data. You are better off always putting two records into the table for each reciprocal relationship. Then you only need to say:

select * from friends where friend_id1 = :my_id and status = :whatever 

If you do it this way, the control data for when friendship happens might need to move to another table

Answer 3

CREATE TABLE `tbl_friends` (
  `friend_id` int(11) NOT NULL auto_increment,
  `friend_status` tinyint(4) NOT NULL default '0',
  `friend_id1` int(11) NOT NULL default '0',
  `friend_id2` int(11) NOT NULL default '0',
  `friend_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `friend_confirm` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`friend_id`)
) TYPE=MyISAM AUTO_INCREMENT=10 ;

Would be like so:

SELECT * FROM tbl_friends WHERE friend_id2 = %d OR friend_id1 = %d AND friend_status != 0 ORDER BY friend_id

More Visual example:

SELECT
    *
FROM
    tbl_friends
    WHERE
        friend_id2 = %$1d
    OR
        friend_id1 = %$1d
    AND
        friend_status != 0
    ORDER BY
        friend_id
    DESC

Then just run it threw a sprintf function and your ready.

$sql = sprintf($sql,$_SESSION['id']);