SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,438
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)

    Sorting by columns in joined tables

    PHP Code:
    $result mysql_query("SELECT 
    individual_ID,
    forename,
    surname,
    maiden,
    gender,
    dob,
    dob_place,
    doc,
    doc_place,
    dod,
    dod_place,
    burial,
    burial_place,
    portrate,
    location,
    grave_ID,
    gravestone_ID,
    stone,
    image
    FROM individual
    LEFT JOIN location
    ON ( individual.dob_place = location.location_ID )
    LEFT JOIN grave
    ON ( individual.individual_ID = grave.individual_association  )
    LEFT JOIN gravestone
    ON ( grave.grave = gravestone.gravestone_ID )
    WHERE individual_ID = '4'
    "
    ); 
    Output from above query:
    individual_ID|forename|surname|maiden|gender|dob|dob_place|doc|doc_place|dod|dod_place|burial|burial_place|portrate|location|grave_ID|gravestone_ID|stone|image

    4|William|Jacklin|Unknown|M|1846-00-00|8|0000-00-00|1|1906-10-14|7|1|7|man|Kneesworth-Cambridge-England|1|1|In Loving Remembrence of|Gravestones_003

    I have a table with locations in as the locations will be used multiple times:
    PHP Code:
    CREATE TABLE `location` (
      `
    location_IDmediumint(6NOT NULL auto_increment COMMENT 'Location ID number',
      `
    locationvarchar(80NOT NULL default 'Unknown' COMMENT 'Location',
      
    PRIMARY KEY  (`location_ID`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=
    The grave table is also a lookup table for the gravestones table and that works OK.

    Problem 1 is the location and as you can see the dob_place = 8 doc_place = 1 dod_place = 7 & burial_place = 7
    How can I get the "location" from the location table relating to each of the dob etc?

    Problem 2 is I am creating a timeline and I need to select dob, doc, dod and an event from an event table and order them all by their dates.
    I could save all these items into another table and sort them by date when I retreave the data from the table but was wondering if there was another method to save creating another table.

    PHP Code:
    $result mysql_query("SELECT 
    individual_ID,
    forename,
    surname,
    dob,
    dod,
    event,
    date
    FROM individual
    JOIN events
    ON ( individual.individual_ID = events.event_ID  )
    ORDER BY dob ASC, dod ASC, date ASC
    "
    ); 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Rubble View Post
    How can I get the "location" from the location table relating to each of the dob etc?
    by joining to the location table as many times as you need...
    Code:
    SELECT individual_ID
         , forename
         , surname
         , maiden
         , gender
         , dob
         , dob_place
         , dob_loc.location AS dob_location
         , doc
         , doc_place
         , doc_loc.location AS doc_location
         , dod
         , dod_place
         , dod_loc.location AS dod_location
         , burial
         , burial_place
         , bur_loc.location AS burial_location
         , portrate
         , grave_ID
         , gravestone_ID
         , stone
         , image
      FROM individual
    LEFT OUTER
      JOIN location AS dob_loc
        ON dob_loc.location_ID = individual.dob_place
    LEFT OUTER
      JOIN location AS doc_loc
        ON doc_loc.location_ID = individual.doc_place
    LEFT OUTER
      JOIN location AS dod_loc
        ON dod_loc.location_ID = individual.dod_place
    LEFT OUTER
      JOIN location AS bur_loc
        ON bur_loc.location_ID = individual.burial_place
    LEFT OUTER
      JOIN grave
        ON grave.individual_association = individual.individual_ID
    LEFT OUTER
      JOIN gravestone
        ON gravestone.gravestone_ID = grave.grave
     WHERE individual_ID = 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,438
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    Thanks for the quick reply; visitors arriving soon so I have to go and be social !

    This is what I have so far as a scrren shot:
    http://www.rubble.info/donotdelete/tree_page.jpg

    Did a little "bodge" for the spouse section! I should be able to rewrite this part now you have given me some more info.

    PHP Code:
    // Marriage section
    $index $row['female_ID'];
    $result_m mysql_query("SELECT forename, surname, maiden FROM individual WHERE individual_ID = $index ");
    $row_m mysql_fetch_array$result_m );
    echo 
    "<tr><td colspan=\"2\">
    <b>Spouse</b> "
    .$row_m['forename']." ".$row_m['surname']." ( Maiden name: ".$row_m['maiden'].")<br>
    <b>Married date:</b> "
    .format_date$row['m_date'] )."<br>
    <b>Married place:</b> "
    .format_location$row['location'])."<br>
    </td></tr>"



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
  •