Solved

PHP - Nested Repeat Region - two tables

Posted on 2007-12-03
8
485 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
  • 5
  • 3
8 Comments
 
LVL 14

Expert Comment

by:Tchuki
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
 
LVL 14

Expert Comment

by:Tchuki
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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:
Tchuki earned 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now