[Pro] Looping db action

Legacy Dynamo
1

As usual when I have to use something new, I need to jump in at advanced level rather than take a stroll up the learning curve.

I need to write an action that will will duplicate a

, or possibly a tr> n times depending on database queries. Each
or tr> will have to contain a mix of calls for database content.

I’m not using anyone’s library. I will have to call php for each data item. The data is far too complex for any off the shelf solution.

I’ve been going through the docs and have made a couple of trivial actions. The one covering a repeated uses [Repeat] … [/Repeat] without saying what this represents, so I can’t get any further.

The docs imply that what I want to do is possible, but assume long familiarity with Action writing.

Where should I start looking?

David

.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

2

Well, you say you’re not using a library, but I would recommend that
you try my MyActiveRecord class for this. You can be up and running in
very few lines of code.

I just did an experimental update to the library that allows you to
get around the one and only requirement – that your unique ID is
named ‘id’. If you can post a quick schema of your data with the
columns you want to iterate over noted, I am sure that Todd or I could
give you the basics in one e-mail or two.

Walter

On Jun 7, 2010, at 2:32 PM, David Ledger wrote:

As usual when I have to use something new, I need to jump in at
advanced level rather than take a stroll up the learning curve.

I need to write an action that will will duplicate a

, or
possibly a tr> n times depending on database queries. Each
or
tr> will have to contain a mix of calls for database content.

I’m not using anyone’s library. I will have to call php for each
data item. The data is far too complex for any off the shelf solution.

I’ve been going through the docs and have made a couple of trivial
actions. The one covering a repeated uses [Repeat] … [/
Repeat] without saying what this represents, so I can’t get any
further.

The docs imply that what I want to do is possible, but assume long
familiarity with Action writing.

Where should I start looking?

David

.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

3

Here’s a complete example:

<?php
define('MYACTIVERECORD_CONNECTION_STR', 'mysql:// 
user:password@localhost/database_name');
require_once('MyActiveRecord.php');
class widgets extends MyActiveRecord{}
$widgets = MyActiveRecord::FindAll('widgets','available = 1','price  
DESC');
$out = '<table cellspacing="0">';
foreach($widgets as $widget){
	$out .= '<tr><td>' . $widget->name . '</td><td>' . $widget->price .  
'</td></tr>';
}
$out .= '</table>';
//print $out for your table
?>

Reading multiple tables is almost as easy. One-to-many, many-to-many
lookups, paging, etc. are all built in. This is meant to be the C in a
full MVC stack, but you don’t have to use it this way unless you
really need to build something major. One file included alongside your
custom logic (which is very few lines indeed) and you have a complete
data access system ready to go.

Walter

On Jun 7, 2010, at 2:48 PM, Walter Lee Davis wrote:

Well, you say you’re not using a library, but I would recommend that
you try my MyActiveRecord class for this. You can be up and running
in very few lines of code.

I just did an experimental update to the library that allows you to
get around the one and only requirement – that your unique ID is
named ‘id’. If you can post a quick schema of your data with the
columns you want to iterate over noted, I am sure that Todd or I
could give you the basics in one e-mail or two.

Walter

On Jun 7, 2010, at 2:32 PM, David Ledger wrote:

As usual when I have to use something new, I need to jump in at
advanced level rather than take a stroll up the learning curve.

I need to write an action that will will duplicate a

, or
possibly a tr> n times depending on database queries. Each
or
tr> will have to contain a mix of calls for database content.

I’m not using anyone’s library. I will have to call php for each
data item. The data is far too complex for any off the shelf
solution.

I’ve been going through the docs and have made a couple of trivial
actions. The one covering a repeated uses [Repeat] … [/
Repeat] without saying what this represents, so I can’t get any
further.

The docs imply that what I want to do is possible, but assume long
familiarity with Action writing.

Where should I start looking?

David

.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

4

Well, you say you’re not using a library, but I would recommend that
you try my MyActiveRecord class for this. You can be up and running
in very few lines of code.

I just did an experimental update to the library that allows you to
get around the one and only requirement – that your unique ID is
named ‘id’. If you can post a quick schema of your data with the
columns you want to iterate over noted, I am sure that Todd or I
could give you the basics in one e-mail or two.

Walter

On Jun 7, 2010, at 2:32 PM, David Ledger wrote:

I need to write an action that will will duplicate a

, or
possibly a tr> n times depending on database queries. Each
or
tr> will have to contain a mix of calls for database content.

I’m not using anyone’s library. I will have to call php for each
data item. The data is far too complex for any off the shelf
solution.

I’ve been going through the docs and have made a couple of trivial
actions. The one covering a repeated uses [Repeat] …
[/Repeat] without saying what this represents, so I can’t get any
further.

The docs imply that what I want to do is possible, but assume long
familiarity with Action writing.

Where should I start looking?

Thanks Walter.

It’s not the data extraction that escapes me, although I’ll have to
look more deeply into MyActiveRecord, it’s how to get multiple
products to appear in a Freeway page.

A ‘quick schema’ isn’t that simple. There are over 130 fields, but
the majority of these are to do with prices at different quantity
levels and the prices of optional extras - also at different quantity
levels.

The database was generated from a FileMaker database that had a lot
of repeated fields. It was exported as html and that was parsed into
mySQL. Because of that it’s not a well designed database for mySQL.
If I were a database expert designing it from scratch I’m sure I
wouldn’t end up with what I have.

Each row is a product, which may have options. There is a product
code field (Code), a product name (Name), a product description
(Description), a supplier (Supplier), a product range (Range), a
product finish (Finish), a pointer to a jpg, and a database index
(idx, like ‘id’), There are also several cost fields and their
corresponding price break quantities, and up to 18 options, each with
space for several costs and price breaks. Each option also has a type
that determines how information about is is to be extracted from the
database.

Options come in three main types. One is another product that is also
available by itself and has a db entry, but is normally supplied
along with the main product. The second is build-time option that
incurrs an extra cost. The third is a build-time option (or choice)
that has no extra cost.

Options that are also products in their own right have an option type
indicating a ‘link’ and consist of a name and the product code of
that product. Most of these were repeated option fields in the
original FileMaker that have been pulled out on their own.

Build-time no-cost options are included in the Finish field, which is
tinytext. They are specified in much the same way as CSS, and can be
parsed to give the name of a popup menu and the options within it.
Some of these will also have to trigger a floating pane illustrating
sub-options.

Cost options just populate one of the 18 sets of option fields. The
no-cost options are done differently because some records would
require over 200 sets of option field groups. That was the way things
were heading while it was still in FileMaker.

When showing one product, all the options will have to be extracted
and added to the page in various ways. This I can cope with (or
should be able to).

When showing a range of similar products, only the main fields of
Code, Name and the jpg need to be shown. But I need to show many on
one page. This I don’t see how to do.

The current website is a ‘cataloge’ website rather than the
‘Quotation’ website we are aiming for. It’s all Freeway, but hand
generated. Because it’s hand generated there are some
inconsistencies, but that’s not surprising for a site of this size.
It’s at http://www.kirkhouse.co.uk/. There isn’t even an early
version of the new one yet.

David

–
David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

5

Okay, well if you need to show multiple rows of these elements, the first thing to do is decide how you prefer to lay them out.

If you’re returning results in a table, this might be a row of the table. If you’re returning results as a set of nested DIVs, then you would lay out how one such would look, group it, and then apply the TemplateHelper Action to turn it into a Partial (a fragment of HTML+code that can be iterated multiple times).

In order to loop over database results in a table, you use the Extend button in the Inspector to mark one row of the table as a template. This row will be repeated as many times as you have records in the current result set.

Click into any cell in that row. In the Inspector, find the Extend button. Navigate within that dialog to the Before TR field, and insert the outer part of your loop:

<?php foreach($widgets as $widget){ ?>

Then move to the After /TR field, and add the end of the loop:

<?php } ?>

In the row itself, add the code to “play back” the particular attribute you need to show, within the appropriate cell of the table row. You can use a Markup item or the CrowBar Action (as an inline element) to insert this code:

<?= $widget->h('foo') ?>

You would add a different attribute within each cell, and then when the page is processed by the server, the current value of that field would be displayed. (The code above is using MyActiveRecord’s h() function to sanitize the database content for Web display. If you write this yourself, be sure to cover that step so your page doesn’t disappear halfway down the screen.)

If you are going the TemplateHelper route, then you would need to mark up the various places in your template element where you need text or images to appear, just as you would in the table layout. (Inside a template, you can get away with using just the variable name rather than the full object reference, so it would look like this: <?= $foo ?>.) Once you do that, you would use a function like this to repeat it on screen as many times as necessary:

class widget extends MyActiveRecord{
    //use the current object to render a partial file
    function render($strPartialName){
        if(file_exists('./' . $strPartialName)){
            ob_start();
            extract(array_map('htmlspecialchars',get_object_vars($this)));
            include('./' . $strPartialName);
            return ob_get_clean();
        }
    }
}

In Freeway, you would draw an HTML box where you wanted these elements to repeat, and then double-click inside it for a text cursor. Use CrowBar to add the following loop code all by itself on a line:

<?php 
    foreach $widgets as $widget {
        $widget->render('_widget.php');
    } 
?>

Set the outer HTML box to indeterminate height (so it can grow) and that is that. Each turn of the loop (iterating over an array of found objects) presents the next object to the function (or table row, if you went that route) and places that object’s current values into the template or row.

Walter

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

6

At 10:31 -0400 8/6/10, waltd wrote:

Click into any cell in that row. In the Inspector, find the Extend
button. Navigate within that dialog to the Before TR field, and
insert the outer part of your loop:

<?php foreach($widgets as $widget){ ?>

Then move to the After /TR field, and add the end of the loop:

<?php } ?>

I can’t get the Inspector to show the row, only either the table or
the cell. The reference manual says it’s possible as well, but
doesn’t say how. Latest FW Pro, latest Ref manual download.

If I click in the cell, I get the ‘overdrawn square’ in the
Inspector. That section has an Extend… button, but the pane it
produces is ‘Extended Attributes for ’. If I select the row with
the solid black right-pointing arrow I get the same. Same for a
column select arrow.

David

–
David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

7

Look in your inspector while you have the ‘overdrawn square’ tab showing. There are three segments to that pane, only one of which will be open by default. Open up Current Row, and click the Markup button. (Not Extend, my memory error here.)

Walter

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options