SQL to show all entries within a number of categories

Question

Hi, I'm writing an sql query on three tables which finds and displays categories and all the entries within each category

For example

Category 1 post 1 post 2 post 3 post 4

Category 2 post 5 post 6

Category 3 post 7 post 8

etc

I have the categories displaying but can only get one item from each. Can anyone suggest a better approach?

$sql = "SELECT c.CategoryDescription, f.Description, l.FileID, l.CategoryID FROM 
FileCategories c, Files f, FilesLK l WHERE
c.PictureCategoryID IN (58, 59, 60, 61, 62, 63) AND 
c.PictureCategoryID = l.CategoryID AND
f.ID = l.FileID
GROUP BY c.CategoryDescription";

while($row = mysql_fetch_array($result)) {
  $html .= '<h3><a href="#">'.$row['CategoryDescription'].'</a></h3>
  <div>'.$row['Description'].'</div>';
}

Thanks

Answer 1

Sorry, this is the basic layout with some irrelevant fields omitted:

FileCategories
ID | CategoryDescription


Files
ID | Description

FilesLK
FileID | CategoryID

Answer 2

Well your GROUP BY c.CategoryDescription is making sure you only have one post per category. Can't really cut down the rows like that. Why not put some logic in the loop to make it smarter.

$thiscat = '';
while($row = mysql_fetch_array($result)) {
  if($thiscat != $row['CategoryDescription']) {
    $thiscat = $row['CategoryDescription'];
    $html .= '<h3><a href="#">'.$thiscat.'</a></h3>';
  }
  $html .= '<div>'.$row['Description'].'</div>';
}

If you remove the group by from your SQL and change your loop to this it will print the header row only when the category description changes.

Tho personally I would recommend writing it like this:

$thiscat = '';
while(list($cat,$desc,$fileid,$catid) = mysql_fetch_row($result)) {
  if($thiscat != $cat) {
    $thiscat = $cat;
    $html .= "<h3><a href=\"#\">$thiscat</a></h3>";
  }
  $html .= "<div>$desc</div>";
}

Just have to use escaped double quotes or single quotes for tag attributes is all but is a lot neater and easier to read/troubleshoot... IMHO.

HTH :)