SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query for member information and photo

    Currently I have 2 tables in mysql:

    member
    member_photo

    The member_photo has a record for each photo they upload. They can then choose which photo is their primary photo, which is flagged with a field called photomain. Im trying to get the list of all members along with their primary photo but Im using 2 queries which I feel is slowing down the page. Is there a subquery I should be using?

    PHP Code:
     
    $query1 
    $db->Execute("SELECT id,firstname,lastname FROM members ORDER BY lastname ASC");
     
    //loop through the records
    while(!query1->EOF)
    {
    $memberid $query1->fields['id'];
    $firstname $query1->fields['firstname'];
    $lastname $query1->fields['lastnamename'];
    $query2 $db->Execute("SELECT photothumb FROM member_photo WHERE memberid = $memberid AND photomain = 1 LIMIT 1");
     
    $memberphoto $query2->fields['photothumb'];
     
    $query1->MoveNext(); 
    Thanks
    Silly

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT m.id
         , m.firstname
         , m.lastname 
         , mp.photothumb
      FROM members as m
    LEFT OUTER
      JOIN member_photo as mp
        ON mp.memberid = m.id
       AND mp.photomain = 1
    ORDER 
        BY m.lastname 
         , m.firstname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help! It worked perfectly.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •