Using one variable to look up info in two tables

Question

Hello,

The code below works nicely. Each "title" in the MySQL table "submission" is printed in reverse chronological order alongside its corresponding "loginid."

I have another MySQL table ("login") with the fields "loginid" and "username." So each row has a "loginid" and a "username." The "loginid" in these two MySQL tables "submission" and "login" are equivalent to each other.

In the HTML table printed below, how could I replace the "loginid" with the "username"?

Thanks in advance,

John

  <?php
    $sqlStr = "SELECT loginid, title, url, displayurl
          FROM submission ORDER BY datesubmitted DESC LIMIT 10";
    $result = mysql_query($sqlStr);

    $arr = array(); 
    echo "<table class=\"samplesrec\">";
    while ($row = mysql_fetch_array($result)) { 
        echo '<tr>';
        echo '<td class="sitename1"><a href="http://www.'.$row["url"].'"&gt;'.$row["title"].'&lt;/a&gt;&lt;/td&gt;';
     echo '</tr>';
     echo '<tr>';
     echo '<td class="sitename2"><a href="http://www.'.$row["url"].'"&gt;'.$row["loginid"].'&lt;/a&gt;&lt;/td&gt;';
     echo '</tr>';
     }
    echo "</table>"; 


    ?>

Answer 1

You will have to use a JOIN in your query, like so:

$sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, l.username
             FROM submission AS s,
                  login AS l
            WHERE s.loginid = l.loginid
         ORDER BY s.datesubmitted DESC
            LIMIT 10";

Then just use the $row['username'] field within your code.

bye