Looping problem

[grumpy]

I've been away from PHP for awhile and need some help with a looping problem. In my example code below, I have two Mysql tables:

 

tblDepRatesCats:

 

ID | header | left_text | center_text | right_text | header_order

 

 

tblRates_balance:

 

id | depratecat | MinBalance | InterestRate | APY | suborder

 

 

tblDepRatesCats.ID =  tblRatesBalance.depratecat. For each row in tblDepRatesCats, there may be 0 or 1 or more rows in tblRates_balance.

 

When results displayed, it should show each tblDepRatesCats row data followed by the related tblRatesBalance data (and the ability to add/edit more rates).

 

My problem is that I can only get the first row of tblDepRatesCats and its relative tblRates_balance rows to appear. I can't figure out why it won't loop and show the other.

 

 You can view my test page here: http://www.bentleg.com/vieweditratecats7.php.

 

My relevant test code is below. Thanks for any assistance.

 

<form action="<? echo htmlentities($_SERVER['PHP_SELF']); ?>" name="Edit" method="POST">  
     <?php
 $sql = "select * from tblDepRatesCats order by header_order";
$result = $link->query($sql);


 while($row = mysqli_fetch_assoc($result)){


 $ID[] = $row["ID"];


// for testing
if(is_array($cattedid)){
echo "IS ARRAY";
}else{
echo"not array";
}
echo implode(',',$ID);


// end arrray test


echo("<hr><input type=\"text\" name=\"ID[]\" value=\"" . $row["ID"] . "\"><p><tr><td>Category name/header text: <input type=\"text\" name=\"header$ID\" value=\"" . $row["header"] . " \" size=\"40\"> Order: <input type=\"text\" name=\"header_order$ID\" value=\"" . $row["header_order"] . "\" size=\"2\"><br>
Left sub-text:<input type=\"text\" name=\"left_text$ID\" value=\"" . $row["left_text"] . "\" size=\"25\"> 
Center sub-text:<input type=\"text\" name=\"center_text$ID\" value=\"" . $row["center_text"] . "\" size=\"25\">
Right sub-text<input type=\"text\" name=\"right_text$ID\" value=\"" . $row["right_text"] . "\" size=\"25\">


<input type=\"checkbox\" name=\"delete_ids[]\" value=\"" . $row["ID"] . "\"> Mark to delete




</p>




 <input type=\"submit\" name=\"editcat\" value=\"EDIT\">
</form>


");


foreach ($ID as $new_depratecat) {   
$sql2="SELECT * FROM tblRates_balance where depratecat='$new_depratecat' ORDER BY suborder";
$result = $link->query($sql2);
?>




<div style="width:90%;margin:auto;">
   
<form method="POST" id="new_depratecat">
    <div id="itemRows">
  
    Minimum Balance: <input type="text" name="add_MinBalance" size="30" /> 
    Interest Rate: <input type="text" name="add_InterestRate" /> 
    APY: <input type="text" name="add_APY" /> 
     Order: <input type="text" name="add_suborder" size="2"/> 




  << Add data and click on "Save Changes" to insert into db. <br>
  You can add a new row and make changes to existing rows all at one time and click on "Save Changes."
  New entry row will appear above after saving.


    <?php


    while($rates = mysqli_fetch_array($result)): ?>
        <p id="oldRow<?=$rates['id']?>">
       
       
        Minimum Balance: <input type="text" name="MinBalance<?=$rates['id']?>" value="<?=$rates['MinBalance']?>" />
         Interest Rate: <input type="text" name="InterestRate<?=$rates['id']?>" value="<?=$rates['InterestRate']?>" /> 


         APY: <input type="text" name="APY<?=$rates['id']?>" value="<?=$rates['APY']?>" />
          Order: <input type="text" name="suborder<?=$rates['id']?>" value="<?=$rates['suborder']?>" />
         <input type="checkbox" name="delete_ids[]" value="<?=$rates['id']?>"> Mark to delete</p>
        <?php endwhile;?>


    </div>


    <p><input type="submit" name="ok" value="Save Changes"></p>
    </form>

 

[Barand]

try using $result2 in conjunction with $sql2 instead of $result

[grumpy]

Thank you, that helped but not quite solved. . I still have something wrong.  Now the result rows show all the rows for its id in the array:

http://www.bentleg.com/vieweditratecats7.php

 

Still can't see my problem. any additional help much appreciated.

[cyberRobot]

Thank you, that helped but not quite solved. . I still have something wrong.  Now the result rows show all the rows for its id in the array:

http://www.bentleg.com/vieweditratecats7.php

 

The following line of code will eventually store all the IDs processed by the while loop?

$ID[] = $row["ID"];

Is there a reason you're storing the IDs? If not, you could just use the $row['ID'] variable. For example:

$sql2="SELECT * FROM tblRates_balance where depratecat='{$row['ID']}' ORDER BY suborder";

[grumpy]

Thank you for your help. Its working as expected now. I've posted my fixed text code here:

 

<form action="<? echo htmlentities($_SERVER['PHP_SELF']); ?>" name="Edit" method="POST">  
     <?php
 $sql = "select * from tblDepRatesCats order by header_order";
$result = $link->query($sql);


 while($row = mysqli_fetch_assoc($result)){


 $ID[] = $row["ID"];


// for testing
if(is_array($cattedid)){
echo "IS ARRAY";
}else{
echo"not array";
}
echo implode(',',$ID);


// end arrray test


echo("<hr><input type=\"text\" name=\"ID[]\" value=\"" . $row["ID"] . "\"><p><tr><td>Category name/header text: <input type=\"text\" name=\"header$ID\" value=\"" . $row["header"] . " \" size=\"40\"> Order: <input type=\"text\" name=\"header_order$ID\" value=\"" . $row["header_order"] . "\" size=\"2\"><br>
Left sub-text:<input type=\"text\" name=\"left_text$ID\" value=\"" . $row["left_text"] . "\" size=\"25\"> 
Center sub-text:<input type=\"text\" name=\"center_text$ID\" value=\"" . $row["center_text"] . "\" size=\"25\">
Right sub-text<input type=\"text\" name=\"right_text$ID\" value=\"" . $row["right_text"] . "\" size=\"25\">


<input type=\"checkbox\" name=\"delete_ids[]\" value=\"" . $row["ID"] . "\"> Mark to delete




</p>




 <input type=\"submit\" name=\"editcat\" value=\"EDIT\">
</form>


");


//foreach ($ID as $new_depratecat) {   
//$sql2="SELECT * FROM tblRates_balance where depratecat='$new_depratecat' ORDER BY suborder";
$sql2="SELECT * FROM tblRates_balance where depratecat='{$row['ID']}' ORDER BY suborder";
$result2 = $link->query($sql2);
?>




<div style="width:90%;margin:auto;">
   
<form method="POST" id="new_depratecat">
    <div id="itemRows">
  
    Minimum Balance: <input type="text" name="add_MinBalance" size="30" /> 
    Interest Rate: <input type="text" name="add_InterestRate" /> 
    APY: <input type="text" name="add_APY" /> 
     Order: <input type="text" name="add_suborder" size="2"/> 




  << Add data and click on "Save Changes" to insert into db. <br>
  You can add a new row and make changes to existing rows all at one time and click on "Save Changes."
  New entry row will appear above after saving.


    <?php


    while($rates = mysqli_fetch_array($result2)): ?>
        <p id="oldRow<?=$rates['id']?>">
       
       
        Minimum Balance: <input type="text" name="MinBalance<?=$rates['id']?>" value="<?=$rates['MinBalance']?>" />
         Interest Rate: <input type="text" name="InterestRate<?=$rates['id']?>" value="<?=$rates['InterestRate']?>" /> 


         APY: <input type="text" name="APY<?=$rates['id']?>" value="<?=$rates['APY']?>" />
          Order: <input type="text" name="suborder<?=$rates['id']?>" value="<?=$rates['suborder']?>" />
         <input type="checkbox" name="delete_ids[]" value="<?=$rates['id']?>"> Mark to delete</p>
        <?php endwhile;?>


    </div>


    <p><input type="submit" name="ok" value="Save Changes"></p>
    </form>
    
    <?php
}


//}


?>