SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2004
    Sthlm, Sweden
    0 Post(s)
    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
    Toronto, Canada
    63 Post(s)
    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
    select T.title
         , T.Ti_Startdate
         , A.lastname
         , A.firstname
      from Titles T
      join Authors A
        on T.author_id = A.Au_ID
        BY Ti_Startdate | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts