Search code examples
phpopentbstinybutstrong

Use tbs to merge multiple records from sql to single block with mergeblock

I am using template docx and tbs template engine to generate document by values from sql. I want to load multiple records from sql dynamically to table I created in template docx which have single block called vm.productname. I want to make rows created dynamically and print for how many records available in sql array.But, failed to load multiple records dynamically.

This is how template.docx look like. template.docx

<?php
include_once('../tinybutstrong/tbs_class.php');
include_once('../tinybutstrong/plugins/tbs_plugin_opentbs.php');
//$tbs  = new clsTinyButStrong("##","##");
$tbs    = new clsTinyButStrong;
$tbs->Plugin(TBS_INSTALL, OPENTBS_PLUGIN);
$tbs->LoadTemplate('file_tobe_tested/1_template_defaultdelimiter.docx');

$db_name    ="template";
$db_con     =new PDO("mysql:host=localhost;dbname=template", 'root', '');

$query_get_companyinfo="select * from company where company_name='ABC Sdn Bhd'";
$data_get_company_info=get_data_from_sql_assoc($query_get_companyinfo,$db_con);

$tbs->MergeField('vs.company_name',$data_get_company_info[0]['company_name']);
$tbs->MergeField('vs.company_address_street',$data_get_company_info[0]['company_address_street']);
$tbs->MergeField('vs.company_address_zipcode',$data_get_company_info[0]['company_address_zipcode']);
$tbs->MergeField('vs.company_address_city',$data_get_company_info[0]['company_address_city']);
$tbs->MergeField('vs.company_address_state',$data_get_company_info[0]['company_address_state']);
$tbs->MergeField('vs.company_address_country',$data_get_company_info[0]['company_address_country']);

//assume customer_name is adam

$query_get_customerinfo="select * from customer where customer_name='Adam'";
$data_get_customer_info=get_data_from_sql_assoc($query_get_customerinfo,$db_con);

$tbs->MergeField('vs.customer_name',$data_get_customer_info[0]['customer_name']);
$tbs->MergeField('vs.customer_address_street',$data_get_customer_info[0]['customer_address_street']);
$tbs->MergeField('vs.customer_address_zipcode',$data_get_customer_info[0]['customer_address_zipcode']);
$tbs->MergeField('vs.customer_address_city',$data_get_customer_info[0]['customer_address_city']);
$tbs->MergeField('vs.customer_address_state',$data_get_customer_info[0]['customer_address_state']);
$tbs->MergeField('vs.customer_address_country',$data_get_customer_info[0]['customer_address_country']);
$date = date("Y-m-d");

//get customer_id from $data_get_customer_info
$customer_id=$data_get_customer_info[0]['customer_id'];

$query_get_products ="select product_name from product where customer_id='".$customer_id."'";
$data_get_products  =get_data_from_sql_assoc($query_get_products,$db_con);

$tbs->MergeBlock('vm.product_name',$data_get_products);
//$TBS->MergeBlock('blk_res',$result);

$output_file        ='output_1'.$date.'.docx';
$tbs->Show(OPENTBS_FILE,$output_file);

print "<a href=\"$output_file\">$output_file</a><br>";

function get_data_from_sql_assoc($query,$db)//fetch associative
    {
        $statement = $db->prepare($query);  
        $statement->execute();
        $data = $statement->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }
?>

I get this error when I excute the code

TinyButStrong Error in block's definition [vm.product_name...]: at least one tag corresponding to tr is not found. Check opening tags, closing tags and embedding levels.

Thanks in advance.

Solution

  • I change [vm.product_name;block=tr] to [vm.product_name;block=tbs:row] in template.

    Ammended template: enter image description here

    In php code, I change $tbs->MergeBlock('vm.product_name',$data_get_products); to 

    $tbs->MergeBlock('vm',$data_get_products);

    Since I have another variable under vm, that is vm.product_price, I have included product_price records to $data_get_products. Then, I merge block using vm as block name so that both variable will be replaced with respective records. This solved my issue.

    Alternatively , ,* should work for multiple records. But I failed in making it run.

    Method MergeBlock() enhancement in v3: from this link

    • Now you can makes the method to return the full merged data by adding '*' as a block name to merge.

      Example : $data = $TBS->MergeBlock('blk,*','SELECT id FROM table_a');