rob
Administrator
Lets consider that you have two tables:
1) company
id name
-----------
1 fabrik
2 google
2) staff
id name company_id
----------------------
1 rob 1
2 hugh 1
3 Sergi 2
4 bob 1
5 jeff 1
and that your company table contains a left join to the staff table. When viewing the table you will see this data:
id name id name company_id
--------------------------------------
1 fabrik 1 rob 1
1 fabrik 2 hugh 1
1 fabrik 4 bob 1
1 fabrik 5 jeff 1
2 google 3 Sergi 2
Now the issue that you have is that the company name is duplicated for each person in the company, which looks a little confusing
First of all what makes things a little easier is if we group the data by the company's id, to do this:
- edit the table
- select the data tab
- select "id" from the "group by" drop down list
- Save the table
Now your data looks like this:
id name id name company_id
--------------------------------------
1 fabrik 1 rob 1
1 fabrik 2 hugh 1
1 fabrik 4 bob 1
1 fabrik 5 jeff 1
id name id name company_id
--------------------------------------
2 google 3 Sergi 2
This is better, as each company is now in its own table, but there is still duplicated company information.
So taking this one step further lets say we want to display our data like this:
Comany: fabrik
name
---------------
rob
hugh
bob
jeff
Company: google
name
----------------
Sergi
Now we are going to have to create a custom table template
So copy the current template you are using - (details of how to do this in this FAQ - http://fabrikar.com/index.php?option=com_smf&Itemid=9&topic=1778.0)
for this example I have copied the "default" table template. The part of the code we are interested in is this:
the first thing we need to do is find where the grouped table starts (each set of grouped data is contained within its own table). This is here:
so before the <table> we want to enter the company name. To do this we need to get the first record object in the group:
we can then access the company name with this:
note how "company___name" follows the standard fabrik syntax for accessing elements
So now we have the company name stored in the $companyName variable, we simply want to write it out to the page:
Next we want to write out our table, but only with the user's names, so we can replace all the code between "<table class="fabrikTable">" and "</table>" with this:
That should be enough to give us the desired layout.
Below is an example of the entire table template file:
1) company
id name
-----------
1 fabrik
2 google
2) staff
id name company_id
----------------------
1 rob 1
2 hugh 1
3 Sergi 2
4 bob 1
5 jeff 1
and that your company table contains a left join to the staff table. When viewing the table you will see this data:
id name id name company_id
--------------------------------------
1 fabrik 1 rob 1
1 fabrik 2 hugh 1
1 fabrik 4 bob 1
1 fabrik 5 jeff 1
2 google 3 Sergi 2
Now the issue that you have is that the company name is duplicated for each person in the company, which looks a little confusing
First of all what makes things a little easier is if we group the data by the company's id, to do this:
- edit the table
- select the data tab
- select "id" from the "group by" drop down list
- Save the table
Now your data looks like this:
id name id name company_id
--------------------------------------
1 fabrik 1 rob 1
1 fabrik 2 hugh 1
1 fabrik 4 bob 1
1 fabrik 5 jeff 1
id name id name company_id
--------------------------------------
2 google 3 Sergi 2
This is better, as each company is now in its own table, but there is still duplicated company information.
So taking this one step further lets say we want to display our data like this:
Comany: fabrik
name
---------------
rob
hugh
bob
jeff
Company: google
name
----------------
Sergi
Now we are going to have to create a custom table template
So copy the current template you are using - (details of how to do this in this FAQ - http://fabrikar.com/index.php?option=com_smf&Itemid=9&topic=1778.0)
for this example I have copied the "default" table template. The part of the code we are interested in is this:
Code:
<?php if(count($this->rows) == 0){?>
<div class="emptyDataMessage"><?php echo $this->emptyDataMessage;;?></div>
<?php }else{
foreach($this->rows as $group){?>
<table class="fabrikTable">
<tr>
<?php foreach($this->headings as $heading){?>
<th><?php echo $heading;?></th>
<?php }?>
</tr>
<?php
$c = 0;
foreach($group as $row){
?>
<tr class="oddrow<?php echo $c;?>">
<?php foreach($this->headings as $heading=>$label){
?>
<td><?php echo($row->$heading);?></td>
<?php }?>
</tr>
<?php $c = 1-$c;
}
}?>
<tr class="fabrik_calculations">
<?php
foreach($this->calculations as $cal){
echo "<td>" . $cal->calc ."</td>";
}
?>
</tr>
</table>
<?php }
the first thing we need to do is find where the grouped table starts (each set of grouped data is contained within its own table). This is here:
Code:
foreach($this->rows as $group){
<table class="fabrikTable">
so before the <table> we want to enter the company name. To do this we need to get the first record object in the group:
Code:
$firstRecord = $group[0];
we can then access the company name with this:
Code:
$companyName = $firstRecord->company___name;
note how "company___name" follows the standard fabrik syntax for accessing elements
So now we have the company name stored in the $companyName variable, we simply want to write it out to the page:
Code:
echo "<p>Company:$companyName</p>";
Next we want to write out our table, but only with the user's names, so we can replace all the code between "<table class="fabrikTable">" and "</table>" with this:
Code:
<tr><th>Name</th></tr>
<?php
$c = 0;
foreach($group as $row){
?>
<tr class="oddrow<?php echo $c;?>">
<td><?php echo($row->staff___name);?></td>
</tr>
<?php $c = 1-$c;
?>
That should be enough to give us the desired layout.
Below is an example of the entire table template file:
Code:
<?php /**
* @package fabrik
* @version 1.0.4
* @Copyright (C) Rob Clayburn
* @license GNU/GPL [url]http://www.gnu.org/copyleft/gpl.html[/url]
*/
/* MOS Intruder Alerts */
defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' );
?>
<h1><?php echo $this->table->label;?></h1>
<div><?php echo $this->table->intro;?></div>
<form action="<?php echo $this->table->action;?>" method="post" id="<?php echo $this->formid;?>" name="fabrikTable">
<?php if($this->showAdd){?>
<span class="pagenav">
<a href="<?php echo $this->addRecordLink;?>"><?php echo _ADD_RECORD;?></a>
</span>
<?php }?>
<?php if($this->showCSV){?>
<span class="pagenav">
<a href="<?php echo $this->csvLink;?>"><?php echo _EXPORT_TO_CSV;?></a>
</span>
<?php }?>
<?php if($this->showRSS){?>
<span class="pagenav">
<a href="<?php echo $this->rsslink;?>"><?php echo _SUBSCRIBE_RSS;?></a>
</span>
<?php }?>
<?php if($this->showFilters){?>
<table class="filtertable">
<tr>
<th colspan="2" style="text-align:left"><?php echo _SEARCH;?>:</th>
</tr>
<?php foreach($this->filters as $filter){?>
<tr>
<td><?php echo $filter->label;?></td>
<td style="text-align:right;"><?php echo $filter->element;?></td>
</tr>
<?php } ?>
<?php if($this->filter_action != 'onchange') {?>
<tr>
<td colspan="2" style="text-align:right;">
<input type="button" class="button" value="<?php echo _GO;?>" name="filter" />
</td>
</tr>
<?php }?>
</table>
<?php }?> <br style="clear:both;" />
<br />
<?php if(count($this->rows) == 0){?>
<div class="emptyDataMessage"><?php echo $this->emptyDataMessage;;?></div>
<?php }else{
$firstRecord = $group[0];
$companyName = $firstRecord->company___name;
echo "<p>Company:$companyName</p>";
foreach($this->rows as $group){?>
<table class="fabrikTable">
<tr><th>Name</th></tr>
<?php
$c = 0;
foreach($group as $row){
?>
<tr class="oddrow<?php echo $c;?>">
<td><?php echo($row->staff___name);?></td>
</tr>
<?php $c = 1-$c;
?>
</table>
<?php }
if( $this->canDelete ){?>
<label><input type="checkbox" id="table_<?php echo $this->table->id;?>_checkAll" /><?php echo _CHECK_ALL;?></label>
<?php
}
print_r($this->hiddenFields);?>
<?php echo $this->nav;
echo $this->deleteButton;
?>
</form>