Categories > Your tips & tricks >

Merge table with dynamic number of columns

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Skrol29
Date: 2005-01-01
Time: 00:00

Merge table with dynamic number of columns

Hello,

A question which comes back often is how to merge an Html table with a variable number of columns.

What we want:
Row  Col1  Col2  ...  ColN
#1   d1-1  d2-1  ...  dN-1
#2   d1-2  d2-2  ...  dN-2
...  ...   ...   ...  ...
#3   d1-M  d2-M  ...  dN-M
ColX : name of the column number X
dX-Y : value of data item of column X at the row Y.


Here is a solution for a case where we want to display the content of a MySQL table without knowing its columns.
This example also shows rows with alternative display.

HTML:
<tr>
  <td>Row</td>
  <td>[col_0.val;block=td]</td>
</tr>
<tr bgcolor="#99CC66">
  <td>#[row.#;block=tr]</td>
  <td>[row.[col_1.val;block=td]]</td>
</tr>
<tr bgcolor="#00CC66">
  <td>#[row.#;block=tr]</td>
  <td>[row.[col_2.val;block=td]]</td>
</tr>

PHP:
// Opening the query
$res = mysql_query('SELECT * FROM '.$my_table);

// Building the array of column names
$col = array();
$iMax = mysql_num_fields($res);
for ($i=0;$i<=$iMax;$i++) $col[$i] = mysql_field_name($i);

// Merging column names
$TBS->MergeBlock('col_0,col_1,col_2',$col);

// Merging rows
$TBS->MergeBlock('row',$res);

Note: it needs to have one different column's block for each row of the table.
In the example, the table in the template has 3 rows, so we need 3 column's blocks: 'col_1', 'col_1' and 'col_3'.
They are merged all tree in only one MergeBlock() call.


Enjoy,
By: magicktrickpony
Date: 2005-07-12
Time: 13:06

Re: Merge table with dynamic number of columns

Hi-ho!!!

After spending _countless_ hours of trying to get this example to work, I would like to point out that:

1.) If you start counting from Zero the for loop needs to adapted to
for ($i=0;$i < $iMax;$i++) $col[$i] = mysql_field_name($i);

2.) Sillyness to teh extreme:

if you are a whitespace freak just like me and type:
$TBS->MergeBlock('col_0, col_1, col_2', $col);
it won't work. Is this a bug - possiblement???

3.) If you are using postgres you might like to use this code:

// Opening the query
$res = pg_query('SELECT * FROM '.$my_table);

// Building the array of column names
$col = array();
$iMax = pg_num_fields($res);
for ($i=0;$i<=$iMax;$i++) $col[$i] = pg_field_name($res, $i);

// Merging column names
$TBS->MergeBlock('col_0,col_1,col_2',$col);

// Merging rows
$TBS->MergeBlock('row',$res);

Au revoir!!!

--ye olde pony
By: magicktrickpony
Date: 2005-07-15
Time: 14:23

Re: Merge table with dynamic number of columns

Yes!! Because I am thoroughly bored I would like to include an example code for ADOdb library for PHP (http://phplens.com/lens/adodb/docs-adodb.htm)

// Opening the query
$res = $db->Execute('SELECT * FROM '.$my_table);
$arr = $res->getArray();

// Building the array of column names
$col = array();
$iMax = $res->FieldCount();

for ($i = 0; $i < $iMax; $i++) {
  $field = $res->FetchField($i);
  $col[$i] = $field->name;
}

[...]

// Merging column names
$TBS->MergeBlock('col_0,col_1,col_2',$col);

// Merging rows
$TBS->MergeBlock('row',$arr);

Cheers!!!!!!!!

--thepony
By: Skrol29
Date: 2005-07-15
Time: 15:04

Re: Merge table with dynamic number of columns

Thanks ThePony :)

By the way:
>$TBS->MergeBlock('col_0, col_1, col_2', $col);
>it won't work. Is this a bug - possiblement???

It's not a bug, it's only not supported (yet).
But it could be easily supported.
By: thuc101
Date: 2010-03-15
Time: 08:38

Re: Merge table with dynamic number of columns

thank Scrok29, very good

but some change
for ($i=0;$i<=$iMax;$i++) $col[$i] = mysql_field_name($i);
by:
for ($i=0;$i<$iMax;$i++) $col[$i] = mysql_field_name($res,$i);