One-to-Many relationship help

[twsnnva]

I would like to query my DB and get an output that displays all of the "Update Comments" for a specific task, under that task. Currently if there is more than one update comment, everything is duplicated, just to display the update comment. Rather then try to explain what I mean here's the code.

~Thomas

<?php
mysql_connect(sqlhost,username,"password");
@mysql_select_db(testdb) or die( "Unable to open database");
$query="SELECT tasks.id, organizations.name AS Client, tasks.name AS 'TaskName',tasks.description AS 'TaskDescription', tasks.comments AS 'TaskComments',updates.comments AS 'UpdateComments', tasks.created AS 'DateCreated',tasks.start_date AS 'DateStarted', tasks.modified AS 'DateModified', tasks.complete_date AS 'DateCompleted' FROM ((tasks LEFT JOIN updates ON tasks.id=updates.item) LEFT JOIN projects ON tasks.project=projects.id) LEFT JOIN organizations ON projects.organization=organizations.id WHERE (tasks.complete_date) Between '$S_SDATE' And '$S_EDATE' AND organizations.name like '$S_ORGSEL' ORDER BY tasks.name";
$result=mysql_query($query);
$num_rows=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num_rows) {
   $TASK=mysql_result($result,$i,"TaskName");
   $CLIENT=mysql_result($result,$i,"Client");
   $T_DESCRIPTION=mysql_result($result,$i,"TaskDescription");
   $T_COMMENTS=mysql_result($result,$i,"TaskComments");
   $U_COMMENTS=mysql_result($result,$i,"UpdateComments");
   $DATE_CREATED=mysql_result($result,$i,"DateCreated");
   $DATE_STARTED=mysql_result($result,$i,"DateStarted");
   $DATE_MODIFIED=mysql_result($result,$i,"DateModified");
   $DATE_COMPLETED=mysql_result($result,$i,"DateCompleted");

   echo "<b>$CLIENT</b><br>Task: $TASK<br>Description: $T_DESCRIPTION<br>Task Comments: $T_COMMENTS<br>Update Comments: $U_COMMENTS  <br>Date Created: $DATE_CREATED<br>Date Started: $DATE_STARTED<br>Date Modified: $DATE_MODIFIED<br>Date Completed: $DATE_COMPLETED<br><br> <br>";
   $i++;
}
?>

Here's that output I get with this code:
The only difference is the Update Comments.

Test Client
Task: Do some stuff
Description: This is a description
Task Comments: This is a task
Update Comments: This is update 1
Date Created: 2005-02-01 14:24
Date Started: 2005-01-31
Date Modified: 2005-02-03 15:59
Date Completed: 2005-02-01


Test Client
Task: Do some stuff
Description: This is a description
Task Comments: This is a task
Update Comments: This is update 2
Date Created: 2005-02-01 14:24
Date Started: 2005-01-31
Date Modified: 2005-02-03 15:59
Date Completed: 2005-02-01


Test Client
Task: Do some stuff
Description: This is a description
Task Comments: This is a task
Update Comments: This is update 3
Date Created: 2005-02-01 14:24
Date Started: 2005-01-31
Date Modified: 2005-02-03 15:59
Date Completed: 200

I would like to end up with something like this:

Test Client
Task: Do some stuff
Description: This is a description
Task Comments: This is a task
Update Comments: This is update 1
Update Comments: This is update 2
Update Comments: This is update 3
Date Created: 2005-02-01 14:24
Date Started: 2005-01-31
Date Modified: 2005-02-03 15:59
Date Completed: 2005-02-01

[eoghain]

This is a very common pattern when dealing with database queries
using multiple tables. The solution is to set a placeholder to let you
know when you are dealing with a new record, or just extra data
from a secondary table.

$currTask = "";
while ($i < $num_rows) {
   $TASK=mysql_result($result,$i,"TaskName");
   $CLIENT=mysql_result($result,$i,"Client");
   $T_DESCRIPTION=mysql_result($result,$i,"TaskDescription");
   $T_COMMENTS=mysql_result($result,$i,"TaskComments");
   $U_COMMENTS=mysql_result($result,$i,"UpdateComments");
   $DATE_CREATED=mysql_result($result,$i,"DateCreated");
   $DATE_STARTED=mysql_result($result,$i,"DateStarted");
   $DATE_MODIFIED=mysql_result($result,$i,"DateModified");
   $DATE_COMPLETED=mysql_result($result,$i,"DateCompleted");

   if ($currTask != $TASK)
   {
      $currTask = $TASK;
      echo "<b>$CLIENT</b><br>"
          ."Task: $TASK<br>"
          ."Description: $T_DESCRIPTION<br>"
          ."Task Comments: $T_COMMENTS<br>"
          ."Date Created: $DATE_CREATED<br>"
          ."Date Started: $DATE_STARTED<br>"
          ."Date Modified: $DATE_MODIFIED<br>"
          ."Date Completed: $DATE_COMPLETED<br><br> <br>";
   }
   echo "Update Comments: $U_COMMENTS  <br>"
   $i++;
} 

[twsnnva]

eoghain,
Thanks alot.