Jump to content


Photo

Connect Two Tables


  • Please log in to reply
2 replies to this topic

#1 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 06 September 2006 - 10:37 PM

I'm currently using this code to display recent members who have signed on:
$sel_mem="select * from sc_member where scm_mem_id<>'1' order by scm_lastlogin desc LIMIT 0,2";

I'd like to modify the above code to show ONLY members with images. To do this, I have to use 'sc_member_images' table, scm_mem_id would be the unique identifier on both tables. So, from results pulled from above code, show ONLY if scm_mem_id exists on table sc_member_images WHERE sci_main=1

I don't know how to incorporate the two into a single SELECT statement and I can't use sub-queries because of current mysql version. Thanks.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 September 2006 - 10:51 PM

This list members where a matching id is found in the images table
$sel_mem = "SELECT m.* FROM sc_member m
    INNER JOIN sc_member_images i
    ON m.scm_mem_id = i.scm_mem_id 
    WHERE m.scm_mem_id<>'1' 
    ORDER BY m.scm_lastlogin DESC

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 MarioRossi

MarioRossi
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 06 September 2006 - 10:59 PM

If the relationship between your images and users table is 1 -> 1 then you can do an INNER JOIN on the tables and only the rows where your "scm_mem_id" values match in both tables will be returned.

Don't forget that as you are dealing with two tables in the 1 query you will have to prefix your table names

This should do it although I haven't checked it.

SELECT * from (sc_member INNER JOIN imagestable ON sc_member.scm_mem_id=imagestable.scm_mem_id) WHERE sc_member.scm_mem_id != '1' ORDER BY sc_member.scm_lastlogin desc LIMIT 0,2

....And barand beat me to it :)
he seems like a knowlegeable chap maybe he can help me with my availability calendar problem ;)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users