Working with Multiple Content Sources in GoLive

It will happen. One day you'll encounter a situation where you need to manipulate data from multiple content sources, such as for an e-commerce site. While GoLive allows you to add multiple content sources with ease, extracting multiple records from one source (based on multiple records from another source) is beyond the scope of its current GUI. However, with some cutting, pasting, editing, and using nested loops, you can manipulate data from multiple content sources.

The following is a simplified, yet fairly typical data structure. There are three data tables: one containing client information, another product details, and the third containing the order details. Each is linked by common fields.

ClientID
Fname
Sname
City
OrdNum

OrdDate
OrdNum
ProdID
ProdQty
ClientID

ProdID
Description
Price

The order table contains information referenced from both the client table (ClientID and OrdNum) and from the product details (ProdID).

In this example, we're going to extract the required data from one content source and use that data to extract additional data from other content sources.

Begin by building a fairly standard reporting page such as the following. Add a content source that references the client data table, and then use a repeat rows object to build the report.

Figure 1. Building the report.

This will look like the picture below when viewed live.

Figure 2. How the report looks live.

So far, only data from the table named clients can be extracted. In order to get the data from a second table, another content source must be added. To do this, add another content source in the header, call it "listord," and set it to extract data from the orders table.

Figure 3. Adding the second content source.

There are now two data sources specified (listing and listord), but only one is being reported in the repeat rows object. Now what is needed is the first data source to provide the keys to extract the required information from the second data source. To do this, add a second repeat rows container inside the first. Then bind this to the "order" content source, and add the required fields.

Figure 4. Binding the order details.

Next, switch to source.

Locate the orders content source in the head section. It should look like the following:

<?php // GoLive Content Source
   $listord = WrapMySQLDatabaseResults("books", "select * 
   from orders", "block=1","listord");
?>

Select it, then cut it. Now scroll down, and find the clients repeat row container. Paste the second data source inside the first loop--specifically outside the loop for the order section, but inside the loop for the clients.

60  <tr>
61  <?php // GoLive Content Source
62    $listord = WrapMySQLDatabaseResults("books", "select * from orders",
   "block=1","listord");
63  ?>
64  
65  <?php $listord->MoveFirst();  ?>
66  <?php while (!$listord->EOF()) { ?>
67  <tr>
68    <td width="22%"><?php echo $listord->Value("prodid")?></td>
69    <td width="40%"></td>
70    <td width="17%"><?php echo $listord->Value("prodqty")?></td>
71    <td width="21%"></td>
72  </tr>
73  <?php $listord->MoveNext(); ?>
74  <?php } ?>

The order content source now needs to be altered to retrieve orders belonging to the active client in the loop. Add where clientID= '" .$listing->Value("clientID") . "'" to the select statement.

60  <tr>
61  <?php // GoLive Content Source
62    $listord = WrapMySQLDatabaseResults("books", "select * from orders
   where clientID= '" .$listing->Value("clientID") . "'", "block=1","listord");
63  ?>
64
65  <?php $listord->MoveFirst();  ?>

This will retrieve orders where the "clientID" equals that of the active client. It is extracting data from two content sources, and, in the case of the orders content source, it is matching the order transaction to the clientID. Therefore, each time the client loop cycles and extracts the information for the next client, it will also extract the order transactions that relates to the client.

When viewed live, the report will look like the following:

Figure 5. Report with order items added.

This can be further expanded. Add another content source in the header, bound it to the products table (naming it 'listprod'), and add a filter to match the requested parameter. Although that filter will not be used, it becomes easier to amend the search parameters if added now.

Figure 6. Adding the third content source.

Switch to source, and locate the code for the content source. It should look like the following:

<?php // GoLive Content Source
   $listprod = WrapMySQLDatabaseResults("books", "select * from products 
      where prodid = '" .   $GLOBALS["prodid"] . "'", "block=1","listprod");
?>

Select it, cut it, and paste it inside the 'listord' loop as shown below.

65  <?php $listord->MoveFirst();  ?>
66  <?php while (!$listord->EOF()) { ?>
67  <?php // GoLive Content Source
68    $listprod = WrapMySQLDatabaseResults("books", "select * from products 
    where prodid = '" .   $GLOBALS["prodid"] . "'", "block=1","listprod");
69  ?>
70  <tr>
71    <td width="22%"><?php echo $listord->Value("prodid")?></td>
72    <td width="40%"></td>
73    <td width="17%"><?php echo $listord->Value("prodqty")?></td>
74    <td width="21%"></td>
75  </tr>
76  <?php $listord->MoveNext(); ?>
77  <?php } ?>

Next, amend it to retrieve a product based on the product code in the listorder content source. Do this by replacing $GLOBALS["prodid"] with $listord->value("prodid"), which is the product id from the order table.

65  <?php $listord->MoveFirst();  ?>
66  <?php while (!$listord->EOF()) { ?>
67  <?php // GoLive Content Source
68    $listprod = WrapMySQLDatabaseResults("books", "select * from products 
    where prodid = '" .   $listord->value("prodid") . "'", "block=1","listprod");
69  ?>
70  <tr>

Switch back to layout mode, and add bindings for the product description and price.

Figure 7. Adding the product bindings.

The final report will look like the one below. It merges data from three tables. It produces a list of clients, which in turn lists order transactions belonging to that client, which in turn extracts product information from the product table.

Figure 8. The final report.

Wrapping Up

At first glance, it may appear that GoLive's Dynamic Content is not suitable for advanced applications. However, by using nested loops you can expand the capabilities of GoLive's Dynamic Content to extract information from many different data tables. This will allow you to build complex reports and present information in many different formats.

Give GoLive's Dynamic Content a second glance. With a little know-how, it can be more powerful than it initially appears.

Derry Thompson is currently co-owner of GloDerWorks with his partner Gloria Hansen. An active participant in the GoLive community and an avid Mac user, he can be found in various airports using his Titanium G4 Powerbook.

Return to the Web Development DevCenter.