Nested Loops

[Xtremer360]

I'm trying to figure out the best way to handle this situation. What I want it to do is when it runs the groups query the first time and shows the groupName I want it to take that group name's id field value to the bioFieldsQuery and retrieve all of the fields that go with that group and then when that loop is complete and it runs the groups query again for the second group then it uses that second group's id to retrieve all of that group's bioFields until all bioFields and groups have been looped through. As of right now what I have is it showing the group names.

 

<?php
$bioConfigurationID = $_GET['bioConfigurationID'];

$groupsQuery = "
    SELECT 
        *
    FROM
        manager_character_bio_groups";
$groupsResult = mysqli_query($dbc,$groupsQuery);

$bioFieldsQuery = "
    SELECT
        bioFields.id,  
        bioFields.fullName,
        bioFields.status
    FROM 
        manager_character_bio_fields AS bioFields
        LEFT JOIN manager_character_styles AS styles
            ON styles.id = bioFields.characterStyleID  
    WHERE 
        styles.id = '".$bioConfigurationID."'";
$bioFieldsResult = mysqli_query ( $dbc, $bioFieldsQuery ); // Run The Query

?>

 

<div class="form2">
    <form action="" method="post" id="bioConfigurationsForm" class="niceform">
        <?php 
        while ($row = mysqli_fetch_array($groupsResult, MYSQLI_ASSOC)) {
            
            echo "<fieldset>";
            echo "<legend>".$row['groupName']."</legend>";
            
            
            echo "</fieldset>";   
               
        }
        formEdit($contentPageID,$pageName);
        formFooter($fileName,$pageName);
        ?>
    </form>
</div>

[AyKay47]

not really sure what you are trying to accomplish, but you will most likely want to increment a variable to distinguish the number iteration you are in..

 

<div class="form2">
    <form action="" method="post" id="bioConfigurationsForm" class="niceform">
        <?php 
        $count = 0;
        while ($row = mysqli_fetch_array($groupsResult, MYSQLI_ASSOC)) {
            if($count == 1){
                  //first iteration, act accordingly
            }elseif($count == 2){
                  //second iteration, act accordingly
            }
            echo "<fieldset>";
            echo "<legend>".$row['groupName']."</legend>";
            
            
            echo "</fieldset>";   
            $count++;
        }
        formEdit($contentPageID,$pageName);
        formFooter($fileName,$pageName);
        ?>
    </form>
</div>

[Psycho]

You NEVER want to run queries in loops. 99% of the time you can get the results you want with a single query. The other 1% of the time you are trying to do something you shouldn't.

 

In this case you can achieve what you want with one query utilizing a JOIN. I'm not really following your code and I don't know all of the fields you need. But, the 2nd query you have looks pretty close to what I think you need. I think you just need help on how to process the results into the format you want. Below is some sample code, but you don't show the fields you want to display from the "manager_character_styles" table. You will have to include those in the query and add the code to display that data.

 

<?php
$bioConfigurationID = mysql_real_escape_string(trim($_GET['bioConfigurationID']));

$query = "SELECT bioFields.id, bioFields.fullName, bioFields.status
          FROM manager_character_bio_fields AS bioFields
          LEFT JOIN manager_character_styles AS styles
            ON styles.id = bioFields.characterStyleID  
          WHERE styles.id = '".$bioConfigurationID."'";
$result = mysqli_query ($dbc, $bioFieldsQuery ); // Run The Query

if(!$result)
{
    die("Error running query: {$query}<br>Error: " . mysql_error());
}

$lastGroupID = false;
$fieldSets = '';
while($row = mysql_fetch_assoc($result))
{
    //Check if new group
    if($lastGroupID != $row['id'])
    {
        //If not first goup close last fieldset
        if($lastGroupID)
        {
            $fieldSets .= "</fieldset>\n";
        }

        //Create new fieldset
        $fieldSets .= "<fieldset>\n";
        $fieldSets .= "<legend>{$row['fullName']}</legend>\n";
        $lastGroupID = $row['id'];
    }

    //Show record from styles table
    // - need to update query to pull those field
    //   and write the code here to display those records
    // $fieldSets .= ?????

}

//Close last fieldset
$fieldSets .= "</fieldset>\n";

?>
<div class="form2">
    <form action="" method="post" id="bioConfigurationsForm" class="niceform">
        <?php 
        echo $fieldSets;
        formEdit($contentPageID,$pageName);
        formFooter($fileName,$pageName);
        ?>
    </form>
</div>

 

[Xtremer360]

I'm still not sure on this.

 

// Show record from styles table
    // - need to update query to pull those field
    //   and write the code here to display those records
    // $fieldSets .= ?????

[Xtremer360]

I think you have it confused because the groupName should be the in the legend. And then wiht the groupName ID then it uses that to get its fields.

[Psycho]

I think you have it confused because the groupName should be the in the legend. And then wiht the groupName ID then it uses that to get its fields.

 

No, I do not have it confused. I used the "fullName" in the legend which I assumed to be the group name since you didn't have any field explicit called "groupName" in your sample code. If it is supposed to be a different database field, then include that field in the query and use that in the legend. As for groupID, I specifically used that value to determine when there was a change in the groups because the ID *should* be set as a primary key in the database, but the name field likely isn't required to be unique. So, ID is a better parameter to use. Additionally, you SHOULD NOT "then with the groupName ID then it uses that to get its fields". That is the exact opposite of what you should do. You should just do a JOIN to get the data you want.

 

As for

I'm still not sure on this.

 

// Show record from styles table
    // - need to update query to pull those field
    //   and write the code here to display those records
    // $fieldSets .= ?????

How am I supposed to know what data you want to display. I tried to make this as easy as possible, but based upon the code you first showed it is impossible for me to know what data you want to pull from the "manager_character_style" table since you didn't show any code that did that.

 

I will say there is one thing I missed in the code I provided. The query will need an "ORDER BY bioFields.id" clause to ensure the records are arranged accordingly.

 

If you want more help, then specify the fields from the tables that you want displayed under the legend. If you insist on trying to run queries in loops don't ask for my help. I'm not upset or trying to be mean, I just see no point in tying to help if you don't want to take any advice.

[Xtremer360]

while ($row2 = mysqli_fetch_array($bioFieldsResult, MYSQLI_ASSOC)) {
                
                    echo "<dl>";
                        echo "<dt><label for=".$row['id'].">".$row['groupName']."</label></dt>";
                        echo "<dd><input type=\"radio\" name=".$row['id']." id=".$row['id']." value=\"1\""; 
                        if($row['statusID'] == "Active") {
                            echo " checked=\"checked\"";
                        }
                        echo "/><label class=\"check_label\">Active</label><input type=\"radio\" name=".$row['id']." id=".$row['id']." value=\"1\""; 
                        if($row['status'] == "Inactive") {
                            echo " checked=\"checked\"";
                        }
                        echo "/><label class=\"check_label\">Inactive</label>";
                        echo "</dd>";
                    echo "</dl>";

[Psycho]

Right, I saw that in your first post. But, the query you showed only pulled the fields: bioFields.id, bioFields.fullName, and bioFields.status. But, the code you used to display the results used: id, groupName, and statusID and status. I can't see into your database so I made some logical assumptions. We can either continue on with you providing the same data as before, but it won't help me to provide a solution for you.

 

Like I said, give me a description of the tables involved (i.e. the field names and associations) and give me a description of what you are trying to achieve and I can help you.

[Xtremer360]

I apologize for the confusion is my db structure.

 

Table: manager_character_bio_groups

Fields: id, groupName

 

Table: manager_character_bio_fields

Fields: id, groupName, characterStyleID, status, fullName, shortName

 

Okay main while loop is looping through ALL of the groups and putting the groupName as the legend and inside that while loop is another while loop where it caries the id of the group and thtne queries the db for the fiels that exist in that id which is equal to the groupID of the bio_fields table. It will put radio buttons for each of the fields. One will an Active radio button and the other will be an Inactive button and these two buttons will have a value of Active or Inactive and one of them will be preselected due to the db query because it is equal to the status field from the bio_fields table.

[Xtremer360]

Anyone understand what I"m attempting to do?

[Psycho]

... inside that while loop is another while loop where it caries the id of the group and thtne queries the db for the fiels that exist in that id which is equal to the groupID of the bio_fields table.

 

Have you not heard anything I've stated in this thread? You do not want to run queries in a loop. You do one single query with all the data that you need. Then format the output accordingly. I think I've stated this about four times now.

 

As to your explanation, it makes no sense. For one, you have the field "groupName" in both tables. Based upon the table names, that field should only appear in the "manager_character_bio_groups" table. Also, you state that you're trying to compare the id of the groups to the "groupID" field in the "manager_character_bio_fields" table, but no such field exists in your table descriptions. If you have a field from one table as a foreign key in another table it is a good idea to give them the same name in both tables so it is easy to understand the foreign key references. With the two tables you have two different "id" fields, so that requires more work to differentiate them in the query (you should name them something like "groupID" and "fieldID"). Also, you ahve two "groupName" fields i those two tables. Are they different values or what? Lastly, using text values of "Active" / "Inactive" is a poor implementation. You should change the column name to "Active" and use a tiny int type field saving a  0(false) or 1 (true), that way you don't need to compare the value of the field to a text value.

 

Here is some sample code (again I assumed the relationship between he tables is character_bio_groups.id = manager_character_bio_fields.groupName, but I could be wrong, but you keep providing the same imprecise information. I'm sorry if you think I'm being rude, but you've been around here long enough that you should know better.

 

Anyway, this is the best I can do with what I have been given. If it is not what you are after you need to provide a description of ALL the fields relevant in the tables involved" the fields used for the output and the fields that are used to JOIN the tables.

 

<?php

$bioConfigurationID = mysql_real_escape_string(trim($_GET['bioConfigurationID']));

$query = "SELECT groups.id as grpID, groups.groupName as grpGroupname,
                 fields.id as fldID, groups.groupName as fldGroupname, fields.status
          FROM manager_character_bio_groups AS groups
          LEFT JOIN manager_character_bio_fields AS fields
            ON groups.id = fields.groupID
          WHERE fields.characterStyleID = '".$bioConfigurationID."'
          ORDER BY groups.id";
$result = mysqli_query ($dbc, $bioFieldsQuery ); // Run The Query

if(!$result)
{
    die("Error running query: {$query}<br>Error: " . mysql_error());
}

$lastGroupID = false;
$fieldSets = '';
while($row = mysql_fetch_assoc($result))
{
    //Check if new group
    if($lastGroupID != $row['grpID'])
    {
        //If not first goup close last fieldset
        if($lastGroupID)
        {
            $fieldSets .= "</fieldset>\n";
        }

        //Create new fieldset for group
        $fieldSets .= "<fieldset>\n";
        $fieldSets .= "<legend>{$row['grpGroupname']}</legend>\n";
        $lastGroupID = $row['grpID'];
    }

    //Display the record
    $activeChecked   = ($row['status']=="Active")   ? ' checked="checked"' : '';
    $inactiveChecked = ($row['status']=="Inactive") ? ' checked="checked"' : '';
    $fieldSets .= "<dl>\n";
    $fieldSets .= "  <dt><label>{$row['fldGroupname']}</label></dt>\n";
    $fieldSets .= "  <dd>\n";
    $fieldSets .= "    <input type=\"radio\" name=\"fieldID[{$row['fldID']}]\" id=\"field_{$row['fldID']}_A\" value=\"1\"{$activeChecked} />\n"; 
    $fieldSets .= "    <label class=\"check_label\">Active</label>\n";
    $fieldSets .= "    <input type=\"radio\" name=\"fieldID[{$row['fldID']}]\" id=\"field_{$row['fldID']}_I\" value=\"0\"{$inactiveChecked} />\n";
    $fieldSets .= "    <label class=\"check_label\">Inactive</label>\n";
    $fieldSets .= "  </dd>\n";
    $fieldSets .= "</dl>\n";

}

//Close last fieldset
$fieldSets .= "</fieldset>\n";

?>
<div class="form2">
    <form action="" method="post" id="bioConfigurationsForm" class="niceform">
        <?php 
        echo $fieldSets;
        formEdit($contentPageID,$pageName);
        formFooter($fileName,$pageName);
        ?>
    </form>
</div>

 

[Xtremer360]

yes I'm sorry the groupName in the fields table was supposed to be groupID I mistakenly  put groupName again.

[Xtremer360]

So the thing about this is that the CORRECT structure is this:

 

Table: manager_character_bio_groups

Fields: id, groupName

 

Table: manager_character_bio_fields

Fields: id, groupID, characterStyleID, status, fullName, shortName

 

Also how they are connected is through the id of the group is equal to the groupID of the fields table. This works except for it does not show the fullName of the field. That is the fldFullName that isn't displaying.

 

                 fields.id as fldID, fields.fullName as fldFullName, fields.status
          FROM manager_character_bio_groups AS groups
          LEFT JOIN manager_character_bio_fields AS fields
            ON groups.id = fields.groupID
          WHERE fields.characterStyleID = '".$bioConfigurationID."'
          ORDER BY groups.id";
$result = mysqli_query ($dbc, $query ); // Run The Query

if(!$result)
{
    die("Error running query: {$query}<br>Error: " . mysql_error());
}

$lastGroupID = false;
$fieldSets = '';
while($row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
{
    //Check if new group
    if($lastGroupID != $row['grpID'])
    {
        //If not first goup close last fieldset
        if($lastGroupID)
        {
            $fieldSets .= "</fieldset>\n";
        }

        //Create new fieldset for group
        $fieldSets .= "<fieldset>\n";
        $fieldSets .= "<legend>{$row['grpGroupname']}</legend>\n";
        $lastGroupID = $row['grpID'];
    }

    //Display the record
    $activeChecked   = ($row['status']=="Active")   ? ' checked="checked"' : '';
    $inactiveChecked = ($row['status']=="Inactive") ? ' checked="checked"' : '';
    $fieldSets .= "<dl>\n";
    $fieldSets .= "  <dt><label>{$row['fldFullName']}</label></dt>\n";
    $fieldSets .= "  <dd>\n";
    $fieldSets .= "    <input type=\"radio\" name=\"fieldID[{$row['fldID']}]\" id=\"field_{$row['fldID']}_A\" value=\"1\"{$activeChecked} />\n"; 
    $fieldSets .= "    <label class=\"check_label\">Active</label>\n";
    $fieldSets .= "    <input type=\"radio\" name=\"fieldID[{$row['fldID']}]\" id=\"field_{$row['fldID']}_I\" value=\"0\"{$inactiveChecked} />\n";
    $fieldSets .= "    <label class=\"check_label\">Inactive</label>\n";
    $fieldSets .= "  </dd>\n";
    $fieldSets .= "</dl>\n";


}

//Close last fieldset
$fieldSets .= "</fieldset>\n";

 

[Psycho]

You marked this as solved, but apparently it isn't?

 

If the value for this field

fields.fullName as fldFullName

 

is not getting displayed in the output, you will need to go through normal debugging steps to determine the problem. I can only provide some things to check since I can't see your data.

 

Since the query is not failing, I have to assume that the field "fields.fullName" does exist - otherwise the query would create an error. The second thing I see is that the name we are giving to that value, "fldFullName", does appear to exactly match the name used when referencing the results: "$row['fldFullName']". So, the only things I can see as being a problem are:

 

1. The field "fullName" in that table only has empty values. Check the tables directly in the database (e.g. phpmyadmin)

2. There is a style being applied to the <dt> elements that is preventing it from displaying in the browser (check the HTML source code from the browser).

3. If neither of those is the problem, echo the query to the page and then run it through phpmyadmin. That will at least tell you if the problem is with the query or in the PHP code that processes the results.

[Xtremer360]

I marked it as solved because it is. I looked over the code and did a little tinkering with it and was able to figure this out. I do appreciate your help to get me understanding it to that point.