Feild Lables

[dcjones]

Hi all.

I have a table which shows all the data correctly. No problem there.

The table has the following structure:

feild 1 = ID
feild 2 = staff_name
feild 3 = staff_position
feild 4 = staff_location
feild 5 = depart_id

example:

ID = 1
staff_name = fred smith
staff_position = manager
staff_location = ley
depart_id = 1

ID = 2
staff_name = john doe
staff_position = manager
staff_location = ley
depart_id = 1

ID = 3
staff_name = les jones
staff_position = manager
staff_location = ley
depart_id = 1

When the data is displayed it shows the "staff_position" for each member of staff.

What I want to do is to show the "staff_position" ONCE for all the staff members with the same position.

Anyone have any ideas.

The code I am using to display the record is this:

$maxRows_ley_staff = 70;
$pageNum_ley_staff = 0;
if (isset($_GET['pageNum_ley_staff'])) {
  $pageNum_ley_staff = $_GET['pageNum_ley_staff'];
}
$startRow_ley_staff = $pageNum_ley_staff * $maxRows_ley_staff;

mysql_select_db($database_ley_community, $ley_community);
$query_ley_staff = "SELECT * FROM ley_staff WHERE depart_ID = '7' ORDER BY id ASC";
$query_limit_ley_staff = sprintf("%s LIMIT %d, %d", $query_ley_staff, $startRow_ley_staff, $maxRows_ley_staff);
$ley_staff = mysql_query($query_limit_ley_staff, $ley_community) or die(mysql_error());
$row_ley_staff = mysql_fetch_assoc($ley_staff);

if (isset($_GET['totalRows_ley_staff'])) {
  $totalRows_ley_staff = $_GET['totalRows_ley_staff'];
} else {
  $all_ley_staff = mysql_query($query_ley_staff);
  $totalRows_ley_staff = mysql_num_rows($all_ley_staff);
}
$totalPages_ley_staff = ceil($totalRows_ley_staff/$maxRows_ley_staff)-1;

Thanks in advance

[SD-Syndicate]

Hi dcjones,

Im not sure if this is what your after or even if theres a simpler way, but what I would do is create a separate recordset for each staff_position, like:

"SELECT * FROM ley_staff WHERE staff_position = 'manager' ORDER BY id ASC"

And

"SELECT * FROM ley_staff WHERE staff_position = 'cleaner' ORDER BY id ASC"

Etc...

Then use tables to display each recordset as so.

| Managers | Location | Staff ID |
|Fred Smith | Ley | 1 |
|John Doe | Ley | 1 |

|Cleaners | Location | Staff ID |
| Joe Bloggs | Newcastle | 2 |

Etc... Hope this helps

[dcjones]

Hi SD-Syndicate

Thanks for your reply, I will give that a go. Good idea.

I will let you know.

[bubblenut]

Or taking the problem from another angle

$maxRows_ley_staff  = 70;
$pageNum_ley_staff  = isset($_GET['pageNum_ley_staff']) ? $_GET['pageNum_ley_staff'] : 0;
$startRow_ley_staff = $pageNum_ley_staff * $maxRows_ley_staff;

mysql_select_db($database_ley_community, $ley_community);
$sql = "SELECT * FROM ley_staff ORDER BY staff_position ASC, id ASC LIMIT $startRow_ley_staff, $maxRows_ley_staff";
$res = mysql_query($sql, $ley_community) or die("SQL Error: ".$sql."<br />\n".mysql_error());
$current_position='';
while($row = mysql_fetch_assoc($res)) {
	$output[$row['staff_position']][]=$row;
	if($current_position!=$row['staff_position']) {
    echo("<br />\n<h2>Position: ".$row['staff_position']."</h2><br />\n");
		$current_position=$row['staff_position'];
	}
	echo("Staff Name: ".$row['staff_name']."<br />\nStaff Location: ".$row['staff_location']."<br />\n");
}

It echoes out the Job Title only when it's different to the last. Or you can use the associative array that's built and put into $output;

HTH
Bubble

[dcjones]

Hi bubblenut,

Your code works great. One last question if I may.

I have the following code to display the results. What is the best way of using your code and keeping the layout of the page the same.

If you could help, thats

My current layout code:

 do { 

  <tr class="sixstagesBG">
      <td class="bodytextbold">

[php echo $row_ley_staff['staff_position']; [/code]</td>
<td>&nbsp;</td>
<td colspan="3" class="bodytext">

 echo $row_ley_staff['staff_name']; ?></td>

</tr>
<tr class="sixstagesBG">
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>

 } while ($row_ley_staff = mysql_fetch_assoc($ley_staff)); 

[bubblenut]

Now, I think you can do this bit yourself :rolleyes:
Look at my code, find the start of the while loop. Try and break it down into two parts, the bit which decides whether or not to display the staff position and then does if it's needed; and the bit which displays the other fields (HINT: you can ignore the line $output[$row['staff_position']][]=$row😉. Once you have figured out which bit does what it should be a fairly simple task to place it around your code.

Good Luck
Bubble

[dcjones]

Hi Bubblenut,

Your right, it's fair comment.

I will work on it.

I must thank you agin.