Mutual results

[Canman2005]

Hi all

 

I have the following $_SESSION & $_GET set

 

<?php
$_SESSION['idnum'] = 4;
$_GET['id'] = 1;
?>

 

I then have the following db table called `people`

 

member  friend

1            2

1            3

1            4

1            8

4            3

4            5

4            8

 

What I do at the moment is run a QUERY which looks like

 

SELECT * FROM `people` WHERE `member` = $_GET['id']

 

This gets

 

member  friend

1            2

1            3

1            4

1            8

 

How could I tell the QUERY to get all rows where

 

`member` = $_GET['id'] (ID - 1)

 

and

 

`member` = $_SESSION['idnum'] (ID - 4)

 

but only return results where they both contain the same `friend`

 

So when I run the QUERY it would get only

 

member  friend

1            3

4            3

4            8

1            8

 

Because `member` contains both 1 and 4 and they both share the same `friend`

 

But then doing a GROUP BY `friend` so that it returns

 

member  friend

1            3

1            8

 

Does that make any sense? Can anyone help?

 

Thanks in advance

 

Dave

[Canman2005]

any ideas anyone?

[Maq]

Why are you assigning a number to a $_GET?  I assume for testing purposes.  Try this, not sure if it's exactly what you want but should give you a good idea:

 

*NOT TESTED*

 

$_SESSION['idnum'] = 4;
$_GET['id'] = 1;

$id = $_GET['id'];
$ses = $_SESSION['idnum'];

$query "SELECT * FROM table WHERE id = $id and friend = ANY 
        (SELECT friend FROM table WHERE member = $ses) GROUP BY member";

[GingerRobot]

So the final table is the result you're after? That is, this one:

 

member  friend

1            3

1            8

 

If so, it should be a simple case of a HAVING clause:

 

SELECT *,COUNT(*) as friend_count FROM people WHERE member = 1 OR member = 4 GROUP BY friend HAVING friend_count=2