asked on

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

Vel Eous

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

nxpsupport

ASKER

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.

nxpsupport

ASKER

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?

Vel Eous

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

nxpsupport

ASKER

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

nxpsupport

ASKER

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)

nxpsupport

ASKER

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

ASKER CERTIFIED SOLUTION

Vel Eous

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial