homepage Welcome to WebmasterWorld Guest from 54.166.65.9
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Having some trouble with a recordset
cgallent




msg:1298728
 2:29 pm on Jan 13, 2004 (gmt 0)

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




msg:1298729
 6:12 pm on Jan 13, 2004 (gmt 0)

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




msg:1298730
 7:49 pm on Jan 13, 2004 (gmt 0)

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




msg:1298731
 10:11 pm on Jan 13, 2004 (gmt 0)

<?

$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>

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved