SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast judgetules's Avatar
    Join Date
    Dec 2004
    Location
    Newcastle, UK
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    First Post - problem joining tables...

    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 Code:
    <?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.

  2. #2
    SitePoint Zealot soart's Avatar
    Join Date
    Nov 2003
    Location
    Norwich UK
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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:
    Code:
    select updates.title
         , updates.content
         , updates.date
         , author.name 
         , author.email
      from author
    left outer
      join updates  
        on author.id
         = updates.authorid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast judgetules's Avatar
    Join Date
    Dec 2004
    Location
    Newcastle, UK
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    EDIT : Sorted, thank you very much!


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
  •