Having some trouble with a recordset

[cgallent]

Here's what I have, hopefully I'm explaining everything.

On a page I need to be able to display a rate plan for a client. This rate plan is associated with a master plan and provider (each in different tables). So a provider can have several master plans and each of those master plans have many rate plans.

What I need to be able to do is display on the page each of the master plan names followed by all of the rate plans for the master plan. Then, go to the next master rate plan and do the same thing.

Here's the code I've tried:

<?php do {?>
<tr>
<td colspan="2"><p><?php echo $row_Recordset2['pkg_name'];?></p>
<table width="100%" border="0" cellspacing="0" cellpadding="2">
<?php do {?>
<tr>
<td><?php echo $row_Recordset3['pkg_name'];?></td>
<TD align="right">$ <?php echo $row_Recordset3['plan_price'];?>.00</TD>
</tr>
<?php } while ($row_Recordset3 = mysql_fetch_assoc($Recordset3));?>
</table></td>
</tr>
<?php } while ($row_Recordset2 = mysql_fetch_assoc($Recordset2));?>

Recordset2 pulls the master plan names from it's table and recordset 3 pulls the rate plan data from it's table.

What it's doing is putting all of the rate plan data with the first master plan. It's not breaking out so that rate plan 1 is with master plan 1 and rate plan 2 is with master plan 2

Let me know if I'm leaving anything out. Thanks for your help.

cgallent

[coopster]

You could really simplify this by creating one result set using a JOIN statement in your query. Then, while looping through the result set, determine whether or not your key has changed and format your html accordingly. For example:

$sql = "SELECT field1, field2, field3  
 FROM table1 
 INNER JOIN table2 ON (table1.key = table2.key)  
 INNER JOIN table3 ON (table2.key = table3.key)  
 WHERE client = '$myclientvariable'  
 ORDER BY field1, field2, field3"; 
$rows = mysql_query($sql); 
$previous_key_value = ''; // initialize variable 
while ($row = mysql_fetch_assoc($rows)) { 
print '<tr><td>'; 
// Only print the first key value once: 
 if ($row['field1']!= $previous_key_value) { 
 print $row['field1']; 
 $previous_key_value = $row['field1']; 
 } 
print '</td><td>' . $row['field2'] . '</td><td>' . $row['field3'] . '</td></tr>'; 

Disclaimer:
This is only an example. I don't know your table names, columns names or keys or how they are related so I used a generic example. I didn't test any of the code either. However, this should give you an idea of how you might approach a solution.

If you need some specifics, just provide more detail and we'll help you understand how to accomplish the task at hand.

[cgallent]

I finally came up with a join statement that gave me the recordset I needed. The problem lies in how I'm trying to segment and format the data output on the page. Here's an exmple of how I'd like the output as HTML (still need help on the PHP/MySQL:

<tr><td colspan=3>Master.Plan1</td></tr>
<tr><td>Rate.Plan1.field1</td><td>Rate.Plan1.field2</td><td>Rate.Plan1.field3</td></tr>
<tr><td>Rate.Plan2.field1</td><td>Rate.Plan2.field2</td><td>Rate.Plan2.field3</td></tr>
<tr><td>Rate.Plan3.field1</td><td>Rate.Plan3.field2</td><td>Rate.Plan3.field3</td></tr>
<tr><td colspan=3>Master.Plan2</td></tr>
<tr><td>Rate.Plan1.field1</td><td>Rate.Plan1.field2</td><td>Rate.Plan1.field3</td></tr>
<tr><td>Rate.Plan2.field1</td><td>Rate.Plan2.field2</td><td>Rate.Plan2.field3</td></tr>
<tr><td>Rate.Plan3.field1</td><td>Rate.Plan3.field2</td><td>Rate.Plan3.field3</td></tr>

The three tables are carrier, carrier_pkg, carrier_plan. carrier_pkg is keyed to carrier with an carrier_id, carrier_plan is keyed to carrier_plan with pkg_id and to carrier with carrier_id.

Does this explain it better?

Thanks for the reply.
cgallent

[seomike2003]

<?

$query_text="SELECT anytable INNER JOIN what ever join WHERE ever";

$rs = mysql_query($query_text) or die(mysql_error());

?>

<table.....>

<?
while($rw = mysql_fetch_array($rs)){

$variable1 = $rw['dbfield'];
$variable2 = $rw['dbfield2'];
$variable3 = $rw['dbfield3'];
"
"
"
?>
<tr><td colspan=3><? echo $variable1;?></td></tr>
<tr><td><? echo $variable2;?></td><td><? echo $variable3?></td><td><? echo $variable4?></td></tr>
<tr><td>Rate.Plan2.field1</td><td>Rate.Plan2.field2</td><td>Rate.Plan2.field3</td></tr>
<tr><td>Rate.Plan3.field1</td><td>Rate.Plan3.field2</td><td>Rate.Plan3.field3</td></tr>
<tr><td colspan=3>Master.Plan2</td></tr>
<tr><td>Rate.Plan1.field1</td><td>Rate.Plan1.field2</td><td>Rate.Plan1.field3</td></tr>
<tr><td>Rate.Plan2.field1</td><td>Rate.Plan2.field2</td><td>Rate.Plan2.field3</td></tr>
<tr><td>Rate.Plan3.field1</td><td>Rate.Plan3.field2</td><td>Rate.Plan3.field3</td></tr>
<? }?>
</table>