SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot Rio's Avatar
    Join Date
    Nov 2001
    Location
    United Kingdom
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query from three tables

    Hello there,

    Can anyone tell me if it's possible to select data from three tables at once in MySQL? And if so, how? I have looked up and down in the sitepoint forums (and few other places), but I can't find any mention of this anywhere.

    Rio

  2. #2
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you looking for JOIN?
    I can't tell you more without more info.

  3. #3
    SitePoint Zealot Rio's Avatar
    Join Date
    Nov 2001
    Location
    United Kingdom
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    I have three tables - articles, regions and organisations - in my db like below. Each row in "articles" corresponds to sigle row in the other two tables, ie. article "Community Multimedia Centre" corresponds to table "regions" at the same region_id, and to table "organisations" at the same org_id.

    Now what I'd like to do is, based on the relationship above, to pull out the columns:
    id, title (from articles), region_name (from regions) and org_name (form organisations) in single query. I think it have to be done in a sigle query because I'm using the resulting array to display the search result in the same page, using while( ) function.

    Will this be possible

    Code:
    articles
    +----+--------------------------------+-----------+--------+                                                                        |
    | id | left(title, 30)                | region_id | org_id |                                                                        +
    +----+--------------------------------+-----------+--------+                                                                        |
    | 18 | Community Multimedia Centres P |      7102 |   1026 |                                                                        |
    | 16 | Pacific Islands Draft Regional |      4004 |      0 |                                                                        |
    | 11 | ITU And C&W Sign MoU For Remot |         0 |   1019 |                                                                        |
    | 13 | OECD Publishes Report Measuri |      1001 |   1022 |                                                                         |
    | 14 | Asia Pacific Dispute Resolutio |      7027 |   1013 |                                                                        |
    |  9 | Joint ITU/WIPO Symposium Exami |      1001 |   1019 |                                                                        |
    | 10 | ITU Telecom Africa Charts Prog |      6046 |   1019 |                                                                        +
    +----+--------------------------------+-----------+--------+
    
    regions
    +-----------+----------------+                                                                                                      |
    | region_id | region_name    |                                                                                                      +
    +-----------+----------------+                                                                                                      |
    |      1000 | Commonwealth   |                                                                                                      |
    |      1001 | Global         |                                                                                                      |
    |      2000 | Africa         |                                                                                                      |
    |      3000 | Americas       |                                                                                                      |
    |      4000 | Asia           |                                                                                                      |
    |      5000 | Europe         |                                                                                                      |
    |      2001 | Africa Central |                                                                                                      +
    +-----------+----------------+
    
    organisations
    +--------+-----------+                                                                                                              |
    | org_id | org_name  |                                                                                                              +
    +--------+-----------+                                                                                                              |
    |   1001 | AusAID    |                                                                                                              |
    |   1002 | Bellanet  |                                                                                                              |
    |   1003 | CIDA      |                                                                                                              |
    |   1004 | CIDIF     |                                                                                                              |
    |   1005 | CTO       |                                                                                                              |
    |   1006 | DFID      |                                                                                                              |
    |   1007 | Dot.Force |                                                                                                              +
    +--------+-----------+

  4. #4
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the query you need to use:
    Code:
    SELECT articles.id,articles.title,regions.region_name,organisations.org_name
    FROM articles
    LEFT JOIN regions ON (articles.region_id=regions.region_id)
    LEFT JOIN organisations ON (articles.org_id=organisations.org_id)
    And here's a colored version of that query, if you want:
    Code:
    SELECT articles.id,articles.title,regions.region_name,organisations.org_name
    FROM articles
    LEFT JOIN regions ON (articles.region_id=regions.region_id)
    LEFT JOIN organisations ON (articles.org_id=organisations.org_id)
    As you can see, we are selectiong what we need from the articles table, then JOINing that table with the regions tables. We take a column in the articles table (region_id) and use it to make a 'link' with another column (region_id) in the regions table.
    (in this case, region_id is the name of both columns, but the column names can be differnet in JOINs)

    Hope I explained it ok.

  5. #5
    SitePoint Zealot Rio's Avatar
    Join Date
    Nov 2001
    Location
    United Kingdom
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Finally JOIN started make sense to me... So this means that I can string together a series of tables by adding table_reference, is it? There's so much more to learn!

    Do you know any good tutorial site for MySQL? It seemms like quite a difficult one to come by.

    Anyway, thanks again, FireFly.

  6. #6
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, sorry, I don't know of any good tutorials for MySQL. They say Kevin's book is good, but I don't know what it primarily focuses on.
    And to be honest, the MySQL docs aren't all that good.
    Last edited by FireFly; Jan 23, 2002 at 10:39.

  7. #7
    SitePoint Zealot Rio's Avatar
    Join Date
    Nov 2001
    Location
    United Kingdom
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Really. Which database are you comparing MySQL with? This is the only db server I know how to use, so it's difficult for me to compare with. I'm interestted to know what kind of short fall you lhave in mind. As far as I can see, a lot of people seems to think that MySQL works very well with PHP.

  8. #8
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops, there'a a missing word there, 'docs'. I wanted to say that MySQL's official docs (http://www.mysql.com/doc/) aren't that great, I didn't say MySQL isn't.

    I really like MySQL and it's pretty much the only one I know.

  9. #9
    SitePoint Zealot pnathan's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am reading the SAMS "PHP and MySQL Web Development" book and it is quite good. There is loads of content in it. I would recomment it to somebody looking to seriously get into PHP and MySQl. Also, get you boss to pay for it, I did
    I have two tickets to the Crows, sweet.

  10. #10
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are into buying books about MySQL, do yourself a huge favor and buy this one:
    http://www.amazon.com/exec/obidos/tg...481805-0088721
    It's awesome.

  11. #11
    SitePoint Zealot pnathan's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, thanks for the recommendation.

    Quick question: At the moment I use MySQL as a data storage and manipulate the data using PHP. I use a few MySQL commands but mainly I do all the work using PHP. Is this how you guys/gals approach building web applications?
    I have two tickets to the Crows, sweet.

  12. #12
    SitePoint Zealot Rio's Avatar
    Join Date
    Nov 2001
    Location
    United Kingdom
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sugar! It's me who missed the word 'doc'.

    I've see both books recommended here in my local book shop. They look very good. It is just a matter of catching my boss when he's in a rare moment of generous mood

    Just for reference, I have Sams "Teach Yourself MySQL in 21 days", which I found quite good (though a bit dry).

    I'm still quite a newby in this field, but I feel like you can save a lot of hussle if you wtite good queries and are fluent with MySQL functions.

  13. #13
    SitePoint Wizard masm50's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    2,508
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    How do you do a LEFT JOIN with PHP?

    Thanks for the help with the LEFT JOIN in MySQL, but I can't really see how to implement that in PHP.

    Here's my example:

    I am trying to select stuff from 4 tables, and here's the code I'm using at the moment:

    ------------------------------------------------------
    $result = mysql_query ("SELECT * FROM reviews
    LEFT JOIN rating ON (reviews.RID=rating.ID)
    LEFT JOIN authors ON (reviews.AID=authors.ID)
    LEFT JOIN license ON (reviews.LID=license.ID)
    WHERE CID = '$CID'
    AND ID = '$ID'
    ");

    if ($row = mysql_fetch_array($result)) {

    do {
    print $row["Title"];
    print ("</P>");

    print $row["rating.Name"];
    print ("</P>");

    print $row["authors.Name"];
    print ("</P>");

    print $row["license.Name"];
    print ("</P>");

    print $row["FileSize"];
    print ("</P>");
    print $row["ReviewText"];
    print ("</P>");
    } while($row = mysql_fetch_array($result));

    } else {print "Sorry, no records were found";}

    -------------------------------------------------------

    I get the error:

    Warning: Supplied argument is not a valid MySQL result resource in c:\program files\apache group\apache\htdocs\reviewdynamic8.php on line 18
    Sorry, no records were found

    when I try to run the script.
    (Line 18 is the one that says:

    if ($row = mysql_fetch_array($result)) {

    What have I done wrong?
    I think it is something to do with how I have tried to call things from the joined table (eg. license.Name), how do you do that?

    Thanks for any help,

    Tim

  14. #14
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First, use this technique instead of the IF then do-while:
    Code:
    $result=mysql_query($query);
    if (mysql_num_rows($result)>0) { 
    	while ($row = mysql_fetch_array($result)) {
    		// do your stuff here
    	}
    } else {
    	// no records were found
    }
    I think it's better.

    So your code now look slike this, right?
    Code:
    if (mysql_num_rows($result)>0) { 
    
    while ($row = mysql_fetch_array($result)) {
    print $row["Title"]; 
    print ("</P>"); 
    
    print $row["rating.Name"]; 
    print ("</P>"); 
    
    print $row["authors.Name"]; 
    print ("</P>"); 
    
    print $row["license.Name"]; 
    print ("</P>"); 
    
    print $row["FileSize"]; 
    print ("</P>"); 
    print $row["ReviewText"]; 
    print ("</P>"); 
    }
    
    } else {print "Sorry, no records were found";}
    I don't think the problem is with your query, but maybe it just doesn't return any rows? Try the new code I gave you and see if you get the error. If you don't get it, make sure that $CID and $ID are valid ID's that return any rows.

  15. #15
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Rio
    Sugar! It's me who missed the word 'doc'.
    No, I just edited my post when I found the mistake.

  16. #16
    SitePoint Wizard masm50's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    2,508
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    It still won't work...

    Firefly,

    Thanks for your help, but the script still won't work, and gets the same errors.

    So I'm guessing it must be the query itself.

    the ID and CID are deffinitely valid, and are passed to the page through a string in the link from the page before.

    Any experts on LEFT JOINS, that could possibly check my syntax?

    Thanks

    Tim


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
  •