help with join mysql - php

Hi, I have two tables and I need to get the
information for both to display a picture and
member data.

-----
members
memmberId (primary key), fname
memberImg
ID (primary key), filename,memID

This query
SELECT memberImg.filename,members.fname
FROM memberImg,members
where memberImg.ID = 2;

returns 60 records, I am trying to get 1 where
the ID = 2 or myVar.
the memID filed gets updated when the user
adds the picture. The memeberImg.memID matches the members.memberID.

Can someone please help me with this join.

Thanks,
~cherylw

Hi Cheryl,

Looks like your query is not tying the two tables together based on the relationship between members.memberID and memberImg.memID.

Try:

SELECT memberImg.filename,members.fname
FROM memberImg,members
where memberImg.ID = 2 AND
members.memberId = memberImg.memID;

Hope this helps,

thanks for your reply on this I am really desperate once again. I am not getting any data with that query, but it looks like the right approach. Let me explain. I need to match the memberImg.memId with members.memberID where
memberImg.memID = '$currID'
here's what I tried...

$sql_result = mysql_query("SELECT memberImg.filename,memberImg.ID,*
FROM memberImg,members where memberImg.memID = '$currID' AND members.memberId = memberImg.memID;", $db);


thanks and I really could use the help I am supposed to have this directory by the morning est.

~cherylw

update.. this works now... after I my last post it made more sense to me;

$sql_result = mysql_query("SELECT *, memberImg.filename,memberImg.ID
FROM members,memberImg where memberImg.memID = '$currID' AND members.memberId = memberImg.memID;", $db);


Thanks for everyones help,
~cheryl

Hi Cheryl,

Glad that worked out for you. If it matters to you, I know with Oracle, the WHERE condition is processed from last to first. I'm not sure if mySQL is the same, but if it is, you might get a performance boost by rearranging the WHERE condition as:

$sql_result = mysql_query("SELECT *, memberImg.filename,memberImg.ID
FROM members,memberImg where members.memberId = memberImg.memID AND memberImg.memID = '$currID' ;", $db);

This way, the first slice is to narrow down the memberImg records to only those that match the memID of $currID, and then perform the join to members with that smaller subset.

Probably negligable, but with very large recordsets, it might help.

Good luck!