Trouble with JOIN when WHERE is present

brrutzo

Hello,

I searched the newbie forums for other posts about JOINS. I found some things that were similar, but they were a bit more advanced then I am.

I am running into difficulty trying to return columns with a JOIN when a WHERE is present.

Is the answer that the where needs to be written as an if/else statement? If so, how would I write it with the Session ID. Or If I wanted to say field "is not null"?

Also, please note that I am playing with files a friend had given me, Creating the
$_SESSION is a bit beyond my grasp at this point, although I know when to use it.


//This works great
$sql = "SELECT * FROM invites WHERE USERID = " . $_SESSION['userid'];

//This works as well
$sql = "SELECT invites.*, users.email, users.lastlogin FROM invites JOIN users ON invites.email = users.email";


//But when I add the WHERE so I see only the records of the person that is logged in, it does not work. 
$sql = "SELECT invites.*, users.email, users.lastlogin FROM invites JOIN users ON invites.email = users.email WHERE USERID = " . $_SESSION['userid'];

Time is a precious commodity, and I truly appreciate yours.

Thank you in advance,
Brrutzo

NogDog

I don't see anything obviously wrong. I'd try adding some debugging to see if it tells us anything about what's going on (I'm assuming MySQL here, so modify as needed for other DBMS):

$sql = "SELECT invites.*, users.email, users.lastlogin FROM invites JOIN users ON invites.email = users.email WHERE USERID = " . $_SESSION['userid'];
$result = mysql_query($sql);
if(!$result)
{
   echo "Query failed.<br />\n$sql<br />\n" . mysql_error();
}
elseif(mysql_num_rows($result) == 0)
{
   echo "No rows returned by query:<br />\n$sql";
}

brrutzo

The error code said that USERID in the WHERE clause was ambigous. This was my fault. It should have read WHERE tablename.colname (not just tablename).

Thank you for the help! And thank you for the mysql_error() function. I will add that to my code going forward.

Thanks again!