SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    canada
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query doesn't return row if one colum is empty

    beleow is the code for a query of all the enties in my photo cms.
    i have 4 rows (entry, category, author, location) the entrry holds a number in the Entry_Location_ID colum that relates to the id in the location table. Everything works but if i don't set the entry location id to an existing location id, that entry is not shown. Same thing with the category which is setup pretty much the same. If i remove
    PHP Code:
     WHERE category.Cat_ID entry.Entry_Cat_ID AND
        
    entry.Entry_Location_ID location.Location_ID 
    i get multiple repeats in my results.

    Any ideas?
    thank you
    scott
    PHP Code:
    $result = @mysql_query("SELECT *,
          DATE_FORMAT(Entry_Date,'%b %d/%y') AS readable_date FROM
          entry, category, author, location
        WHERE category.Cat_ID = entry.Entry_Cat_ID AND
        entry.Entry_Location_ID = location.Location_ID
        ORDER BY entry.Entry_Date DESC"
    );
        if (!
    $result) {
            die(
    'Error performing query: ' mysql_error());
            };
      echo (
    '<table border="0" cellspacing="0" cellpadding="0">
      <tr>
            <td width="60px"> </td>
          <td width="70px"><b>Date</b></td>
        <td width="140px"><b>Title</b></td>
        <td width="100px"><b>Category</b></td>
      </tr>
      '
    );
        while ( 
    $row mysql_fetch_array($result) ) {
        if (
    $i 2) {$rowcol=2;}
            else {
    $rowcol=1;}
          
    $id=$row['Entry_ID'];
        
    $title=substr($row['Entry_Title'], 0,20);
        
    $category=substr($row['Cat_Title'], 0,15);
        
    $date=$row['Entry_Date'];
        
    $status=$row['Entry_Status'];
        
    $location=substr($row['Location_Title'], 0,15);
            
    $short_date=$row['readable_date'];
        
    $folder=$row['Entry_Folder'];
        
    $filename=$row['Entry_Filename'];
        if (
    $status=="0") {$status="draft";}
            if (
    $status=="1") {$status="live";}
          echo (
    '<tr class="row'.$rowcol.'">
            <td valign="top">
            <a href="entry_edit.php?id='
    .$id.'">
            <img src="../photos/'
    .$folder.'/'.$filename.'-thumb.jpg" width="60" height="45" alt="" border=0></a></td>
        <td valign="top">'
    .$short_date.'<br />'.
        
    $status.'</td>
        <td valign="top"><a href="entry_edit.php?id='
    .$id.'">'.$title.'</a></td>
          <td valign="top">'
    .$category.'<br />'.
        
    $location.'</td>
          </tr>'
    );
        
    $i++;
        };
    echo (
    '</table>'); 

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    canada
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    figured it out

    Figured it out. I thought it might have something to do with left joins because
    PHP Code:
        WHERE category.Cat_ID entry.Entry_Cat_ID AND
        
    entry.Entry_Location_ID location.Location_ID 
    was making relation ships only with those that had values
    below is the solution for anyone else that might ever need it
    PHP Code:
        $result = @mysql_query("SELECT DISTINCT *,
          DATE_FORMAT(Entry_Date,'%b %d/%y') AS readable_date FROM
          entry LEFT JOIN category on category.Cat_ID = entry.Entry_Cat_ID
        LEFT JOIN location on entry.Entry_Location_ID = location.Location_ID, author
           ORDER BY entry.Entry_Date DESC"
    ); 


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
  •