Looping problem-should be simple but I can't get it.

[grumpy]

I 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.

 

I'm trying to display the results of querying these tablesso 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, It displays incorrectly like this:

 

NOW Checking Accounts 

Minimum Daily Balance to Earn APY 
  $1000 
  Super NOW Checking Account 

Minimum Daily Balance to Earn APY 
  $2222 
  Super NOW Checking Account 

Minimum Daily Balance to Earn APY 
  $2100 
  Super NOW Checking Account 

Minimum Daily Balance to Earn APY 
  $2000 
 

Below  is my code. Extra echos included for clarity. I can't figure it out. Any help is appreciated. Thanks.

<?php


include ('connect.php');
error_reporting(E_ALL); 


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


  
$result = $qry or die ("Error:" . mysql_error()); 
while ($row = mysql_fetch_assoc($result)) {
echo ("<table width=\"98%\" border=\"0\" bgcolor:\"#ffffff\"><tr><td>");


echo ("" . $row["header"] . " <br><br>");
echo ("" . $row["left_text"] . " <br><br>");


echo ("</tr></td>");
  
echo ("<tr><td>");
echo ("" . $row["MinBalance"] . " <br><br>");


echo ("


</tr></td>");


}


?>

[ginerjm]

Something looks fishy after your run your query.  You destroy the query result with your weird test of the query success.  After that I don't know what you may be seeing.

 

It should resemble this pseudo-code:

 

$result = MySQL_query(  your query string) or die("query failed");
while ($row = MySQL_fetch_assoc($result))
{
   process result row
}

[jazzman1]

The result is correct. You should organize the html output only in the way you want to be.

 

Take a look at mine, using a SQL GROUP_CONCAT() function and Barand's examples, for others I didn't test them

Edited January 9, 2014 by jazzman1