Jump to content


Photo

Help with table joins


  • Please log in to reply
1 reply to this topic

#1 theone

theone
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 09 April 2006 - 02:53 PM

<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$sql = "SELECT * FROM news_site LEFT JOIN staff ON news_site.poster = staff.id WHERE news_site.show = '1' ORDER BY news_site.posted desc LIMIT 10";
$results = mysql_query($sql);
$rowcount = mysql_num_rows($results);
$i = "0";
while ($data = mysql_fetch_array($results)) {
        $i++;
    print "<u>$data[title]</u><br>";
    print "$data[post]<br><br>";
    print DateConvert($data['posted'], "l, jS F Y @ H:i:s");
    if ($i != $rowcount) {
        print "<hr noshade size=1 color=#3366cc>";
    } else {
        unset($i);
    }
}
?>

So thats all good, but what i also want is to be able to fetch the staff.displayname for each news_site.poster, if that makes sense.

Thanks in advance,
Dave
theone

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 April 2006 - 06:58 PM

Well, you're asking for "*" -- all the fields in both tables. This may be troublesome if there are column name collisions. You should explicity refer to the columns you want, and alias them as necessary. In your case, it seems like you be doing:

SELECT news_site.title, news_site.post, news_site.data, staff.displayname.....

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users