How To: where id=(value from a different query)

[gopanthers]

This is all in one table from one database. Each row (with a unique id field) is represents data for a different person.

And there's one field in this table named "big" that represents the id field from another row (another person) in this same table. This isn't the exact situation but it's like a family tree and this field helps to display the first person's relative.

My basic query is like this and it works fine:

$id=$_GET['id'];

// query the DB
$sql = "SELECT * FROM table WHERE id=$id";
$result = mysql_query($sql);	
$myrow = mysql_fetch_array($result);

echo "this 1st person\'s first name is ".$myrow["firstname"];
echo "this 1st person\'s relative is database entry number ".$myrow["big"];

That code above works fine. 🙂

But I don't want to just display their relative's number. I want to display their relative's name, which is found in a different row in the same table. So what I want to do is display the name (and other data) from the 2nd person. I can easily display $myrow["big"], which is a number, but how do I get data from the 2nd person, like the 2nd person's name?

😕

What I tried was to make a 2nd query with slightly altered names:

$id=$_GET['id'];

// query the DB
$sql = "SELECT * FROM brotherhood WHERE id=$id";
$result = mysql_query($sql);	
$myrow = mysql_fetch_array($result);

echo "this person\'s first name is ".$myrow["firstname"];

$sql_big = "SELECT firstname FROM brotherhood WHERE id=25";
$result_big = mysql_query($sql_big);	
$myrow_big = mysql_fetch_array($result_big);

echo "the first name of this person\'s relative is".$myrow_big["firstname"];

This code does indeed work and displays the first name of the 2nd person. But you'll notice that it only works because I manually stuck the number 25 (chosen at random) in the code. That number 25 should come from the 1st person's $big field. But everything I tried to replace "25" with the data entered in the $big field from the 1st person (the initial query) has failed.

Instead of "id=25" in the code above, I've tried...
id=$myrow["big"]
id=($myrow["big"])
id=$big
id=big
and several other attempts.

So what should I replace 25 to make id = the $big field from the first query result?

🙁

[sid]

I'm not 100% sure I got the problem right, but I assume the query should be something like this:

// query the DB
$sql = "SELECT persontable.firstname AS person_firstname, relativetable.* 
  FROM brotherhood relativetable
  WHERE relativetable.id IN (
    SELECT persontable.big
    FROM brotherhood persontable
    WHERE persontable.id = " . intval($id) . "
  )";
$result = mysql_query($sql);    

$myrow = mysql_fetch_array($result); 

a couple of things I'd like to add:

1. Generally it helps to see a scheme of the database tables

2. Also, naming the fields in a more readable manner should be a good idea. F.ex. 'big' could better be called 'relative_of' (if i got the idea of what you're trying to do).

[gopanthers]

The table looks like this:

[CODE]id | firstname | lastname | more_data | big

1 | Andy | Anderson | red | 37

2 | Bob | Burns | blue | 1

3 | Carl | Cross | square | 42

4 | Daniel | Davidson | round | 14

5 | E[/CODE]

And sorry for the confusion on the word "big". It's actually not a size description, it actually is the term for the relationship... short for "big brother"

So according to this chart (single table) Bob Burns' "big brother" is Andy Anderson.

[djjjozsi]

You should use LEFT JOIN in your query.

select u1.* , u2.firstname AS big_firstname, u2.userid AS big_userid from users u1 LEFT JOIN users u2 ON u1.big=u2.userid

Change the field names to you needs.