tbsForum - Topic - Dynamic table with calculated sum on rows and columns

By: Per Nils

Date: 2013-01-16

Time: 00:22

Dynamic table with calculated sum on rows and columns

Hi everyone ...

Background:

I'm a newbie to PHP and html but I'm working on project to display files from harvester and in the end make it possible to include price list from different saw mills so you can see the resault from different mills
I'm doing this in my spare time and it will be public to the comunity after it finished.

Anyway ... after spending hours on write my own function to display the tables I found my self that the most of the code includes just the "print to screen functions". Scanned the web and ended upp here. Reason .... From what I understand I can easily generate calc spreadsheet after I got the html version working.

I have read the manual a few times and search the forums. But I need a push in the right direction to make this.

Basily I got a array with record sets for each tree and stam. Each record contains alot of items but I have narrow it down so I will not confuse readers.

The "narrow down" record set could look like this (just the structure)

      $row_header = array('0' => 'row_h1', '1' => 'row_h2', '2' => 'row_h3');
      $col_header = array('0' => 'col_h1', '1' => 'col_h2', '2' => 'col_h3');

      $data= array();
      $data[] = array('0' => '1:1', '1' => '1:2', '2' => '1:3');
      $data[] = array('0' => '2:1', '1' => '2:2', '2' => '2:3');
      $data[] = array('0' => '3:1', '1' => '3:2', '2' => '3:3');

What I want is

        _________________________________________
        | X     | col_h1 | col_h2 | col_h3 | Sum |
        |----------------------------------------|
        |row_h1 |   1:1 | 1:2   | 1:3   | 21 |
        |----------------------------------------|
        |row_h2 |   2:1 | 2:2   | 2:3   | 45 |
        |----------------------------------------|
        |row_h3 |   3:1 | 3:2   | 3:3   | 21 |
        |----------------------------------------|
        | Sum   |   5    |   2    |   11   | tot |
        |----------------------------------------

1:1 and 1:2 is integers but just to make i clearer I have ref. it by row and column.

$row_header
$col_header

(Is headers for the column and rows)

Bonus is if TBS could sum each row .. and also sum each colum. (Sum both row and column to tot is not nesesary).

First... is the table structure right ? Or should I turn it around ? I mean define column first and expands from there ... Feel backwards but it's possible .

What I have managed so far is using

MergeField

But I lost when I come to table.

From a newbie I think it would be a good idea to expand the exampels. Forum tends over time to be to big to find your problem.

By: Skrol29

Date: 2013-01-16

Time: 12:14

Re: Dynamic table with calculated sum on rows and columns

Hi Per Nils,

Your table looks like the Dynalmic Colums example:
http://www.tinybutstrong.com/examples.php?e=dyncol&m=result

Basically you can do you table with this snippet (not tested)

<table>
<tr>
    <th>X</th>
    <th>[ch1.val;block=th]</th>
</tr>
<tr>
    <td>[rh.[d.$];noerr]</td>
    <td>[d.[ch2.key;block=td];block=tr]</td>
</tr>
</td>

PHP:

$TBS->MergeBlock('ch1,ch2', $col_header); // expand columns: merge blocks 'ch1' and 'ch2' with the simple associative array => virtual columns named "key" and "val"
$TBS->MergeBlock('d', $data); // merge rows, virtual column named "$" is the key of the record in the array.
$TBS->MergeField('rh', $row_header); // merge row headers, MergeBlock() is not available since rows are already merged.

Explanations:

By: Per Nils

Date: 2013-01-17

Time: 22:51

Re: Dynamic table with calculated sum on rows and columns

Have read the example several times .. but the problem was my data ..

My $row_header and $row_column looked like this

Array
(
    [0] => Array
        (
            [0] => 340
            [1] => 370
            [2] => 400
            [3] => 430
            [4] => 460
            [5] => 490
            [6] => 520
            [7] => 550
        )

)

Many thanks for pushing me in the right direction again. I was on the edge to abandon this tool.

But for a newbie like me it's a bit confusing in the beginning.

To make it more easy for newbies (and also to see if I got it right) ..I have written some examples in TBS for dummy's style.

====== MergeField with a simple array ======

With this code

$data=array('22'=> 23, 'd'=> 21);
$TBS->MergeField('r', $data);

Template exampel 1:

     <table>
        <tr>
            <td>[r.22;block=tr]</td>
         </tr>
        </table >

Resault:
23

Template exampel 2:

     <table>
        <tr>
            <td>[r.d;block=tr]</td>
         </tr>
        </table >

Resault:
21

r.d will be the same as echo $r['d'];

====== MergeBlock with a simple array ======
With this code

$data=array('22'=> 23, 'd'=> 21);
$TBS->MergeBlock('r', $data);

Template exampel 1:

     <table>
        <tr>
            <td>[r.val;block=tr]</td>
         </tr>
        </table >

Resault:
23
21

Template exampel 2:

     <table>
        <tr>
            <td>[r.key;block=tr]</td>
         </tr>
        </table >

Resault:
22
d

Template exampel 3:

     <table>
        <tr>
            <td>[r.#;block=tr]</td>
         </tr>
        </table >

Resault:
1
2

Template exampel 4:

     <table>
        <tr>
            <td>[r.$;block=tr]</td>
         </tr>
        </table >

Resault:
22
d

the .val => is the fields value

the .key => is the key

the .$ => is the virtual column and in this case it will return the same as above

the .# => is the number in order (1 = first time 2 = the second time and so on)

====== MergeBlock with a 2d array (table) ======
Our table structure look like this

$data[]=array('0'=> 23, '1'=> 21, '2'=> 1);
$data[]=array('0'=> 12, '1'=> 10, '2'=> 6);
$data[]=array('0'=> 89, '1'=> 33, '2'=> 7);

So with ..

$TBS->MergeBlock('d',$data);

Whit template like this ..

<table style="text-align: left; width: 100%;" border="1" cellpadding="2"
cellspacing="2">
        <tbody>
        <tr>
             <td>[d.0;block=tr] </td>
        </tr>
        </tbody>
</table>

Will show ..

23
12
89

As expected d.0 will show the first column, d.1 will show the second and so on.
To get hole table we have to find a way to expand the d. from the first row key to the last row key value.

This mean that we have do like this.

$data[]=array('0'=> 23, '1'=> 21, '2'=> 1);
$data[]=array('0'=> 12, '1'=> 10, '2'=> 6);
$data[]=array('0'=> 89, '1'=> 33, '2'=> 7);

// Used to expand the columns (eg. d.0 d.1 d.2 ....)
$TBS->MergeBlock('row',$data[0]);

$TBS->MergeBlock('data',$data);

** Notice ** you must do it in the right order. It will not work with $TBS->MergeBlock('data',$data); before $TBS->MergeBlock('row',$data[0]); ..

In our template we have to include the colum expansion

<table style="text-align: left; width: 100%;" border="1" cellpadding="2"
cellspacing="2">
        <tbody>
        <tr>
             <td> [d.[row.key;block=td];block=tr] </td>
        </tr>
        </tbody>
</table>

And now we get the hole table

23 21 1
12 10 6
89 33 2

****************************************************************************
If we go back to topic.

The row sum and the column sum ... Is it possible or have I to do it on php side ?
(In my case I have to do it anyway because I have decided it will make sence to also show the % of each row based on the sum of all rows. The same with columns.

But just to cover the topic for other readers ....

****************************************************************************
PS.

It's nice and clean webpage.. but I'm missing the function for edit your post.
Maybe not so big deal but It could be handy to be able refrace you sentence or fix other kinds of typo error.

Anohter thing that could be handy is a open wiki. I'm guessing that you already had answer this kind of question several times before.
And with a open wiki the comunity could fill in their own tips and snippets.

My rekomendation for wiki is DokuWiki. Just the reason it's a flat file wiki no fiddling with database. It have also syntas highlightning for code.

It's also realtiv easy to write your own plugin. Even a newbie like me ...have manage to do that.

DS.

By: Skrol29

Date: 2013-01-17

Time: 23:36

Re: Dynamic table with calculated sum on rows and columns

Thanks for your example.

> It's nice and clean webpage.. but I'm missing the function for edit your post.

That is in the left side of your message, but it is has a limited duration for editing a message.

> Just the reason it's a flat file wiki no fiddling with database. It have also syntas highlightning for code.

I need to populate the team, or I'll do the wiki myself after the next package of updates.
I'll have a look at DokuWiki.

By: Per Nils

Date: 2013-01-18

Time: 14:09

Re: Dynamic table with calculated sum on rows and columns

>> It's nice and clean webpage.. but I'm missing the function for edit your post.

> That is in the left side of your message, but it is has a limited duration for editing a message.

Nope I only have (if I'm not blind)

>Replay to post
>Get email
>Get direct url

Could it be that you are login as admin ?

Another thing that will improve this forum if you could change

Then the handle in the right bottom corner will be visible and you get a bigger area to do your post as default

> I need to populate the team, or I'll do the wiki myself after the next package of updates.
> I'll have a look at DokuWiki.

Why reinvent the wheel again ? You have all you gona need in Dokuwiki.
But if you like the challange go for it.

One thing that could be a issue is that it has <code> for code syntax.
Could be confusing for users and also you will not be able to copy and paste between the forum and wiki
But the best solution is to change it on the forum instead to do it other way around.

Then you will not break Dokuwiki's syntax manual.

https://www.dokuwiki.org/wiki:syntax

******************** For answering the last part of the topic ************************************

To sumerize each row or column you have to it your self on the php side.
(to my knowledge ....)

By: Per Nils

Date: 2013-01-18

Time: 14:12

Re: Dynamic table with calculated sum on rows and columns

Hmm I was blind after all ....

Now I can see

>Edit your message

By: Per Nils

Date: 2013-01-19

Time: 20:51

Re: Dynamic table with calculated sum on rows and columns

====== How to remove a table on a condition ======

I had a hard time to nail this down so for other dummy's like me ..

so here comes it in TBS for dummy's style....
(Ignore tags <code php> and </code> it's dokuwikis syntax for code block.
And I'm using that for writing due to it's syntax highlighting and some other nice features.)

There is a parameter called nodata but it can't be used in this case.
For more info about nodata check this section in the manual
[[http://www.tinybutstrong.com/manual.php#html_block_section]].

*************************************************************

So let's begin ..

<code php>

      $template = <<<EOT
      <table> border="1" cellpadding="5" cellspacing="0" >
      <tr>
        <td> Our Table: </td>
      </tr>
      <tr>
        <td>[d.val;block=tr;]</td>
      </tr>
      </table>
EOT;
        $data = array('0' => 'bla', '1' => '');
        $TBS = new clsTinyButStrong;
        $TBS->Source = $template;
        $TBS->MergeBlock('d', $data);
        $TBS->Show();

</code>

This will give us

<code>

Our Table:
bla
<- and a empty cell... in html it look's like <td></td>

</code>

But how do we do for getting rid of that empty cell ?

===== magnet (is for MergeField) =====

Let's investigate the parameter magnet.

Whit this code

<code php>

        $template = <<<EOT
      <table border="1" cellpadding="5" cellspacing="0" >
      <tr>
        <td> Our Table: </td>
      </tr>
      <tr>
        <td>[d.0;block=tr;magnet=tr]</td>
      </tr>
      </table>
EOT;
        $data = array('0' => 'bla', '1' => "");
        $TBS = new clsTinyButStrong;
        $TBS->Source = $template;
        $TBS->MergeField('d', $data);
        $TBS->Show();

</code>

As expected ...

<code>

Our Table:
bla

</code>

But if we choose the second key

<code php>

        $template = <<<EOT
      <table border="1" cellpadding="5" cellspacing="0" >
      <tr>
        <td> Our Table: </td>
      </tr>
      <tr>
        <td>[d.1;block=tr;magnet=tr]</td>
      </tr>
      </table>
EOT;
        $data = array('0' => 'bla', '1' => "");
        $TBS = new clsTinyButStrong;
        $TBS->Source = $template;
        $TBS->MergeField('d', $data);
        $TBS->Show();

</code>

You get only the header (Our Table:)

<code>

Our Table:

</code>

Aha but what happens if we change to magnet=table ?

Let's try ...

<code php>

        $template = <<<EOT
      <table border="1" cellpadding="5" cellspacing="0" >
      <tr>
        <td> Our Table: </td>
      </tr>
      <tr>
        <td>[d.1;block=tr;magnet=table]</td>
      </tr>
      </table>
EOT;
        $data = array('0' => 'bla', '1' => "");
        $TBS = new clsTinyButStrong;
        $TBS->Source = $template;
        $TBS->MergeField('d', $data);
        $TBS->Show();

</code>

Yep our table is gone..

Could that be so easy ? ..

For MergeField it will work for you.

Let's consult the manual

magnet=tag
or
magnet=expr
or
magnet=#

Assign a magnet XML/HTML zone to the TBS field. A magnet tag is kept as is when the field has a value, and is deleted when the field is null or empty string.

Parameter magnet supports the same syntax as parameter block, i.e. that expr must be an XML/HTML tag or a TBS extended block expression.
It also support the syntax magnet=# which means that the magnet zone is the HTML/HTML attribute wherein the TBS field is embedded.

If the value is empty the magnet "will take away" the tag and it's surroundings.

But what about MergeBlock ? Then you have to use bmagnet

===== bmagnet (is for MergeBlock) =====

So let's try ...

<code php>

        $template = <<<EOT
      <table border="1" cellpadding="5" cellspacing="0" >
      <tr>
        <td> Our Table: </td>
      </tr>
      <tr>
        <td>[d.val;block=tr;magnet=table]</td>
      </tr>
      </table>
EOT;
        $data = array('0' => 'bla', '1' => "");
        $TBS = new clsTinyButStrong;
        $TBS->Source = $template;
        $TBS->MergeBlock('d', $data);
        $TBS->Show();

</code>

Nop our table is still there (dam).

<code>

Our Table:
bla
<- empty cell In html <td></td>

</code>

So what's going on then ?

We consult the manual again.

<code>

Indicates an XML/HTML zone which must be deleted if the block is merged with no record
(an emprt query, for example, or a PHP Array with no items).

</code>

Aahh.. our $data is not empty so it will not be caught by the bmagnet.

How do we do then ?

The solution I have found is like this ..

<code>

        $template = <<<EOT
<div id="Our_Table">
      <table border="1" cellpadding="5" cellspacing="0" >
      <tr>
        <td> Our Table: </td>
      </tr>
      <tr>
        <td>[d.val;block=tr]</td>
      </tr>
      </table>
        [onshow;block=div;when [show]=1]
</div>

EOT;
        $show = true;
        $data = array('0' => 'bla', '1' => '');
        $TBS = new clsTinyButStrong;
        $TBS->Source = $template;
        $TBS->MergeBlock('d', $data);
        $TBS->MergeField('show', $show);
        $TBS->Show();

</code>

First I have surrounded the table by a <div> tag

Second I have introduce a new variable -> $show that I merge with MergeField.
(Notice that true will be 1 and false will be '' in TBS.)

Then I added the line [onshow;block=div;when [show]=1] inside the div tags.

I don't know exactly how it should be read but it must be something like ...

onshow .. the block surrounded by the tag div is valid when [show] =1 .. if not remove the block (section / part or whatever).

********************************************************************************

Something missing or wrong or invalid please post the right answer.

Hopes this gonna help others to get into this tool.

By: Skrol29

Date: 2013-01-19

Time: 23:37

Re: Dynamic table with calculated sum on rows and columns

Thanks :)

By: Per Nils

Date: 2013-08-29

Time: 23:02

Re: Dynamic table with calculated sum on rows and columns

After a longer break I'm back on my project. I will have a lot of reading and testing to get familiarly again with php html css TBS etc … But there is something that I didn't manage in my first attempt. (I sort of solved it)

The problem I was facing was to show 3 level deep array.

(Having lot of to read and learn again so I must simplifying some stuff).

I'm having a dataset sort of like this …

$data[0] => Array
        (
            ['title'] => 'bla'
            ['author'] => 'bla'
            ['table'] => 2 dimension array
        )

$data[1] => Array
        (
            ['title'] => 'bla'
            ['author'] => 'bla'
            ['table'] => 2 dimension array
        )
     etc ...
)

For the HTML view I just …

$TBS2 = new clsTinyButStrong;
$TBS2->LoadTemplate('tpl_stocknota.html');
$template = $TBS2->Source;

for ($i = 0; $i <count($data); $i++) {
.....
.....

$TBS2->Source = $template;

$TBS2->MergeField ...
$TBS2->MergeBlock ...
......
echo $TBS2->Source;
}

Is it possible to do this whit out the for loop ?

If not it will be a problem (I think) when it comes do the odt, excel etc …

I have found this link http://www.tinybutstrong.com/forum.php?thr=3095 but haven't digged so deep into it yet.

(hmm I'm feeling a bit lazy now … shouting before investigate all search hits ….) Anyway I take my chances..

By: Skrol29

Date: 2013-08-31

Time: 01:51

Re: Dynamic table with calculated sum on rows and columns

Hi,

Yes you can do something like this. But the block and field's names would must have a diffrent name for each loop. That is you must put $i in the name of the field t merge other with the first $TBS2->MergeField() will merge all.

But I don't understand why you have t do this because it is quite like a sub-block feature.

By: Per Nils

Date: 2013-09-01

Time: 23:50

Re: Dynamic table with calculated sum on rows and columns

The above was a bit confusing so I start again .......

I have scanned and read several threads but my head seems to be for slow for this.

Anyway my data should look similar to this ..

</code>
(Perhaps I could remove one level.. but for the time being I think it will suite my needs better with this extra level.)

First issue is to add headers to the table. You have given me the code for it ... (hmm I don't quit get all the steps right now at this point but it doesn't matter)

<code>
foreach ($data as $tmp) {
     for ($i = 0; $i < count($tmp); $i++) {
        $info['title']= $tmp['title'];
        $info['author']= $tmp['author'];

        $colh = $tmp[$i]['table_col_header'];
        $rowh = $tmp[$i]['table_row_header'];

        $TBS2->Source = $template;

        $TBS2->MergeField('info', $info);
        $TBS2->MergeBlock('colh1,colh2', $colh);
        $TBS2->MergeBlock('data', $data);
        $TBS2->MergeField('rowh', $rowh);

        echo $TBS2->Source;
}

</code>

So instead I'm thinking of rearrange my data so the table row_header and col_header is already in the table so to speak. The main drawback whit this is I think would be problem to have different fonts on the header and the data its self if not css could do that.

So just with one big

$TBS2->MergeBlock('data', $data);

I'm having problem to "unwind" it so to speek.

Have been fiddling with this

<code>
<level_1> /* placeholder to the top level */

[data.$;block=main;sub1]

<level_2>

    [data_sub1.$;block=level_2;sub2]

    *******
      What will the syntax for next sub level be ? data_sub1_sub2 ?
    *******

</level_2>

</level_1>

</code>

Or is this the wrong way? Is't better to do as you have done in this thread http://www.tinybutstrong.com/forum.php?thr=1449

There is also some similar in this http://www.tinybutstrong.com/forum.php?thr=2987

What I could think of is when you get back to your app after some times it could be confusing if you have introduced other variables than exist in you data. In this case we have rowh and colh. Hmm having a strong feeling that this is just me ...

... back to reading and testing ....

By: Skrol29

Date: 2013-09-03

Time: 00:31

Re: Dynamic table with calculated sum on rows and columns

Hi Per Nils,

According to your data snippet, I'm suprised that you "foreach" snippet is working.

Anyway, my suggestion is that you should build another data array dedicated to the merge with a simpler structure.
Then the debugging will be easier in both PHP and Template sides.
And you will not need to use TBS with an acrobatic way.

The structure should be like this :

$new_data = Array(
   [0] => Array (
       ['title'] => 'bla',
       ['author'] => 'bla',
       ['row_header'] => 'row1',
       ['col_header_1'] => '1:1',
       ['col_header_2'] => '1:2',
       ['col_header_3'] => '1:3',
       ....
   ),
   [1] => Array (
       ['title'] => 'bla',
       ['author'] => 'bla',
       ['row_header'] => 'row2',
       ['col_header_1'] => '2:1',
       ['col_header_2'] => '2:2',
       ['col_header_3'] => '2:3',
       ....
   )

All you need for that is to loop on the data a first time in order to establish the complete list of possible columns ('col_header_1', ''col_header_2', 'col_header_3', ...).
Then loop a second time in order to build the expected $new_data array. Knowing that each row is supposed to have all the columns 'col_header_1', ''col_header_2', 'col_header_3'.
If a column is missing in the original $data, then let it empty in the new one.

With this new structure, it will be easy to make headers, footers and totals.

By: Per Nils

Date: 2013-09-05

Time: 00:14

Re: Dynamic table with calculated sum on rows and columns

So after some reading I discovered I have got it all wrong. (As usually…)

In my head the procedure was that you build your data in nested array with the tools MergeField and MergeBlock in some convoluted way. After that was done TBS started to unwind that array with help of TBS script looking commands in the template.

Wrong.. (double face palm) … TBS is change'ing your template on the fly when it hits MergeFiled or MergeBlock in you php code.

So for me and other we take some examples.

Let's start with the simplest form of MergeBlock
MergeBlock

// Our array
$Arr[]= 'A';
$Arr[]= 'B';
$Arr[]= 'C';
$Arr[]= 'D';

$template = <<<EOT
<ul>
    [the_array.val]
</ul>
EOT;
     $TBS2 = new clsTinyButStrong;
     $TBS2->Source = $template;
     $TBS2->MergeBlock('the_array', $Arr);
     $TBS2->Show();

Result:

A

Lets tied it to a html tag —> [the_array.val;block=ul]

Result:

A

B

C

D

So it will multiply/copy everything between your chosen xml/html tag. In my case it was <ul> (you don't have to add the < and > stuff).In same run it will also replace it with the corresponding pos in the array. (huh.. did someone understand that sentence?)

So lets investigate some other things that could be handy. This is going to get ugly.
Ugly

We have our nested dataset

// group 1 record 1
$data[0][0]['title']='TBS Manual';
$data[0][0]['author']='TBS Manual writer';
// headers
$data[0][0]['colheader']= array('0' => 'colh_A', '1' =>'colh_B') ;
$data[0][0]['rowheader']= array('0' => 'rowh_1', '1' =>'rowh_2') ;
// data
$data[0][0]['dataRows'][0] = array('0' => 'row_data1A', '1' => 'row_data1B');
$data[0][0]['dataRows'][1] = array('0' => 'row_data2A', '1' => 'row_data2B');

// group 1 record 2
$data[0][2]['title']='Hunsley';
$data[0][2]['author']='Hunsley author';
// headers
$data[0][2]['colheader']= array('0' => 'colh_1A', '1' =>'colh_1B') ;
$data[0][2]['rowheader']= array('0' => 'rowh_11', '1' =>'rowh_12') ;
// data
$data[0][2]['dataRows'][0] = array('0' => 'row_data11A', '1' => 'row_data11B');
$data[0][2]['dataRows'][1] = array('0' => 'row_data12A', '1' => 'row_data12B');

// group 2 record 1
$data[2][0]['title']='Php';
$data[2][0]['author']='Php writer';
// headers
$data[2][0]['colheader']= array('0' => 'colh_2A', '1' =>'colh_2B') ;
$data[2][0]['rowheader']= array('0' => 'rowh_21', '1' =>'rowh_22') ;
// data
$data[2][0]['dataRows'][0] = array('0' => 'row_data21A', '1' => 'row_data21B');
$data[2][0]['dataRows'][1] = array('0' => 'row_data22A', '1' => 'row_data22B');

Starting with just …

$template = <<<EOT
<Top_Level>
   group : [top.$;block=Top_Level] <br>   
</Top_Level>
EOT;
        $TBS2 = new clsTinyButStrong;
        $TBS2->Source = $template;
        $TBS2->MergeBlock('top', $data);
        echo $TBS2->Source;

Result:

group : 0

group : 2

So we get the first level of our data set but how do get to next one ..

We have to use something that is called Subblocks with dynamic queries:

Maybe read this first http://www.tinybutstrong.com/manual.php#html_block_subblock

With that done lets walk in to the next level ..
Next level

$template = <<<EOT
<Top_Level>
    [top;block=Top_Level]   
<Level_2>
     Title :   [Lev2.title;block=Level_2;p1=[top.$]] <br>
     Author : [Lev2.author] <br>
</Level_2>
</Top_Level>
EOT;

        $TBS2 = new clsTinyButStrong;
        $TBS2->Source = $template;
        $TBS2->MergeBlock('top', $data);
        $TBS2->MergeBlock('Lev2', 'array' , 'data[%p1%]');
        echo $TBS2->Source;

Result:

Title : TBS Manual

Author : TBS Manual writer

Title : Hunsley

Author : Hunsley author

Title : Php

Author : Php writer

So TBS will multiply the stuff between <Top_Level> ….. </Top_Level> so many times the are items in our first/top level in the first $TBS2→MergeBlock('top', $data);.

When that is done it continue with the next MergeBlock and extend/multiply in every <Top_Level> … </Top_Level> block the stuff between the tags <Level_2> …. </Level_2> and also replace Lev2.title and Lev2.author with stuff from our dataset from it's keys $data[x][x]['title] and the same with author.

I have replaced [top.$;block=Top_Level] with [top;block=Top_Level] so it wouldn't spit out the current key.
The table

So in our dataset we have the headers for columns and rows in a separate array. So we are reusing Skrol29 post of doing this (long way up).

Starting slowly …

Result:

So we have expand the column headers …

Moving on ..

$template = <<<EOT
<Top_Level>
    [top;block=Top_Level]   
<Level_2>
     Title :   [Lev2.title;block=Level_2;p1=[top.$]] <br>
     Author : [Lev2.author] <br>

     <table style="text-align: left; width: 100%;" border="1" cellpadding="2" cellspacing="2">
     <tr>
         <th> X </th>
         <th> [ch1.val;block=th;p1=[top.$];p2=[Lev2.$]] </th>
     </tr>
     <tr>
         <td> dummy </td>
         <td> [dRows.[ch1.key;block=td;p1=[top.$];p2=[Lev2.$]];block=tr;p1=[top.$];p2=[Lev2.$]] </td>
     </tr>
     </table>
</Level_2>
</Top_Level>
EOT;

        $TBS2 = new clsTinyButStrong;
        $TBS2->Source = $template;
        $TBS2->MergeBlock('top', $data);
        $TBS2->MergeBlock('Lev2', 'array' , 'data[%p1%]');
        $TBS2->MergeBlock('ch1,ch2', 'array' , "data[%p1%][%p2%][colheader]");
        $TBS2->MergeBlock('dRows', 'array' , 'data[%p1%][%p2%][dataRows]');
        echo $TBS2->Source;

Expanding the table with a dummy for row header for now.

Result:

But now comes a problem when we want to fix the row header (I haven't solved it). The table have now been fully expanded so just have to replace every dummy with value from the array with the key 'rowheader'.

Single item means MergeField instead of MergeBlock.

So …

$template = <<<EOT
<Top_Level>
    [top;block=Top_Level]   
<Level_2>
     Title :   [Lev2.title;block=Level_2;p1=[top.$]] <br>
     Author : [Lev2.author] <br>

     <table style="text-align: left; width: 100%;" border="1" cellpadding="2" cellspacing="2">
     <tr>
         <th> X </th>
         <th> [ch1.val;block=th;p1=[top.$];p2=[Lev2.$]] </th>
     </tr>
     <tr>
         <td> [rh.[dRows.$;p1=[top.$];p2=[Lev2.$]];p1=[top.$];p2=[Lev2.$]] </td>
         <td> [dRows.[ch1.key;block=td;p1=[top.$];p2=[Lev2.$]];block=tr;p1=[top.$];p2=[Lev2.$]] </td>
     </tr>
   </table>
</Level_2>
</Top_Level>
EOT;

        $TBS2 = new clsTinyButStrong;
        $TBS2->Source = $template;
        $TBS2->MergeBlock('top', $data);
        $TBS2->MergeBlock('Lev2', 'array' , 'data[%p1%]');
        $TBS2->MergeBlock('ch1,ch2', 'array' , "data[%p1%][%p2%][colheader]");
        $TBS2->MergeBlock('dRows', 'array' , 'data[%p1%][%p2%][dataRows]');
        $TBS2->MergeField('rh', 'array' , "data[%p1%][%p2%][rowheader]");
        echo $TBS2->Source;

Result:

MergeField don't have the paramter option from what I can understand so it seems we have hit the end of the road.

So in my case I have to flaten my dataset to the top level as suggested by Skrol29. Or do you have some other way to attack this?

But the template starting to look very ugly ...

By: Per Nils

Date: 2013-09-05

Time: 20:28

Re: Dynamic table with calculated sum on rows and columns

So I have flatten my data but will exists 1 level down. For a html page you could foreach and Merge and echo it out to the page.

My idea is also to produce a .doc file so I need a one file template solution (I think).

Skrol29 do you have some idea on this. From what I understand in your last post you thought that I have a fixed table in columns and rows but that is not the case.

I have included a ready to copy and paste testing code ..

// dataset 1
$data[0]['title']='TBS Manual';
$data[0]['author']='TBS Manual writer';
// headers
$data[0]['colheader']= array('0' => 'colh_A', '1' =>'colh_B') ;
$data[0]['rowheader']= array('0' => 'rowh_1', '1' =>'rowh_2') ;
// data
$data[0]['dataRows'][0] = array('0' => 'row_data1A', '1' => 'row_data1B');
$data[0]['dataRows'][1] = array('0' => 'row_data2A', '1' => 'row_data2B');

// dataset 2
$data[1]['title']='Hunsley';
$data[1]['author']='Hunsley author';
// headers
$data[1]['colheader']= array('0' => 'colh_1A', '1' =>'colh_1B') ;
$data[1]['rowheader']= array('0' => 'rowh_11', '1' =>'rowh_12') ;
// data
$data[1]['dataRows'][0] = array('0' => 'row_data11A', '1' => 'row_data11B');
$data[1]['dataRows'][1] = array('0' => 'row_data12A', '1' => 'row_data12B');

// dataset 3
$data[2]['title']='Php';
$data[2]['author']='Php writer';
// headers
$data[2]['colheader']= array('0' => 'colh_2A', '1' =>'colh_2B') ;
$data[2]['rowheader']= array('0' => 'rowh_21', '1' =>'rowh_22') ;
// data
$data[2]['dataRows'][0] = array('0' => 'row_data21A', '1' => 'row_data21B');
$data[2]['dataRows'][1] = array('0' => 'row_data22A', '1' => 'row_data22B');

$template = <<<EOT
<Top_Level>
   dataset : [top.$;block=Top_Level] <br>   
</Top_Level>
EOT;
        $TBS2 = new clsTinyButStrong;
        $TBS2->Source = $template;
        $TBS2->MergeBlock('top', $data);
        echo $TBS2->Source;

Hmm .. it seems that the only solution is to include the headers in the data before merge'ing into the template. Then I can use Mergeblock will leads to be able to use Subblocks with dynamic queries.

Have also tested with subblocks

// dataset 1
$data[0]['title']='TBS Manual';
$data[0]['author']='TBS Manual writer';
// headers
$data[0]['colheader']= array('0' => 'colh_A', '1' =>'colh_B') ;
$data[0]['rowheader']= array('0' => 'rowh_1', '1' =>'rowh_2') ;
// data
$data[0]['dataRows'][0] = array('0' => 'row_data1A', '1' => 'row_data1B');
$data[0]['dataRows'][1] = array('0' => 'row_data2A', '1' => 'row_data2B');

// dataset 2
$data[1]['title']='Hunsley';
$data[1]['author']='Hunsley author';
// headers
$data[1]['colheader']= array('0' => 'colh_1A', '1' =>'colh_1B') ;
$data[1]['rowheader']= array('0' => 'rowh_11', '1' =>'rowh_12') ;
// data
$data[1]['dataRows'][0] = array('0' => 'row_data11A', '1' => 'row_data11B');
$data[1]['dataRows'][1] = array('0' => 'row_data12A', '1' => 'row_data12B');

// dataset 3
$data[2]['title']='Php';
$data[2]['author']='Php writer';
// headers
$data[2]['colheader']= array('0' => 'colh_2A', '1' =>'colh_2B') ;
$data[2]['rowheader']= array('0' => 'rowh_21', '1' =>'rowh_22') ;
// data
$data[2]['dataRows'][0] = array('0' => 'row_data21A', '1' => 'row_data21B');
$data[2]['dataRows'][1] = array('0' => 'row_data22A', '1' => 'row_data22B');

$template = <<<EOT
<Top_Level>
   dataset : [top.$;block=Top_Level;sub1=colheader;sub2=rowheader;sub3=dataRows] <br>
   Title : [top.title] <br>
   Author : [top.author]
<table style="text-align: left; width: 100%;" border="1" cellpadding="2" cellspacing="2">
<tr>
      <th> X </th>
      <th> [top_sub1.val;block=th] </th>
</tr>
<tr>
      <td> [top_sub2.val;block=tr] </td>
      <td> [what do we put in here ?] </td>
</tr>
</table>
</Top_Level>
EOT;
        $TBS2 = new clsTinyButStrong;
        $TBS2->Source = $template;
        $TBS2->MergeBlock('top', $data);
        echo $TBS2->Source;

But also here I get to a halt.

Result for those who wonder how it turned out ....

By: Per Nils

Date: 2013-09-05

Time: 21:37

Re: Dynamic table with calculated sum on rows and columns

Skrol29

Is there any chance that you could make a snippet on your idea ?

Don't quite follow you...

By: Skrol29

Date: 2013-09-08

Time: 00:43

Re: Dynamic table with calculated sum on rows and columns

> Is there any chance that you could make a snippet on your idea ?

Ok but can you precise again what is the data you what to start with, and what is the result you whish to have.
I'm a bit lost between the several snippets of structured data and what you truid to display.

By: Per Nils

Date: 2013-09-08

Time: 20:58

Re: Dynamic table with calculated sum on rows and columns

So I'm aiming to visualize the data as shown bellow. The row_tot and col_tot I can do on the php side.

I don't know how many columns and rows my table are but they shouldn't be more than 15*15.

Bellow I have included a copy and paste ready example code.

// dataset 1
$data[0]['title']='TBS Manual';
$data[0]['author']='TBS Manual writer';
// headers
$data[0]['colheader']= array('0' => 'colh_A', '1' =>'colh_B', '2' =>'row_tot') ;
$data[0]['rowheader']= array('0' => 'rowh_1', '1' =>'rowh_2', '2' =>'col_tot') ;
// totals
$data[0]['Row_tot'] = array('0' => 'tot_row_1', '1' => 'tot_row_2');
$data[0]['Col_tot'] = array('0' => 'tot_col_A', '1' => 'tot_col_B');

// data
$data[0]['dataRows'][0] = array('0' => 'row_data1A', '1' => 'row_data1B');
$data[0]['dataRows'][1] = array('0' => 'row_data2A', '1' => 'row_data2B');

// dataset 2
$data[1]['title']='Hunsley';
$data[1]['author']='Hunsley author';
// headers
$data[1]['colheader']= array('0' => 'colh_1A', '1' =>'colh_1B, '2' =>'row_tot'') ;
$data[1]['rowheader']= array('0' => 'rowh_11', '1' =>'rowh_12', '2' =>'col_tot') ;
// totals
$data[1]['Row_tot'] = array('0' => 'tot_row_1', '1' => 'tot_row_2');
$data[1]['Col_tot'] = array('0' => 'tot_col_A', '1' => 'tot_col_B');

// data
$data[1]['dataRows'][0] = array('0' => 'row_data11A', '1' => 'row_data11B');
$data[1]['dataRows'][1] = array('0' => 'row_data12A', '1' => 'row_data12B');

$template = <<<EOT
<Top_Level>
   dataset : [top.$;block=Top_Level] <br>   
</Top_Level>
EOT;
        $TBS2 = new clsTinyButStrong;
        $TBS2->Source = $template;
        $TBS2->MergeBlock('top', $data);
        echo $TBS2->Source;

I have a solution for html with a for loop but that will not translate well into OpenTBS. I want to give the user the ability to download a writer or spreadsheet file.

For spreadsheet maybe I can do csv file the user to import.

By: Skrol29

Date: 2013-09-08

Time: 23:46

Re: Dynamic table with calculated sum on rows and columns

Hi Per Nils,

Here is a solution that should work. It only consists in a building a new data structure from your data, and then use a convenient snippet of template.
Comments are after.

// New data structure
$data2 = array();
foreach ($data as $tbl) {

    $col_lst = array();

    // Data Columns (without Headers and Totals);
    $col_nbr = count($tbl['colheader']) - 1;
    for ($i = 0 ; $i < $col_nbr ; $i++) {
        $col_lst[] = array(
            'id'=> 'c'.$i,
            'caption'=> $tbl['colheader'][$i],
            'total'=> $tbl['Col_tot'][$i],
        );
    }
    $totcol_caption = $tbl['colheader'][$col_nbr];
    $totrow_caption = $tbl['rowheader'][$col_nbr];

    // Data Records (without Footers)
    foreach ($tbl['dataRows'] as $r => $rec) {
        $x[$r] = array(
            'header' => $tbl['rowheader'][$r],
            'total' => $tbl['Row_tot'][$r],
        );
        foreach ($col_lst as $c => $col) {
            $x[$r][$col['id']] = $tbl['dataRows'][$r][$c];
        }
    }

    $tbl2 = array(
        'title' => $tbl['title'],
        'author' => $tbl['author'],
        'col_lst' => $col_lst,
        'data' => $x,
        'totcol_caption' => $totcol_caption,
        'totrow_caption' => $totrow_caption,
        'totrow_total' => '.',
    );

    $data2[] = $tbl2;

}

$template = <<<EOT
<div style="border: 1px; border-style: solid; border-color: blue; margin: 4px;">
    dataset : [top.$;block=div;sub1=col_lst;sub2=col_lst;sub3=col_lst;sub4=data] <br>
    title : [top.title] <br>
    author : [top.author] <br>
    <table border="1">
        <tr>
            <td>X</td>
            <td>[top_sub1.caption;block=td]</td>
            <td>[top.totcol_caption]</td>
        </tr>
        <tr>
            <td>[top_sub4.header;block=tr]</td>
            <td>[top_sub4.[top_sub2.id;block=td]]</td>
            <td>[top_sub4.total]</td>
        </tr>
        <tr>
            <td>[top.totrow_caption]</td>
            <td>[top_sub3.total;block=td]</td>
            <td>[top.totrow_total]</td>
        </tr>
    </table>
</div>
EOT;

$TBS2 = new clsTinyButStrong;
$TBS2->Source = $template;
$TBS2->MergeBlock('top', $data2);
echo $TBS2->Source;

Please note that the order of sub-blocks is important: sub4 can be merged only after sub2.
Sub1, sub2 and sub3 are merged with the same data, but they are different sub-blocks. They can be merged in a different order but they cannot be merged as the same block.

The main data are merged using the tip of Dynamic Columns.
http://www.tinybutstrong.com/examples.php?e=dyncol&m=result

You can notice that dynamic Headers and Footers are not in the same sub-block than the main data (sub4).
This way you can give a different design between main data, headers of columns, headers of rows, the Total row and the Total column.

By: Per Nils

Date: 2013-09-09

Time: 22:11

Re: Dynamic table with calculated sum on rows and columns

So you Skrol29 have one more time done the heavy lifting for a TBS users. ....

Thank you … having a bunker mentality I couldn't think of arrange the data in a different way.

The truth is that after looking at it I think it would suite my needs better when it comes in later state to add a matrix of values to multiply with the data.

The only drawback is that I have to revisit my awful nested algorithm en for collecting the data. But that's life.

It's bit embarrassing that the only thing I have as hived so far is the longest thread in the TBS forum (in number of pages).

With that in mind I will still add some more pages for me and others for ....

Starting with a overview of the new data structure in case you get lost in Skrol29 rearrangement of the data.

===== Data structure =====

In case you prefer php code …

// dataset 0
$data2[0]['title'] = 'TBS Manual';
$data2[0]['author'] = 'TBS Manual writer';

$data2[0]['col_lst'][0]['id'] = 'c0';
$data2[0]['col_lst'][0]['caption'] = 'colh_A';
$data2[0]['col_lst'][0]['total'] = 'tot_col_A';

$data2[0]['col_lst'][1]['id'] = 'c1';
$data2[0]['col_lst'][1]['caption'] = 'colh_B';
$data2[0]['col_lst'][1]['total'] = 'tot_col_B';

$data2[0]['data'][0]['header'] = 'rowh_1';
$data2[0]['data'][0]['total'] = 'tot_row_1';
$data2[0]['data'][0]['c0'] = 'row_data1A';
$data2[0]['data'][0]['c1'] = 'row_data1B';

$data2[0]['data'][1]['header'] = 'rowh_2';
$data2[0]['data'][1]['total'] = 'tot_row_2';
$data2[0]['data'][1]['c0'] = 'row_data2A';
$data2[0]['data'][1]['c1'] = 'row_data2B';

$data2[0]['totcol_caption'] = 'row_tot';
$data2[0]['totrow_caption'] = 'col_tot';
$data2[0]['totrow_total'] = '.';

// dataset 1
$data2[1]['title'] = 'Hunsley';
$data2[1]['author'] = 'Hunsley author';

$data2[1]['col_lst'][0]['id'] = 'c0';
$data2[1]['col_lst'][0]['caption'] = 'colh_1A';
$data2[1]['col_lst'][0]['total'] = 'tot_col_A';

$data2[1]['col_lst'][1]['id'] = 'c1';
$data2[1]['col_lst'][1]['caption'] = 'colh_1B';
$data2[1]['col_lst'][1]['total'] = 'tot_col_B';

$data2[1]['data'][0]['header'] = 'rowh_11';
$data2[1]['data'][0]['total'] = 'tot_row_1';
$data2[1]['data'][0]['c0'] = 'row_data11A';
$data2[1]['data'][0]['c1'] = 'row_data11B';

$data2[1]['data'][1]['header'] = 'rowh_12';
$data2[1]['data'][1]['total'] = 'tot_row_2';
$data2[1]['data'][1]['c0'] = 'row_data12A';
$data2[1]['data'][1]['c1'] = 'row_data12B';

$data2[1]['totcol_caption'] = 'row_tot';
$data2[1]['totrow_caption'] = 'col_tot';
$data2[1]['totrow_total'] = '.';

==== Template structure =====

First we have our ”top” level block that we tied to div or your own html tag (block=div). It will then multiply everything between <div> … </div> as long it have items.

First we start with column headers

Result :

Then we add the ”footers” ..

<div style="border: 1px; border-style: solid; border-color: blue; margin: 4px;">
    dataset : [top.$;block=div;sub1=col_lst;sub2=col_lst;sub3=col_lst;sub4=data] <br>
    title : [top.title] <br>
    author : [top.author] <br>
    <table border="1">
        <tr>
            <td>X</td>
            <td>[top_sub1.caption;block=td]</td>
            <td>[top.totcol_caption]</td>
        </tr>
        <tr>
            <td>[top.totrow_caption]</td>
            <td>[top_sub3.total;block=td]</td>
            <td>[top.totrow_total]</td>
        </tr>
        -->
    </table>
</div>

Result :

Then we comes to add the data ..

Starting with the row headers

<div style="border: 1px; border-style: solid; border-color: blue; margin: 4px;">
    dataset : [top.$;block=div;sub1=col_lst;sub2=col_lst;sub3=col_lst;sub4=data] <br>
    title : [top.title] <br>
    author : [top.author] <br>
    <table border="1">
        <tr>
            <td>X</td>
            <td>[top_sub1.caption;block=td]</td>
            <td>[top.totcol_caption]</td>
        </tr>
        <tr>
            <td>[top_sub4.header;block=tr]</td>
         <tr>
            <td>[top.totrow_caption]</td>
            <td>[top_sub3.total;block=td]</td>
            <td>[top.totrow_total]</td>
        </tr>

    </table>
</div>

Result:

Moving on with the rest

<div style="border: 1px; border-style: solid; border-color: blue; margin: 4px;">
    dataset : [top.$;block=div;sub1=col_lst;sub2=col_lst;sub3=col_lst;sub4=data] <br>
    title : [top.title] <br>
    author : [top.author] <br>
    <table border="1">
        <tr>
            <td>X</td>
            <td>[top_sub1.caption;block=td]</td>
            <td>[top.totcol_caption]</td>
        </tr>
        <tr>
            <td>[top_sub4.header;block=tr]</td>
            <td>[top_sub4.[top_sub2.id;block=td]]</td>
            <td>[top_sub4.total]</td>
         </tr>
         <tr>
            <td>[top.totrow_caption]</td>
            <td>[top_sub3.total;block=td]</td>
            <td>[top.totrow_total]</td>
        </tr>

    </table>
</div>

Worth mention is the … <td>[top_sub4.[top_sub2.id;block=td]]</td> …

The top_sub4 id/key is referenced from a another block. So If you mess that block up you are in a big mess.

We haven't managed to messed that up .. so our result is :

If there are some readers left and wonders what happen with the topic ... To calculate the sum of rows and cols .. you have to do it on the php side.

By: Skrol29

Date: 2013-09-09

Time: 22:39

Re: Dynamic table with calculated sum on rows and columns

> It's bit embarrassing that the only thing I have as hived so far is the longest thread in the TBS forum (in number of pages).

indeed :-)

By: Anonymous

Date: 2013-09-10

Time: 15:44

Re: Dynamic table with calculated sum on rows and columns

:)

Pitty I didn't include more "search" friendly words then I would also have the record in to be nr 1 in most search cases.

For example search word subblock ... I'm not even on the first page (bumer) ... perhaps we will solve that in the future..

..edit ...
Hmm after checking it seems your search hit criteria alg. is based on the last post that contains the word you are searching for.

By: Skrol29

Date: 2013-09-14

Time: 22:43

Re: Dynamic table with calculated sum on rows and columns

> Hmm after checking it seems your search hit criteria alg. is based on the last post that contains the word you are searching for.

Yep.

By: Per Nils

Date: 2013-09-24

Time: 22:05

Re: Dynamic table with calculated sum on rows and columns

Is not on topic but I do it here anyway.

Is there someone else who having trouble with open_tbs when using

        $TBS2 = new clsTinyButStrong;
        $TBS2->Plugin(TBS_INSTALL, OPENTBS_PLUGIN);
        $TBS2->LoadTemplate('doc_template.odt', OPENTBS_ALREADY_UTF8);
        .......
        $TBS2->Show(OPENTBS_DOWNLOAD, $filename);

The issue is that I get general errror input/output error with Libreoffice 4.1.1.2

When I open the file in notepad++ I can see the coments that I have in my php code att the top.

After spending a few days of google around it seems there is problem with Libreoffice and samba share.

I got lost in all the pages I browsed through so I can't determen if it's only with linux and natilus or it also affect windows.
Tried to use http://livehttpheaders.mozdev.org but it didn't gave me much.

When I saved the file on the server

$TBS2->Show(OPENTBS_FILE, 'doc.odt');

It will produce a valid writer file.

Could xdebug mess this upp?

Hmm maybe it's time to try the exampels that was shipped with the open_tbs download .. and polish my google skills ..

** edit **

the demo examples works ... Hmm it's me again then ...

** EDIT 2 **
As usually I rushed to quick ... I solved it by read the manual ... baah ...

The fix was ..

$TBS->PlugIn(OPENTBS_DEBUG_XML_CURRENT);

There it was in black and white ... having some spaces before the *

* OPENTBS DEBUG MODE: if the star, (*) on the left before the word OPENTBS, is not the very fi...

Removed the empty spaces in the code and I am back on track ..

By: Per Nils

Date: 2013-09-26

Time: 23:25

Re: Dynamic table with calculated sum on rows and columns

Have been sitting with this for a while now.

Libreoffice odt file and having dynamic columns. The xml output have my expanded cells but it isn't shown in the document.

Have tried to mark the check box for -> Allow table to split across pages and columns.

Other stuff that have bother me is that tbs tags is split between xml tags. Have to mark everything and from list box choose
clear formatting.

Would be nice if you could include dynamic columns in the opentbs demo.

Dynamic table with calculated sum on rows and columns

Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

Re: Dynamic table with calculated sum on rows and columns

New message