[RESOLVED] Query Record That Gets Divided into Categories

[dispratt]

I have a script in progress that queries multiple tables. Each client record has various completed jobs that each fall under one of five standard category names. It all works fine and echos the correct results.

But I'm trying to display the jobs under a subhead for its category name.

Acme Corp.
Audits <-- category name
- 2001 Local Audit <-- job name
- 2002 Regional Audit
Reports
- 2007 American Report
- 2008 Canadian Report

And not all categories have a job, so they don't need to display. I'm sure I can use the one query result, instead of making five separate queries... so I'm kinda worried the answer is something obvious I'm overlooking. :queasy:

$query = "SELECT *
                FROM client, job, category
                WHERE client.client_id = $client_id
                AND client.client_id = job.client_id
                AND client.job_id = job.job_id
                AND client.category_id = category.category_id";

$result = mysql_query($query) or die('Error : ' . mysql_error());

while($row = mysql_fetch_assoc($result)) {
   echo $row['client_name'];

  if ($row['category_id'] = '1') { 
  echo 'Audits'; 
  echo $row['job_name']; // just for the jobs in the audits category
  } 
  if ($row['category_id'] = '2') { 
  echo 'Reports'; 
  echo $row['job_name']; // just for the jobs in the reports category
  }
  // etc though all five categories...
}

Thanks!

[ixalmida]

You need to add a sort to your query so that results fall into categories:

$query = "SELECT * 
                FROM client, job, category 
                WHERE client.client_id = $client_id 
                AND client.client_id = job.client_id 
                AND client.job_id = job.job_id 
                AND client.category_id = category.category_id
                ORDER BY category, client, job";

From there, I'd just add a check to see if the previous category equals the current category:

$old_category = "nothing";

while($row = mysql_fetch_assoc($result))
  {
  // Echo category heading only if it is new...
  if($row['category'] != $old_category) echo "<strong>$category</strong><br>";

  // Echo job details...
  echo "&nbsp; ".$row['job']."<br>";

  // Make this category the old_category...
  $old_category = $row['category'];
  }

You can use this same method for client headings, if necessary.