PHP loop not displaying properly

Question

I need some help with a looping problem. For 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

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

I'm trying to display the results of querying these tables so that it shows each tblDepRatesCats data with the corresponding tblRates_balance data, but instead it is showing tblDepRatesCats so that if tblDepRatesCats row has 3 tblDepRatesCats rows associted with it, the tblDepRatesCats row is repeated 3 times with one row of tblDepRatesCats'

As you can see below "Super Now Checking Account" is displayed 3 times, but what I want is for it to display just once with the 3 results for minimum balance and apy listed under the one header.

NOW Checking Accounts Minimum Daily Balance to Earn APY Annual Percentage Yield $1000 10

Super NOW Checking Account Minimum Daily Balance to Earn APY Annual Percentage Yield % $2222 2

Super NOW Checking Account Minimum Daily Balance to Earn APY Annual Percentage Yield % $2100 25

Super NOW Checking Account Minimum Daily Balance to Earn APY Annual Percentage Yield % $2000 20

Money Market Accounts Minimum Daily Balance to Earn APY Annual Percentage Yield % $3000 30

Below is my test code. Any help would be greatly appreciated.

$result = mysql_query('SELECT tblDepRatesCats.*, tblRates_balance.* FROM tblDepRatesCats JOIN tblRates_balance ON tblDepRatesCats.ID = tblRates_balance.depratecat ORDER BY tblDepRatesCats.header_order, tblRates_balance.suborder;');

while ($row = mysql_fetch_assoc($result)) 
{
 echo ("<table width=\"98%\" border=\"0\" bgcolor:\"#ffffff\"><tr><td>");
 echo ("" . $row["header"] . " <br>");
 echo ("" . $row["left_text"] . "&nbsp;&nbsp;");
 echo ("" . $row["right_text"] . "");
 echo ("</tr></td>");
 // &nbsp temporarily added
 echo ("<tr><td>");
 echo ("" . $row["MinBalance"] . "");
 echo ("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
 echo ("" . $row["InterestRate"] . " <br><br>");
 echo ("</tr></td>");
}

You should stop using Mysql_*, it's been depreciated. Check out MySqli or PDO. — Styphon
– Styphon, Commented Jan 10, 2014 at 15:36
In query result , records repeated. You have to check your query — Kumar V
– Kumar V, Commented Jan 10, 2014 at 15:38

Ryan · Accepted Answer · 2014-01-10 15:38:35Z

2

Just move the output you don't want to be repeated each time to outside of the while loop...

echo ("<table width=\"98%\" border=\"0\" bgcolor:\"#ffffff\"><tr><td>");
echo ("" . $row["header"] . " <br>");
echo ("" . $row["left_text"] . "&nbsp;&nbsp;");
echo ("" . $row["right_text"] . "");
echo ("</tr></td>");
echo ("<tr><td>");
while ($row = mysql_fetch_assoc($result)) 
{
    //&nbsp temporarily added
    echo ("" . $row["MinBalance"] . "");
    echo ("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
    echo ("" . $row["InterestRate"] . " <br><br>");
}
echo ("</tr></td>");

answered Jan 10, 2014 at 15:38

Ryan

3,5801 gold badge24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bobkingof12vs · Accepted Answer · 2014-01-10 17:34:26Z

0

If I am understanding you right, then all you need to do is shift around your code:

$result = mysql_query('SELECT tblDepRatesCats.*, tblRates_balance.* FROM tblDepRatesCats JOIN tblRates_balance ON tblDepRatesCats.ID = tblRates_balance.depratecat ORDER BY tblDepRatesCats.header_order, tblRates_balance.suborder;');
$new_result = array();
while ($row = mysql_fetch_assoc($result)) 
{
  $new_result[$row["header"]][] = $row;
}

foreach($new_result as $new_row){
  echo ("<table width=\"98%\" border=\"0\" bgcolor:\"#ffffff\"><tr><td>");
  echo ("" . $new_row[0]["header"] . " <br>");
  echo ("" . $new_row[0]["left_text"] . "&nbsp;&nbsp;");
  echo ("" . $new_row[0]["right_text"] . "");
  echo ("</tr></td>");

  foreach($new_row as $row){
   // &nbsp temporarily added
   echo ("<tr><td>");
   echo ("" . $row["MinBalance"] . "");
   echo("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
   echo ("" . $row["InterestRate"] . " <br><br>");
   echo ("</tr></td>");
  }

  //also, dont forget to close the table:
  echo "</table>";
}

The way your code was was starting a new table everytime you went through your loop

edit to avoid warning. Though it might make more sense to find a way to not need to have to call $result[0]["xxxxx"] at all. Maybe a static variable would be easier, though a second query to get just those values might be what you are looking for. It just depends

edited Jan 10, 2014 at 17:34

answered Jan 10, 2014 at 15:38

bobkingof12vs

6805 silver badges23 bronze badges

10 Comments

user3133389 Over a year ago

I tried to move the while loop, but now I get an error repeated 3 times:Notice: Undefined variable: row in on line 10..11..12. Do I need another while loop somewhere?

user3133389 Over a year ago

I need the results to display like this: <br> NOW Checking Accounts Minimum Daily Balance to Earn APY Annual Percentage Yield $1000 10 Super NOW Checking Account Minimum Daily Balance to Earn APY Annual Percentage Yield % $2222 2 $2100 25 $2000 20 Money Market Accounts Minimum Daily Balance to Earn APY Annual Percentage Yield % $3000

bobkingof12vs Over a year ago

and those edits, sorry for the bugs... hopefully I got them all this time

user3133389 Over a year ago

Sorry, guess I'm not explaining myself very well. I'm been trying to edit my answers but the site keeps cutting me off.

user3133389 Over a year ago

For my test, the results should display NOW Checking Accounts with one row under it. The Super Now should show 3 rows. The Money Market should show one row.

|

makallio85 · Accepted Answer · 2014-01-10 15:39:42Z

-1

Use left join if there can be 0 links between tables. Using join only will result rows that have no data in balance table to be dropped out from results.

answered Jan 10, 2014 at 15:39

makallio85

1,3562 gold badges14 silver badges29 bronze badges

Collectives™ on Stack Overflow

PHP loop not displaying properly

3 Answers 3

Comments

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related