SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting information from two tables

    Here is some kind of a complex question I have for you. I have two tables with information in it. One of the tables in an article table with information about articles written. The other table has authors. In the articles table I have an Author ID (AuthID) column. This column is the number of the ID in the Author table. I want to be able the get the Author's Name which is held in the Author table by using the AuthID.

    Can you help me?
    --
    sethtrain

  2. #2
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Execute a join query such as "SELECT * FROM articles INNER JOIN authors USING (authid)", assuming authid is the name used in both tables.

  3. #3
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the Article Table it is AuthID. In the Authors table it is ID
    --
    sethtrain

  4. #4
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sethtrain
    In the Article Table it is AuthID. In the Authors table it is ID
    In that case, "SELECT * FROM articles INNER JOIN authors ON articles.authid=authors.id" should work.

  5. #5
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok here is the situation i need all the information from the article table and the authors name from the Author table. I have this so far:

    PHP Code:
    $query "SELECT * FROM Articles ORDER BY id Desc Limit 1";
            
    $result mysql_query($query);
            
            while(
    $row mysql_fetch_array($result))
            {
            echo 
    "<h2>" $row['ArtTitle'] . "</h2>";
            echo 
    "<h3 class=\"byline\">by <a href=\"/author/" .$row['Folder']. "/\">" $row['Name'] . "</a></h3>";
            echo 
    "<p>" .$row['ArtDescription']. "</p>";
        
            }
            
    ?> 
    Now where I have $row['Name'] I want to display the authors name from the authors table. How would I use your sql statement and the one I already have to get this informaiton?
    --
    sethtrain

  6. #6
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sethtrain
    Now where I have $row['Name'] I want to display the authors name from the authors table. How would I use your sql statement and the one I already have to get this informaiton?
    Your PHP code should do the trick. I would, however, replace the asterisk in my sql statement with the names of the fields that you will actually be using on the page, separated by commas. e.g. "SELECT articles.id AS articleid, articletext, name FROM articles INNER JOIN authors ON articles.authid=authors.id".

    This will save in the query execution time because it won't have to load unneeded fields, and thus, lower the loading time for the page. Also, it is a good idea to define all of your fields like this when making queries involving multiple tables, so as to avoid problems that could arise if fields across different tables have the same name.

    In the example, I had no need to make special definitions for articletext and name, since I assumed that they are exclusive to the articles and authors tables, respectively. articles.id, however, had to be definied because there is a field named 'id' in both tables. I truncated articles.id to 'articleid' using the 'AS' statement, in order to distinguish it from authors.id.

  7. #7
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how will I used the authID to get the author's name from the author's table?
    --
    sethtrain

  8. #8
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sethtrain
    how will I used the authID to get the author's name from the author's table?
    Unless you need to use authid for something like a URL to an author bio page, you don't need to include it in the first part of the query. The INNER JOIN will execute, regardless of whether or not you invoke authid or authors.id in the SELECT portion of the query.

  9. #9
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what would i do if I wanted to get two columns from the author table?
    --
    sethtrain

  10. #10
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am getting this error:

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home2/seth03/public_html/seth/index.php on line 41

    Here is my code:

    PHP Code:
    <?
            $db 
    mysql_connect("localhost""***""***");
            if (!
    $db)
            {
                echo 
    "Error: Could not connect to database. Please try again later.";
                exit;
            }
            
    mysql_select_db("***");
            
    $query "SELECT ID as ArticleID, ArtTitle, ArtDescription FROM Articles INNER JOIN Authors ON ArticleID=Authors.ID ORDER BY ArticleID Desc Limit 1";
            
    $result mysql_query($query);
            
            while(
    $row mysql_fetch_array($result))
            {
            echo 
    "<h2>" $row['ArtTitle'] . "</h2>";
            echo 
    "<h3 class=\"byline\">by <a href=\"/articles/" .$row['Folder']. "/\">" $row['Name'] . "</a></h3>";
            echo 
    "<p>" .$row['ArtDescription']. "</p>";
        
            }
            
    ?>
    Somehow it isn't working
    --
    sethtrain

  11. #11
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the SELECT portion of your SQL statement, you must specify that ID is coming from the articles table, so replace "SELECT ID as ArticleID" with "SELECT articles.ID as ArticleID".

  12. #12
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sethtrain
    what would i do if I wanted to get two columns from the author table?
    Do you mean what you would do if a single article had multiple authors?

  13. #13
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    i am running that select statement that you have supplied and it isn't working. i don't know what to do
    --
    sethtrain

  14. #14
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sethtrain
    i am running that select statement that you have supplied and it isn't working. i don't know what to do
    If you have phpmyadmin or a similar MySQL management program, try running that SQL statement on your DB, and see if anything is returned.

  15. #15
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SQL-query :

    SELECT articles.ID AS ArticleID, ArtTitle, ArtDescription
    FROM Articles
    INNER JOIN Authors ON Articles.AuthID = Authors.id
    LIMIT 0 , 30

    MySQL said:


    Unknown table 'articles' in field list
    --
    sethtrain

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sethtrain
    Unknown table 'articles' in field list
    i believe it is case sensitive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I got it to work. I am not getting the Authors name though. I'm not getting any information from the Authors table
    --
    sethtrain

  18. #18
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sethtrain
    SQL-query :

    SELECT articles.ID AS ArticleID, ArtTitle, ArtDescription
    FROM Articles
    INNER JOIN Authors ON Articles.AuthID = Authors.id
    LIMIT 0 , 30

    MySQL said:


    Unknown table 'articles' in field list
    It is generally a good idea to keep table names and fields completely in lowercase letters, to simplify things so that case-sensitivity problems are avoided. Edit the names of each so that they are all in lowercases, and then try running the query again, with the approriate case changes in the SQL statement as well.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please show latest query, also list all columns in your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sethtrain
    Okay, I got it to work. I am not getting the Authors name though. I'm not getting any information from the Authors table
    Remember to include the fieldname for 'name' in the select portion of the SQL statement.

  21. #21
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay, all table names are set to lowercase.

    My field names are ID, ArtTitle, ArtText, ArtDescription, ArtCategory and AuthID for the articles table.

    My field names for the authors table are ID, Name, Hometown, and Interests.

    Should the column names be lowercase?
    --
    sethtrain

  22. #22
    SitePoint Zealot The Big Show's Avatar
    Join Date
    Nov 2002
    Location
    USA
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sethtrain
    Should the column names be lowercase?
    It would help simplify things.

  23. #23
    SitePoint Zealot sethtrain's Avatar
    Join Date
    Dec 2003
    Location
    Paducah, KY
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok that is done...

    now i forgot the folder column that is in my authors table. Should I use that select statement now?
    --
    sethtrain


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
  •