SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    Sthlm, Sweden
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting new table in loop

    I have two tables, titles and authors. The title (of a book) is linked to an author id, and my problem is, how to get the php-document to display the authors first and last name (for which I've created columns in author).

    I have the following code, which - obviously - returns an error:

    $query2 = 'SELECT * FROM Titles ORDER BY Ti_Startdate ASC';
    $result2 = mysql_query ($query2);
    while ($row = mysql_fetch_array ($result2, MYSQL_NUM)) {
    echo '<tr><td>', stripslashes($row[3]), '<td>', stripslashes($row[2]),'<td>', stripslashes($row[7]), '<td width=30>&nbsp;';


    So far it works fine and shows the date, the title of the book and some more information, but now I want to select a new table, in order to show the author's first and last name.

    The $id I created to fetch the authors id number from Titles-table, and it seems to work, but how to implement it into the query so that I get the correct row?

    $id = stripslashes($row[1]);
    $table = "SELECT * FROM authors ORDER BY Au_ID ASC LIMIT $id";
    while ($row = mysql_fetch_array ($table, MYSQL_NUM)) {
    echo '<tr><td colspan=4>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;by ', $row[1];
    }
    }

    Any suggestions?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sounds like you are perilously close to attempting to do a query within a loop

    that's a real performance killer

    what you want is one query that does a JOIN
    Code:
    select T.title
         , T.Ti_Startdate
         , A.lastname
         , A.firstname
      from Titles T
    inner
      join Authors A
        on T.author_id = A.Au_ID
    order 
        BY Ti_Startdate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •