multiple relations from same table in one query

[chucklarge]

Hi, I am trying to get the name of user that created a project and the user that modified the project last in one query. I have some example tables

-users-
idname
1chuck
2john
3alex

-projects-
idcreatedmodified
12______3
23______1
32_________2

$result = mysql_query("SELECT * WHERE projects.id=2 AND  users.id=projects.created AND users.id=projects.modified") or die(mysql_error());
$row = mysql_fetch_assoc($result);

In this case what does $row['name'] refer to and how can i reference both the
created name and modified name?

Thanks,
Chuck

[Trs2988]

That query would only work if the user who created it and the user who modified it were the same person. Here's what I would do:

$result = mysql_query("SELECT projects.id as id,u1.id as createduser,u2.id as modifieduser from projects,users as u1,users as u2 WHERE projects.id=2 AND  u1.id=projects.created AND u2.id=projects.modified") or die(mysql_error());
$row = mysql_fetch_assoc($result); 

Then, you'd have $id (the project ID), $createduser (the user ID of the user who created the project) and $modifieduser (the user ID of the user who modified the project. Let me know how that works out.

Edit: Err, those variables would be $row['id'], etc...