• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

PHP - Nested Repeat Region - two tables

I would like to know how to get nested repeated regions working correctly.  I have tried Dreamweavers Developer Toolbox and am guessing it is not 100% what I need and would prefer to code it manually rather than rely on the extension.


----------------------------------------------------------
For example I have two tables:
OURPRICES
COMPRICES
-----------------------------------------------------------
The OURPRICES table has the following fields:
ourpId
stationID
price reg
date

The COMPRICES table has the following fields:
comppriceId
competitorID
stationId
price reg
date
-------------------------------------------------------------


I have the date posted from a form and would like to link up matching the station id fields and the date fields..   How would I create a nested repeat region that would allow me to have the single station listed with a subtable with all of the competitor prices.  
Example:
      --  > Station --- Date ---- Price
                     -- > Competitor --  Price
                     -- > Competitor --  Price
                     -- > Competitor --  Price
0
nxpsupport
Asked:
nxpsupport
  • 5
  • 3
1 Solution
 
Vel EousResearch & Development ManagerCommented:
You haven't actually stated that you are using a Database, but you have mentioned tables so I am assuming you are.

If that is the case, something like the following would work.
<?PHP
 
// get the date from the input field
$date = $_POST['dateField'];
 
// use that date to perform query
// select all columns from both tables where the date is $date
// and both stationId and date match
$result = mysql_query("SELECT * FROM ourprices, comprices WHERE ourprices.date = comprices.date AND ourprices.stationId = comprices.stationId AND ourprices.date = '$date'");
 
// while there are rows returned by $result
// echo some the data [0] = column 1, [1] = column 2 ...
while ($rows = myswl_fetch_array($result)) {
echo $rows[0] . " --> " . $rows[1];
}
 
?>

Open in new window

0
 
nxpsupportAuthor Commented:
Would that actually allow for multiple competitors to be repeated?  The query in yours would provide me with too many results I think since there may be multiple competitors price records for each record of our prices.
0
 
nxpsupportAuthor Commented:
You are correct though I am using a mysql db.

The more I look at it I need to add valid information in for the stations and my original idea will not work which I think is why I am confused.  I have changed what I wanted to do a little.

I have a group of stations grouped into territories(territories table - shouldn't be needed - the stations table has a terr_id I will use for filtering).  From the form you now select a date and a territory and hit submit.  The stations table has a field terrId

Now what I need is the following results and nested repeating region:

STATION -- Location -- CIty -- State
      -------> Our Price Regular  --- Date
               -------> Comp Price Regular(Would vary depending on how many competitors each station has)
               -------> Comp Price Regular
               -------> Comp Price Regular
     
     
Is this possible?  I might be a little confusing but maybe you can see what I am getting at?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Vel EousResearch & Development ManagerCommented:
>>  there may be multiple competitors price records for each record of our prices.

Why?


The code:

$result = mysql_query(""); // add your SELECT statement

// while there are rows returned by $result
// echo some the data [0] = column 1, [1] = column 2 ...
while ($rows = mysql_fetch_array($result)) {
echo $rows[0] . " --> " . $rows[1];
}

will iterate through all the database rows (or repeat as you say).  From the sounds of it, what you are needing to do is take a look at the way your database is organised and then sort out the $result sql statement you feed the while loop.
0
 
nxpsupportAuthor Commented:
I was able to get what I wanted somewhat so far.  I have been up all night working on this.  I do thank you for your help.

I now have three tables for this issue:
STATIONS
OURPRICES
COMPPRICES

Current Situation:
Stations is the master and at the moment ourprices is the detail/nested table.  
The main table lists all of the stations information.  
Ourprices lists the current days prices in a nested day table based on the PRICEDATE post from the form.
Compricing is not added yet.

Need:
I would like to add in the competitor prices.  I should be able to just add a third recordset and insert it into the code.  
 if ($totalRows_master1stations>0) {
    $nested_query_detail2ourprices = str_replace("123456789", $row_master1stations['station_id'], $query_detail2ourprices);
    mysql_select_db($database_lgas);
    $detail2ourprices = mysql_query($nested_query_detail2ourprices, $lgas) or die(mysql_error());
    $row_detail2ourprices = mysql_fetch_assoc($detail2ourprices);
    $totalRows_detail2ourprices = mysql_num_rows($detail2ourprices);
    $nested_sw = false;
    if (isset($row_detail2ourprices) && is_array($row_detail2ourprices)) {
      do { //Nested repeat
?>
        
        <tr bgcolor="#999999">
          <td><span class="style3">&nbsp;&nbsp;&nbsp;&nbsp;</span></td>
          <td><span class="style3">Reg</span></td>
          <td><span class="style3">Diesel</span></td>
          <td><span class="style3">GV Gas</span></td>
          <td><span class="style3">GV Diesel</span></td>
        </tr>
        <tr bgcolor="#99CCFF">
          <td><span class="style4"></span></td>
          <td><?php echo $row_detail2ourprices['ourRegPrice']; ?></td>
          <td><?php echo $row_detail2ourprices['ourDieselPrice']; ?></td>
          <td><?php echo $row_detail2ourprices['grossVolGas']; ?></td>
          <td><?php echo $row_detail2ourprices['grossVolDiesel']; ?></td>
        </tr>
        <tr>
          <td colspan="5">&nbsp;</td>
        </tr>
        <?php
      } while ($row_detail2ourprices = mysql_fetch_assoc($detail2ourprices)); //Nested move next
    }
  }
?>
      <?php } while ($row_master1stations = mysql_fetch_assoc($master1stations)); ?>
  </table>

Open in new window

0
 
nxpsupportAuthor Commented:
It would look like this

Station - Name - Address
  -->>>> OUR PRICING --- Price Reg - Price Diesel
  -->>>> COMPETITOR PRICING ---- Price Reg - Price Diesel (Multiple competitors are returned in the query)
0
 
nxpsupportAuthor Commented:
for example I was thinking I could insert the same code just changing the variables around.    This is what I have been working on.

detail3comppricing is a seperate recordset I created for the competitor's prices.
if ($totalRows_master1stations>0) {  
    $nested_query_detail3comppricing = str_replace("123456789", $row_master1stations['station_id'], $query_detail3comppricing);      
    mysql_select_db($database_lgas);
    $detail3comppricing = mysql_query($nested_query_detail3comppricing, $lgas) or die(mysql_error());
    $row_detail3comppricing = mysql_fetch_assoc($detail3comppricing);
    $totalRows_detail3comppricing = mysql_num_rows($detail3comppricing);
    
    if (isset($row_detail3comppricing) && is_array($row_detail3comppricing)) {
      do { //Nested repeat
?>
        
        <tr bgcolor="#999999">
          <td><span class="style3">&nbsp;&nbsp;&nbsp;&nbsp;</span></td>
          <td><span class="style3">Reg</span></td>
          <td><span class="style3">Diesel</span></td>
          <td><span class="style3">GV Gas</span></td>
          <td><span class="style3">GV Diesel</span></td>
        </tr>
        <tr bgcolor="#99CCFF">
          <td><span class="style4"></span></td>
          <td><?php echo $row_detail3comppricing['RegPrice']; ?></td>
          <td><?php echo $row_detail3comppricing['DieselPrice']; ?></td>
          <td><?php echo $row_detail3comppricing['grossVolGas']; ?></td>
          <td><?php echo $row_detail3comppricing['grossVolDiesel']; ?></td>
        </tr>
        <tr>
          <td colspan="5">&nbsp;</td>
        </tr>
        <?php
      } while ($row_detail3comppricing = mysql_fetch_assoc($detail3comppricing)); //Nested move next
    }

Open in new window

0
 
Vel EousResearch & Development ManagerCommented:
Looks to me like you have a lot of redundant code if your database table attributes are what you say they are.

For adding the name of the station that the prices are associated with, an amendment to the $ourResult SQL query could return the station detail as well.

I may still be getting the wrong end of the stick here, if so I appologise.  I too have been up all night.  :P
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-
 
transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 
 
<head>
<title>Petrol Comparison</title>
 
<style type="text/css">
 
body {
	font: normal 10px Verdana;
	color: #666666;
	}
 
table.index {
	display: table;
	border-collapse: collapse;
	width: 100%;
	}
 
table.index a {
	color: #6D929B;
	}
 
table.index th {
	font: bold 10px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
	color: #0000000;
	border-right: 1px solid #CDCDCD;
	border-bottom: 1px solid #CDCDCD;
	border-top: 1px solid #CDCDCD;
	letter-spacing: 2px;
	text-transform: uppercase;
	text-align: left;
	padding: 6px 6px 6px 12px;;
	}
 
table.index th.empty {
	border: none;
	background: none;
	}
 
table.index th.borderLeft {
	border-left: 1px solid #CDCDCD;
	}
 
table.index th.first, table.index td.first {
	border-left: 1px solid #CDCDCD;
	}
 
 
table.index tr.one {
	background: #FFFFFF;
	}
 
 
 
table.index tr.two {
	background: #F5FAFA;
	}
 
 
table.index tr:hover {
	background: #F8F8F8;
	}
 
 
table.index td {
	border-right: 1px solid #CDCDCD;
	border-bottom: 1px solid #CDCDCD;
	padding: 6px 6px 6px 12px;
	vertical-align: middle;
	}
 
</style>
 
 
</head>
 
<body>
 
<table class="index">
	<tr>
		<th class="empty">&nbsp;</th>
		<th class="borderLeft">Regular</th>
		<th>Diesel</th>
		<th>GV Gas</th>
		<th>GV Diesel</th>
	<tr>
 
<?PHP
 
$date = $_POST['dateField'];
 
$ourResult = ("SELECT * FROM ourPrices WHERE date = '$date'");
$compResult = ("SELECT * FROM compPrices WHERE date = '$date'");
 
while (($ourRows = mysql_fetch_assoc($ourResult)) && ($compRows = mysql_fetch_assoc($compResult))) {
	$ourReg = $ourRows['ourRegPrice'];
	$ourDiesel = $ourRows['ourDieselPrice'];
	$ourGVGas = $ourRows['grossVolGas'];
	$ourGVDiesel = $ourRows['grossVolGas'];
	$compReg = $compRows['RegPrice'];
	$compDiesel = $compRows['DieselPrice'];
	$compGVGas = $compRows['grossVolGas'];
	$compGVDiesel = $compRows['grossVolDiesel'];
 
?>
 
	<tr>
		<th class="borderLeft">Our Price</th>
		<td><?PHP echo $ourReg; ?></td>
		<td><?PHP echo $ourDiesel; ?></td>
		<td><?PHP echo $ourGVGas; ?></td>
		<td><?PHP echo $ourGVDiesel; ?></td>
	</tr>
	<tr>
		<th class="borderLeft">Competitors Price</th>
		<td><?PHP echo $compReg; ?></td>
		<td><?PHP echo $compDiesel; ?></td>
		<td><?PHP echo $compGVGas; ?></td>
		<td><?PHP echo $compGVDiesel; ?></td>
	</tr>
 
<?PHP
}
?>
 
</table>

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now