[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

PHP - Nested Repeat Region - two tables

Posted on 2007-12-03
8
Medium Priority
?
504 Views
Last Modified: 2013-12-12
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
Comment
Question by:nxpsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 14

Expert Comment

by:Vel Eous
ID: 20401372
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
 

Author Comment

by:nxpsupport
ID: 20401445
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
 

Author Comment

by:nxpsupport
ID: 20401471
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 14

Expert Comment

by:Vel Eous
ID: 20401861
>>  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
 

Author Comment

by:nxpsupport
ID: 20401893
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
 

Author Comment

by:nxpsupport
ID: 20401899
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
 

Author Comment

by:nxpsupport
ID: 20401927
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
 
LVL 14

Accepted Solution

by:
Vel Eous earned 1500 total points
ID: 20402322
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question