writing data to a mysql file in a .view file

Richb201 commented on Apr 2, 2021

At the bottom of my .view file where I am manipulating data from various datasources, I need to write out the results to a new mysql table. The reason for this is I need to export the data to a Excel sheet. The problem is that I don't have a handle to the dbase in the .view file. Rather, I have access to the dataStores.

Is is possible for me to go directly from my calculated fields (they came from various datastores) and just use Export without the data coming from a table? Something like this?



<?php
$report = new MyReport;
$report->run()->exportToExcel(array(
    "fields" => array(
        "column1" => $mydata1,
        "column2" => $mydata2,
        "column3" =>$mydata3
     
    )
))->toBrowser("myreport.xlsx");

Richb201 commented on Apr 4, 2021

Or perhaps I can load data to a datastore directly from php?

Sebastian Morales commented on Apr 5, 2021

In your view file just create a new DataStore object and set its meta and data with these methods:

$exportDataStore = new \koolreport\core\DataStore($exportData, $exportMeta);
//or
$exportDataStore = new \koolreport\core\DataStore();
$exportDataStore->meta($exportMeta);
$exportDataStore->meta($exportData);

Finally export this datastore to your excel file. Let us know if this meets your requirement. Tks,

Richb201 commented on Apr 5, 2021

Can you point me to docs for the meta command? What is $exportData and $exportMeta?

Richb201 commented on Apr 5, 2021

Here is a listing of methods for dataStore. please point me to exportData and exportMeta.

DataStore
Overview
Normal use
Aggregated Methods
count()
sum()
avg()
min()
max()
mode()
Filter Methods
filter()
filter($func)
where()
whereIn()
whereNotIn()
except()
only()
Join methods
join()
leftJoin()
Adding methods
push()
append()
prepend()
Sort methods
sort()
sortBy()
sortKeys()
sortKeysDesc()
Selecting Methods
all()
data()
top()
topByPercent()
bottom()
bottomByPercent()
first()
last()
take()
slice()
splice()
Other methods
toJson()
isNotEmpty()
isEmpty()
each()
pluck()
reject()
columnMeta()
toArray()
toTableArray()
makeCopy()
get()
has()
process()

Sebastian Morales commented on Apr 6, 2021

$exportMeta and $exportData are metadata and data that you want to export, which you get from manipulating other datastores according to your needs.

Richb201 commented on Apr 7, 2021

I need an example of loading these two. I have all of the data in 10 php variables. How do I get them into a datastore? CSV? This is getting humorous.

Sebastian Morales commented on Apr 7, 2021

Here are a datstore's meta and data structure

$exportMeta = [
    "columns" => [
        "column1" => ["label" => "Column 1", "type" => "string"],
        "column2" => ["label" => "Column =2", "type" => "number", "decimals" => 2],
        ...
    ]
];

$exportData = [
    ["column1" => "a", "column2" => 1, ...],
    ["column1" => "b", "column2" => 2, ...],
];

$exportDataStore = new \koolreport\core\DataStore($exportData, $exportMeta);

But I think if you only want to output the data to csv just convert $exportData to csv with this method:

https://www.php.net/manual/en/function.fputcsv.php

$exportData = array (
    array('aaa', 'bbb', 'ccc', 'dddd'),
    array('123', '456', '789'),
    array('"aaa"', '"bbb"')
);

$fp = fopen('file.csv', 'w');

foreach ($list as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);

Richb201 commented on Apr 7, 2021

Thanks, I am not trying to output to CSV, I am trying to output to Excel. And that works fine. I just don't know how to get the data into the datastore without creating a table first. I have all of the data (that has been calculated) in variables ie $v1, $v2, $v3, etc.

KoolReport's Forum

Writing data to a mysql file in a .view file #2010

Build Your Excellent Data Report