Formatting resultset

jakeone

I have the following SQL query (please ignore any wrapping):

$sql = "SELECT candidates.candID, candFName, candLName, candRefNo, lawAreaName FROM candidates
INNER JOIN candidates_lawAreas ON candidates.candID = candidates_lawAreas.candID
INNER JOIN lawAreas ON lawAreas.lawAreaID = candidates_lawAreas.lawAreaID";
$res = mysql_query($sql);
while ($data = mysql_fetch_assoc($res))
{
echo $data['candID']." ".$data['candFName']." ".$data['candLName']." ".$data['candRefNo']." ".$data['lawAreaName']."<br>";
}

This outputs as follows...

90 Sue Spencer 002357 Commercial Property & Land
90 Sue Spencer 002357 Conveyancing
91 Fred Tipu 002358 Personal Injury
92 Larry Scott 002359 Commercial Property & Land
92 Larry Scott 002359 Conveyancing
93 Dave Brotherton 002360 Employment
93 Dave Brotherton 002360 Company Commercial
93 Dave Brotherton 002360 Advocacy

But I want it to output as...

90 Sue Spencer 002357
Commercial Property & Land
Conveyancing

91 Fred Tipu 002358
Personal Injury

92 Larry Scott 002359
Commercial Property & Land
Conveyancing

93 Dave Brotherton 002360
Employment
Company Commercial
Advocacy

It seems simple enough but I'm tying myself into knots...

Jake

thorpe

echo $data['candID']." ".$data['candFName']." ".$data['candLName']."<br />".$data['candRefNo']."<br />".$data['lawAreaName']."<br /><br />";

jakeone

🙂 It's not as straightforward as that because each candidate has one or more lawAreas - it varies.

I was trying to loop through the lawAreas but only display the candidate name etc. once.