SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    My first join.. please give a pointer !

    I recognise that this is a pretty basic question and I have looked through the forums to find a close match to this, but I hope I will be able to struggle through if someone can give me an initial pointer...

    Scenario - simply, each 'person' record can have up to 6 'projects', each project code held in a different field. I just need to be able to display the project descriptor from the project table, and I really can't grasp the join syntax.

    Even battling with one project code defeats me - here is the instruction I have tried so far:
    -----------------
    $query = "SELECT * FROM person AS tp
    LEFT JOIN project AS pr
    ON pr.code = tp.proj1 // I would also need to match on proj2 etc
    WHERE person_id='$id'";

    $result = mysql_query($query);
    if( !$result ){
    echo mysql_error() . ": " . mysql_errno();
    }
    ------------------
    I then write a form which does things like:
    ------------------
    person's name?php echo $person["tp.name"]; ?>
    projcode?php echo $person["tp.proj1"]; ?> desc?php echo $person["pr.proj1"]; ?>

    The result is that the MySQL does not complain, but just shows pages with all fields blank. And yes, the 'person' record does exist and shows fine if I do just the simple SELECT for id query.

    Would much appreciate assistance on the basics of this and then I will not nag people for more!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    this part is of concern --

    SELECT * FROM person AS tp
    LEFT JOIN project AS pr
    ON pr.code = tp.proj1 // I would also need to match on proj2 etc

    you may want to split off the 6 project fields into a separate table

    it will make for much, much easier sql in the future
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks - but given that it is a single 'person' that holds the six project fields, I'm not clear how that would simplify things - I don't see how I could 'normalise' it any further? But then, it is 5am at the moment after an all-night session trying to understand this stuff....

    Also, I'm wondering why the query at its simplest (trying to match on only one of those projects) fails to show anything..

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    regarding the redesign, i can walk you through it

    regarding your simple query, i can't tell why it isn't returning anything, it's gotta be a data problem

    please show a few sample rows of data from each table

    do you know how to export the table structure and data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very many thanks for your patience and help! Here is the structure (I simplified fieldnames a bit in my posting):
    -----------
    CREATE TABLE tyn_people2 (
    people_id int(9) NOT NULL auto_increment,
    tyn_id varchar(30) NOT NULL default '',
    surname varchar(30) NOT NULL default '',
    forename varchar(50) NOT NULL default '',
    title varchar(30) NOT NULL default '',
    citation varchar(30) NOT NULL default '',
    role text NOT NULL,
    position varchar(255) NOT NULL default '',
    institution varchar(255) NOT NULL default '',
    inst2 varchar(50) NOT NULL default '',
    research text NOT NULL,
    add1 varchar(60) NOT NULL default '',
    add2 varchar(60) NOT NULL default '',
    add3 varchar(60) NOT NULL default '',
    city varchar(60) NOT NULL default '',
    county varchar(60) NOT NULL default '',
    pcode varchar(20) NOT NULL default '',
    country varchar(30) NOT NULL default '',
    email varchar(40) NOT NULL default '',
    hphone varchar(24) NOT NULL default '',
    wphone varchar(24) NOT NULL default '',
    wextension varchar(12) NOT NULL default '',
    mphone varchar(24) NOT NULL default '',
    fax varchar(20) NOT NULL default '',
    webaddr varchar(60) NOT NULL default '',
    proj1 varchar(5) NOT NULL default '',
    proj2 varchar(5) NOT NULL default '',
    proj3 varchar(5) NOT NULL default '',
    proj4 varchar(5) NOT NULL default '',
    proj5 varchar(5) NOT NULL default '',
    proj6 varchar(5) NOT NULL default '',
    entrydate timestamp(14) NOT NULL,
    amended timestamp(14) NOT NULL default '00000000000000',
    expired int(1) NOT NULL default '0',
    nopublish int(1) NOT NULL default '0',
    PRIMARY KEY (people_id)
    ) TYPE=MyISAM;
    Last edited by ndaisley; Jan 28, 2005 at 02:02.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sample data from both this and the other table?

    just the key fields and the fields being joined on will suffice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the project table:
    ------------------------------
    CREATE TABLE tyn_project (
    project_id int(9) NOT NULL auto_increment,
    code varchar(6) NOT NULL default '',
    theme char(1) NOT NULL default '',
    complete char(1) NOT NULL default '',
    title varchar(255) NOT NULL default '',
    subtitle varchar(255) NOT NULL default '',
    ident varchar(40) NOT NULL default '',
    html_linkp varchar(255) NOT NULL default '',
    summary_link varchar(255) NOT NULL default '',
    fact_link varchar(255) NOT NULL default '',
    created timestamp(14) NOT NULL,
    PRIMARY KEY (project_id)
    ) TYPE=MyISAM;

  8. #8
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, had to 'anonymise' the data or I'll be in trouble - this is the 'project' file
    --------------------------

    "1","it1_3","1","y","A blueprint ..........","Evaluation of approaches .........","AbelA","/staff/abel_a.shtml","/research/summary13.shtml","/publications/factsheet3.shtml","20050105112233"
    "2","it1_19","1","y","Modelling technology.........","Technology and the economy............","BertieB","/staff/bertie_b.shtml","/research/summary9.shtml","/publications/factsheet19.shtml","20050105112233"
    "3","efp/03","1","y","change scenarios.........",,"CainC","/staff/cain_c.shtml","/research/summary03.shtml","/publications/factsheet03.shtml","20050105112233"
    Last edited by ndaisley; Jan 29, 2005 at 06:10.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    how about showing a few sample rows of only the columns of data that are involved in the join

    specifically, pr.code and tp.proj1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here we are at length - some data from the 'people' table.
    ---------------------
    "3";"CainC";"Cain";"Charlie";"Dr";"Cain C.";;"Research Fellow";"Policy Studies";;;"Policy Studies";"100 Park Road";;"London";;"NW1";"UK";;;;;;;"/general/cainc.shtml";1;7;25;;;;"20041212000000";"20041212000000";"0";"0"

    "4";"AbelA";"Abel";"Alex";"Dr";"Abel A.";;;"Science Labs";;;"Science Labs";"Viscount St";;"London";;"WC1";"UK";"abel@leicester.ac.uk";;;;;;"/general/abela.shtml";1;2;6;;;;"20041212000000";"20041212000000";"0";"0"

    "5";"AldridgeA";"Aldridge";"Alan";"Mr";"Aldridge A";;;"The Laboratory";;;"The Laboratory";"Sedrup";"Oxford";"Oxon";;"OX2";"UK";"a.aldridge@aol.com";;;;;;"/general/aldridgea.shtml";3;7;26;;;;"20041212000000";"20041212000000";"0";"0"
    Last edited by ndaisley; Jan 29, 2005 at 06:10.

  11. #11
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Oxford
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, tp.proj1, tp.proj2, tp.proj3, tp.proj4, tp.proj5, tp.proj6 from 'people' might hold simple values like:

    1;7;25;32;
    2;4;9
    5;9;
    3;6;21;22;23;32

    pr.code from 'project' is a key that simply looks like:

    1
    2
    3
    4
    5
    Last edited by ndaisley; Jan 29, 2005 at 06:11.


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
  •