Whirlpool

What seems to be the problem with this syntax??

$query =

"SELECT * " .
"FROM relationships r INNER JOIN members m, friends f " .
"ON r.inviter = m.id " .
"WHERE f.uid2 = ANY (SELECT r.recipient) " .
"AND f.uid = '$id'";

'ON r.inviter = m.id WHERE f.uid2 = ANY (SELECT r.recipient) AND f.uid = '6' at line 1

gogles writes...

(SELECT r.recipient)

If you want to put stuff into brackets like that it needs to be (select something from table...) ie. needs to be a full query.

What results are you expecting. It's hard to tell from that statement, there is a chance it can be simplified.

gogles writes...

INNER JOIN members m, friends f

This isn't how you join multiple tables, you need a separate JOIN statement for each one. I'm not really sure what you're trying to do with this query so can't help further.

You would want something more like:

SELECT * FROM relationships r INNER JOIN members m ON r.inviter = m.id INNER JOIN friends f WHERE f.uid2 = ANY (SELECT r.recipient) AND f.uid = id

Im not sure if the second inner join will work without its own 'ON' statement but thats how you add in a second inner join. Simple googling takes someone a fair way.

With this query i am trying to select the members who have become friends with the friends of f.uid, f.uid2 is the id of the friend. Can anyone see any flaws in the logic of my query?

These queries seem to get part of the job done. But now i want to select the name column of the member who has been paired with the friend.

could i add this to first query then
SELECT z.*
JOIN members z ON r.recipient = m.id
then use it as so in the php

$query = "SELECT * " .
"FROM relationships r " .
"JOIN members m ON r.inviter = m.id " .
"JOIN friends f ON r.recipient = f.uid2 " .
"WHERE f.uid = $id";

$query_2 = "SELECT * " .
"FROM relationships r " .
"JOIN members m ON r.inviter = m.id " .
"JOIN friends f ON r.recipient = f.uid " .
"WHERE f.uid2 = $id";

while ($row = mysql_fetch_array($result)) {
$epoch = $row["epoch"]; //epoch will be time when invite was made
$actions[] = array("name" => $row["z.name"],
"action" => $row["status"],
"date" => date("D j M Y @ G:i.s", $epoch));
}

Here are my tables:

create table members (
id int not null primary key auto_increment,
username varchar(40) not null,
password varchar(40) not null,
name varchar(40) not null,
location varchar(40) not null,
gender char(1) not null check gender in ('m','f'),
email varchar(40) default ',
birthday date default null,
description text default ');

create table relationships (
id int not null auto_increment,
recipient int not null references members(id),
inviter int not null,
status varchar(20) not null,
epoch date not null,
primary key (id)
);

create table friends (
id int not null auto_increment,
uid int not null,
uid2 int not null,
primary key (id)
);