First Post - problem joining tables...

[judgetules]

Hello all, pretty new to php and already got a problem :S.

I have a page which shows updates.

Each update shows only the title and the date. The title is a link to open up the update in it's fullest form.

Problem is, I'm having trouble joining all the information to display.

Updates has 5 columns : id, title, content, date and authorid.
Author has 3 columns : id, name, email.

The update in it's fullest form should show: title, content , date, name (with a mailto: link using $email).

I presume i need to join the tables but I can't seem to get it right.

Here is what I have at the minute.

<?php include_once 'header.inc.php';

//show specified update

$id = $_GET['id'];

//Request the update details.

$result = @mysql_query("SELECT updates.id, updates.title, updates.content, updates.date, 

updates.authorid, author.name author.email, author.id  FROM updates LEFT JOIN author ON id=$id 

AND updates.authorid=author.id");
if (!$result)
    {
    exit ('<p>Error performing query: ' . mysql_error() . '</p>');
    }

while ($row = mysql_fetch_array($result))
    {
    $id = $row['updates.id'];
    $title = $row['updates..title'];
    $content = $row['updates.content'];
    $content = ereg_replace("\r","",$content);
    $content = ereg_replace("\n\n","</p><p>",$content);
    $content = ereg_replace("\n","<br />",$content);
    $date = $row['updates.date'];
    $authorid = $row['updates.authorid'];
    $name = $row['author.name'];
    $email = $row['author.name'];
        
    echo "<h4>$title | $date</h4>" .
    "<div class=\"club\"><p>$content</p><p><a href=\"mailto:$email\">$name</a></div>";
    } 

include_once 'footer.inc.php';?>

Here is the error message I keep getting

Error performing query: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '.email, author.id FROM updates LEFT JOIN author ON id=2 AND up

Any help would be appreciated.

[soart]

No guarentte but try

$result = @mysql_query("SELECT updates.* FROM updates LEFT OUTER JOIN author ON author.id=updates.authorid where updates.authorid=author.id and updates.id = '$id'");

Then you can get at the all the contents of updates and author as you need

[r937]

the LEFT OUTER join is what you use if you want all the rows of one table (the left one, i.e. the one mentioned first), together with matching rows, if any, from the right table (second one mentioned)

here, it's unlikely that you would have updates for authors that don't exist, but it seems reasonable to have authors who don't have any updates

therefore author must be the left table in the left outer join:

select updates.title
     , updates.content
     , updates.date
     , author.name 
     , author.email
  from author
left outer
  join updates  
    on author.id
     = updates.authorid

[judgetules]

EDIT : Sorted, thank you very much!