CODEWITHSUNDEEP
phpmysql
unicornication
unicornication

Reputation: 627

Alternative for this query?

I'm trying to find out who the friends of 'x' are, and using that array of data to query another table.

What I have so far is

<?
session_start();
$user = $_SESSION['username'];
include($_SERVER['DOCUMENT_ROOT'].'/assets/global/scripts/***.php');
$data = mysql_query("SELECT * FROM hotm**e_rates WHERE (SELECT * FROM hot**e_friends WHERE f1='$user' OR f2='$user') ORDER BY id LIMIT 0,1");
$row = mysql_fetch_assoc($data);
if($row['f1']==$user) {
echo $row['f2'];
}else{
echo $row['f1'];
}
?>

That doesnt work. :( error is that the return val is a bool.

Any ideas on how to fix this or use an alternative?

thanks! :D

Upvotes: 0

Views: 112

Answers (2)

bencoder
bencoder

Reputation: 846

I don't think you can do it like Jeff suggests - if the user has more than 1 friendship then it will fail since the subquery will return more than one row. I'm assuming that there is a field 'username' in the hotm**e_rates table, that we can use to join to. I've also made the assumption that you want to get all the _rates that are associated with the friends of $user:

SELECT
    r.*,
    f.f1,
    f.f2
FROM
    hotm**e_rates   r,
    hotm**e_friends f
WHERE
    (f.f1 = '$user'
AND r.username = f.f2)
OR  (f.f2 = '$user'
AND r.username = f.f1)

I'm not entirely sure if the above will work since it's joining dynamically on different fields... that might be problematic and I haven't tested it, if it doesn't work then you should be able to do it with a union, joining on the f1 and f2 separately and unioning the result sets together:

(   SELECT
        r.*,
        f.f1,
        f.f2
    FROM
        hotm**e_rates   r,
        hotm**e_friends f
    WHERE
        f.f1 = '$user'
    AND r.username = f.f2
) UNION (
   SELECT
        r.*,
        f.f1,
        f.f2
    FROM
        hotm**e_rates   r,
        hotm**e_friends f
    WHERE
        f.f2 = '$user'
    AND r.username = f.f1
)

Upvotes: 0

Shoe
Shoe

Reputation: 76240

The value returned is bool because an error occurred in your MYSQL query. You have to set something like WHERE [main_query_table] = (SELECT [table_field] FROM hot**e_friends WHERE f1='$user' OR f2='$user').

References:

Upvotes: 1

Related Questions