Categories > OpenTBS with XLSX >

mysql, then xml export

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: beni
Date: 2014-04-24
Time: 09:33

mysql, then xml export

Can somebody please help me? I have this script, which is not exporting to my excel xml file.

v12.php
<?php
include_once('../tbs_class_php5.php') ;

$TBS = new clsTinyButStrong ;
include_once('../plugins/tbs_plugin_excel.php');

//getting id
$sdat = mysql_real_escape_string($_GET['sdat']);
$edat = mysql_real_escape_string($_GET['edat']);
$klant = mysql_real_escape_string($_GET['klant']);

//DB start
$host = "localhost";
$user = "";
$password = "";
$dbname = "";
   
$connection = mysql_connect($host, $user, $password);
mysql_select_db($dbname,$connection);

$query = $TBS->MergeBlock('blk1','mysql','SELECT
    a.ID AS "Boekingnr",
    c.name as "Klantnaam",
    b.nummer AS "Lokaal",
    v.prijs as "Lokaalprijs",
   
    k.vaste_waarde as "korting_waarde",
    k.procent as "korting_procent",
   
    a.start_datum as "Startdatum",
    a.eind_datum as "Einddatum",
       
    a.opmerkingen as "Opmerkingen",

    h.naam as Extras1,
    a.extra_1_aantal as "Aantal1",
    h.prijs as exprijs1,
    j.naam as Extras2,
    a.extra_2_aantal as Aantal2,
    j.prijs as exprijs2,
    l.naam as Extras3,
    a.extra_3_aantal as Aantal3,
    l.prijs as exprijs3
   
    FROM boeking AS a
    LEFT OUTER JOIN rooms AS b ON (b.id = a.room_id)
    LEFT OUTER JOIN bamboo_clients AS c ON (c.id = a.klant_id)
    JOIN extras AS h ON (h.id = a.extra_1_id)
    JOIN extras AS j ON (j.id = a.extra_2_id)
    JOIN extras AS l ON (l.id = a.extra_3_id)
    LEFT OUTER JOIN verblijfssoort AS v ON (v.id = b.prijs_id)
    LEFT OUTER JOIN korting AS k ON (k.id = a.korting_id)
   
     WHERE YEAR(a.start_datum) = YEAR(CURDATE()) and
     (MONTH(a.start_datum) >= "$sdat")

    group by a.id
    order by a.id, a.start_datum') ;

while($blk1=mysql_fetch_assoc($query)) {
$array= unserialize($blk1);
//print_r($blk1);
}
if (!is_array($blk1)) $blk1 = array();
// DB eind

$TBS = new clsTinyButStrong;
$TBS->PlugIn(TBS_INSTALL, TBS_EXCEL);
$TBS->LoadTemplate('v12.xml');
$TBS->MergeBlock('blk1',$blk1);

$TBS->Show(TBS_EXCEL_DOWNLOAD, 'result.xml');
?>

v12.xml file contents of first sheet are:
[blk1.Boekingnr;noerr;block=Row]    [blk1.Klantnaam;noerr]    [blk1.Lokaal;noerr]    [blk1.LokaalPerUur;noerr]    #WAARDE!    [blk1.korting_waarde;noerr]    [blk1.korting_procent;noerr]    [blk1.Startdatum;noerr]    [blk1.Einddatum;noerr]    [blk1.Opmerkingen;noerr]    [blk1.Extras1;noerr]    [blk1.Aantal1;noerr]    [blk1.Prijs1;noerr]    [blk1.Extras2;noerr]    [blk1.Aantal2;noerr]    [blk1.Prijs2;noerr]    [blk1.Extras3;noerr]    [blk1.Aantal3;noerr]    [blk1.Prijs3;block=tr;onsection=showme;noerr]
By: Skrol29
Date: 2014-04-24
Time: 13:12

Re: mysql, then xml export

Hi beni;

The problem is in your way of retrieving data :
   $query = $TBS->MergeBlock(...)

First, MergeBlock() returns the number of record, not the recordset.
It can return the full recordset if you do $TBS->MergeBlock('blk1,*', ...)

But anyway, it won't work in your case because TBS is optimized to not execute the query if the block is not found.
In your case, you are running $query = $TBS->MergeBlock(...) without any template loaded.

So you'd better to:
$sql = 'SELECT
    a.ID AS "Boekingnr",
    c.name as "Klantnaam",
    ...
';

$TBS = new clsTinyButStrong;
$TBS->PlugIn(TBS_INSTALL, TBS_EXCEL);
$TBS->LoadTemplate('v12.xml');
$TBS->MergeBlock('blk1', 'mysql', $sql);

$TBS->Show(TBS_EXCEL_DOWNLOAD, 'result.xml');
By: beni
Date: 2014-04-24
Time: 13:47

Re: mysql, then xml export

Thanks Skrol29,
Could you please put for me the whole script. Still something missing, or my array is not ok?

I appreciate your help,

Beni
By: Skrol29
Date: 2014-04-24
Time: 14:02

Re: mysql, then xml export

Hi,

I can help only on the TBS part.
What array are you talking about ?
By: beni
Date: 2014-04-24
Time: 14:17

Re: mysql, then xml export

I mean the
while($data=mysql_fetch_assoc($q)) {
$array= unserialize($data);
//print_r($data);
}
if (!is_array($data)) $data = array();
By: Skrol29
Date: 2014-04-24
Time: 14:49

Re: mysql, then xml export

In my suggestion you don't need it anymore.

Use my snippet just after your db connection.
By: beni
Date: 2014-04-24
Time: 14:56

Re: mysql, then xml export

still doesn't work.
Do i add at the top these classes, and correct versions?
// Include classes
include_once('../tbs_class.php'); // v3.9.0
include_once('../plugins/tbs_plugin_opentbs.php'); // v1.9.0


THIS IS the code until now:
<?php
include_once('../tbs_class_php5.php') ;

$TBS = new clsTinyButStrong ;
include_once('../plugins/tbs_plugin_excel.php');

//getting id
$sdat = mysql_real_escape_string($_GET['sdat']);
$edat = mysql_real_escape_string($_GET['edat']);
$klant = mysql_real_escape_string($_GET['klant']);

//DB start
$host = "localhost";
$user = "";
$password = "";
$dbname = "";
   
$connection = mysql_connect($host, $user, $password);
mysql_select_db($dbname,$connection);

$sql = 'SELECT
    a.ID AS "Boekingnr",
    c.name as "Klantnaam",
    a.ID AS "Boekingnr",
    c.name as "Klantnaam",
    b.nummer AS "Lokaal",
    v.prijs as "Lokaalprijs",
   
    k.vaste_waarde as "korting_waarde",
    k.procent as "korting_procent",
   
    a.start_datum as "Startdatum",
    a.eind_datum as "Einddatum",
       
    a.opmerkingen as "Opmerkingen",

    h.naam as Extras1,
    a.extra_1_aantal as "Aantal1",
    h.prijs as exprijs1,
    j.naam as Extras2,
    a.extra_2_aantal as Aantal2,
    j.prijs as exprijs2,
    l.naam as Extras3,
    a.extra_3_aantal as Aantal3,
    l.prijs as exprijs3
   
    FROM boeking AS a
    LEFT OUTER JOIN rooms AS b ON (b.id = a.room_id)
    LEFT OUTER JOIN bamboo_clients AS c ON (c.id = a.klant_id)
    JOIN extras AS h ON (h.id = a.extra_1_id)
    JOIN extras AS j ON (j.id = a.extra_2_id)
    JOIN extras AS l ON (l.id = a.extra_3_id)
    LEFT OUTER JOIN verblijfssoort AS v ON (v.id = b.prijs_id)
    LEFT OUTER JOIN korting AS k ON (k.id = a.korting_id)
   
     WHERE YEAR(a.start_datum) = YEAR(CURDATE()) and
     (MONTH(a.start_datum) >= 05)

    group by a.id
    order by a.id, a.start_datum';


$TBS = new clsTinyButStrong;
$TBS->PlugIn(TBS_INSTALL, TBS_EXCEL);
$TBS->LoadTemplate('v24.xlsx');
$TBS->MergeBlock('blk1', 'mysql', $sql);

$TBS->Show(TBS_EXCEL_DOWNLOAD, 'result.xlsx');
?>
By: Skrol29
Date: 2014-04-24
Time: 15:39

Re: mysql, then xml export

The plug-in TBS_EXCEL doesn't work with XLSX templates, only XML/XLS templates.
For XLSX, use the OpenTBS plug-in.
By: beni
Date: 2014-04-27
Time: 12:36

Re: mysql, then xml export

Question:

why is it so difficult to fill a excel sheet with database data, and to post a complete/simple script which does that 100%?

Beni
By: Skrol29
Date: 2014-04-27
Time: 23:32

Re: mysql, then xml export

> why is it so difficult to fill a excel sheet with database data, and to post a complete/simple script which does that 100%?

If you are talking about XLSX workbooks then the script must :
- create or edit ZIP files because an XLSX is a ZIP archive
- format item data in the XLSX paradigm : all text strings are saved in a separated file, dates are converted, and all values can have a format
and if you want formating there is more
By: beni
Date: 2014-04-28
Time: 08:36

Re: mysql, then xml export

and xls only? a script which does it? none of the samples work yet.
By: Skrol29
Date: 2014-04-28
Time: 23:42

Re: mysql, then xml export

And XLS is a binary content in a private format.
It's worse.