While loop and format of results

[DesignerSeries]

I have a database with a cascading approach to its rows and I can either get the results repeated over and again or I cannot get the end portions of this to return to the page (e5). Here is the data:

Rolename, Class, e2, e3, e4 ,e5

Example

Rolename: Role 1
Class: 123
e5: five

Rolename: Role 1
Class: 123
e5: six

Rolename could be repeated many times with different e5 values.

In my code I have the following, and it is returning the results over and again, you will see that I am trying to push the results to a table. Right now it shows the rolename over and again. If I group the records in the query it only returns the first row. I need to show one Role, possibly more classes and likely more e2, e3 ,e4 ,e5., make sense?

$sql1=` $DB_con2->prepare("SELECT ability_id, oprid, rolename, class, e2, e3, e4, e5 FROM validation_abilities WHERE ability_id IN ('".$ability_role_implode."') AND rolename = :rolename");

$sql1->execute(array(":rolename"=>$rolename));


while($sql1_results1 = $sql1->fetch(PDO::FETCH_ASSOC)){

        $ability_id_role = $sql1_results1['ability_id'];
        $oprid_ul1 = $sql1_results1['oprid'];
        $rolename1 = $sql1_results1['rolename'];
        $classid1 = $sql1_results11['classid'];
        $e2_1 = $sql1_results1['e2'];  
        $e3_1 = $sql1_results1['e3'];  
        $e4_1 = $sql1_results1['e4'];  
        $e5_1 = $sql1_results1['e5'];  

  echo "<td>";  
        if(!empty($classid1)){
        echo $application_classid_title;    
        echo "<p class=\"v\"><a href=\"$environment/access/analysis_actions/e2/?app=$app&ru=U&classid=$classid1&ruleid=$rule_id_returned\"> $classid1</a></p><br/>";             
        }    
        if(!empty($e2_1)){
        echo $application_e2_title;            
        echo "<p class=\"v\"><a href=\"$environment/access/analysis_actions/e2/?app=$app&ru=U&e2=$e2_1&ruleid=$rule_id_returned\">$e2_1</a></p><br/>";             
        }          
        if(!empty($e3_1)){
        echo $application_e3_title;
        echo "<p class=\"v\"><a href=\"$environment/access/analysis_actions/e3/?app=$app&ru=U&e3=$e3_1&ruleid=$rule_id_returned\"> $e3_1</a></p><br/>";             
        }    
        if(!empty($e4_1)){
        echo $application_e4_title;
        echo "<p class=\"v\"><a href=\"$environment/access/analysis_actions/e4/?app=$app&ru=U&e4=$e4_1&ruleid=$rule_id_returned\">$e4_1</a></p><br/>";             
        }          
        if(!empty($e5_1)){
        echo $application_e5_title;
        echo "<p class=\"v\"><a href=\"$environment/access/analysis_actions/e5/?app=$app&ru=U&e5=$e5_1&ruleid=$rule_id_returned\">$e5_1</a></p><br/>";             
        }              

          echo "</p>";
        echo "</td>";   
        }

Please let me know if you need anything else, I have been going round and round in circles on this one..

Thanks,

DS

[laserlight]

Maybe it would be easier to rework the database design. Could you tell us more about roles and classes and how they are related, and what do these e2, e3, e4 etc columms mean? Are they really numbered?

[DesignerSeries]

So here is an example and a better explanation of what I am trying to achieve:

Role -> Role1, Classid ->classid1, e3-> e3_one, e5-> e5_one

Role-> Role1, Classid -> classid1, e3->e3_one, e5-> e5_two

Role -> Role1, Classid -> classid1, e3->e3_one, e5-> e5_three

The Role will always be the parent to eh other elements.

Do I need to move to foreach loops within loops for each of these columns? If so how would that be best implemented?

Thanks!

[dalecosp]

Agreed. A view in MySQL Workbench or some similar software, or even a "describe" would help:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| bad   | varchar(128) | YES  |     | NULL    |                |
| good  | varchar(128) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

[NogDog]

If I'm understanding the issue to be that you want some sort of grouping of the query results to avoid repeating the same role and/or class values over and over, it may be advisable on your fetch loop to populate a multi-dimension array from the results, then you can loop on that array (and its subordinate dimensions) to do the actual output. That way you can put the results in the desired hierarchical structure, then use that array as the source for your HTML output.

Or I'm entirely missing the issue. 🙂

[DesignerSeries]

Thanks NogDog, that is correct would you or anyone happen to have a referral to a good tutorial on this approach? I have seen some examples of foreach loops within loops as opposed to a while loop for all (which is what I am using)

[Weedpacket]

This is just for display, right? And any other time you're using a record you want the whole record?

While I feel that a design that reflects the hierarchy may help, if it's just a matter of display then:

1. For a start, add an ORDER BY clause listing the different display columns (ORDER BY Role, Classid, ...)
2. Before beginning the loop, create some variables to store the values from the previous row in the table. (There's no previous row before you start, so the variables will have nulls in them.)
3. Going through the loop, check the current value in each column with the corresponding variable containing the previous value. If they're the same, then it's part of the same group so doesn't need printing (print an empty string instead so the columns don't get messed up).
4. More complicated would involve resetting ClassId every time Role changes, and e2 every time ClassId changes (including if it's been reset because Role changed), resetting e3 every time e2 changes (including if...).
5. Actually, maybe building a hierarchical structure would be easier.
6. Which would basically be taking $Role, $ClassID, $e2, etc. and setting $hierarchy[$Role][$ClassID][$e2][$e3]... = true.
7. 'Course, then you need nested loops to loop through those nested arrays.

[DesignerSeries]

That's right, just for display, I'll work through your suggestions. Thanks for your help!