Grouping output using data from one field in the result

[somethingnew]

Query result:

+--------+-----------+--------------+-----------+
| rol_id | pos_name  | role_name    | sys_name  |
+--------+-----------+--------------+-----------+
|      2 | A Manager | Role A       | System A  |
|      1 | A Manager | Role B       | System A  |
|    105 | A Manager | Role A       | System B  |
|    106 | A Manager | Role B       | System B  |
|    107 | A Manager | Role C       | System B  |
|    108 | A Manager | Role D       | System B  |
|      4 | A Manager | Role A       | System C  |
|     25 | A Manager | Role A       | System C  |
|    100 | A Manager | Role A       | System C  |

Required output:

Position Name “A Manager” can access

• “System A”
  • “Role A”
  • “Role B”
• “System B”
  • “Role A”
  • “Role B”
  • “Role C”
  • “Role D”

etc.

Current code, using a for loop to grab the system names:

$posname = mysqli_fetch_array($posresult);
echo $posname['pos_name']. '<br/>';
echo 'A total of ' .$result->num_rows. ' rows were returned<br/>';
$numResults = $result->num_rows;

$all_results = array();

while ($row = mysqli_fetch_assoc($result)) {
    // Append all rows to an array
    $all_results[] = $row;
}

for($i = 0; $i < $numResults; $i++){
    echo $all_results[$i]["sys_name"];
    echo '<br/>';
}

I was intending to learn nested loops next, to put the roles under each system.

But the above lists each system for every row in the array (expected behaviour with the current code), how could I group the output so it looks as above, with each system listed once and each role associated with it printed beneath it?

[phdr]

When you fetch the data into $all_results, pivot/index it using the sys_name column -

$all_results[$row['sys_name']][] = $row;

You can then use two nested foreach(){} loops to produce the output.

[somethingnew]

Thank you! I’ve come up with this but now getting every role under each system

I will try your suggestion

$posname = mysqli_fetch_array($posresult);
echo $posname[‘pos_name’]. ‘
’;
echo ‘A total of ’ .$result->num_rows. ’ rows were returned

’;
$numResults = $result->num_rows;

$all_results = array();
while ($row = mysqli_fetch_assoc($result)) {
// Append all rows to an array
$all_results[] = $row;
}

$j = 0;
while ($j < $numResults) {
$sysName = $all_results[$j][“sys_name”];
echo $sysName;
echo ‘
’;

for($i = 0; $i < $numResults; $i++){
if ($all_results[$i][“sys_name”] = $all_results[$j][“sys_name”]){
echo $all_results[$i][“role_name”];
echo ‘
’;
}
}
echo ‘

---

’;
$j++;

[somethingnew]

So here’s the code that worked in the end. Thanks for helping out

$all_results = array();
while ($row = mysqli_fetch_assoc($result)) {
// Append all rows to an array
$all_results[] = $row;
}
//var_export($all_results);

$j = 0;
$control = $j;
//$all_results[$row['sys_name']][] = $row;
while ($j < $numResults) {
if ($control == $j){
echo 'System Name: ' . $all_results[$j]["sys_name"];
echo '<br/>';

for($i = 0; $i < $numResults; $i++){
if ($all_results[$i]["sys_name"] == $all_results[$j]["sys_name"]){
echo 'Role: ' . $all_results[$i]["role_name"];
echo '<br/>';
$control++;
}
}
echo '<br/>';
}
$j++;
}

[phdr]

Or more simply -

$all_results = [];
while ($row = mysqli_fetch_assoc($result)) {
	$all_results[$row['sys_name']][] = $row;
}

foreach($all_results as $sys_name=>$arr)
{
	echo "System Name: $sys_name<br/>";
	foreach($arr as $row)
	{
		echo "Role: {$row['role_name']}<br/>";
	}
	echo '<br>';
}

[somethingnew]

I’ll try that thank you. I think foreach should be what I attempt next. Thanks again