KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Pass variable to sql query in report #1387

Open George opened this topic on on Apr 15, 2020 - 7 comments

George commented on Apr 15, 2020

Hi all ,

I hope I'm explaining this correctly , I use codeigniter + grocecycrud. I'm want to create a report which consists from around 10 sql queries that include calculations and joins in my database . When I create a view in grocery crud I have a view button lets say that calls a function in my controller and presents the detail of record with id 1 as in : http://localhost/index.php/controller/function/1 . I want to create a report that takes this id from the controller and pass it to the sql query in koolreport :

eg controller function :

function analysis()
{
        $this->load->view('analysis.php');
}

My analysis.php is :

<?php


require APPPATH."reports/Analysis.php";

$report = new Analysis;
$report->run()->render();

and one of my query in Analysis.php :

<?php
require APPPATH . "/libraries/koolreport/autoload.php";

class Analysis extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources" => array(
                "data" => array(
                    "connectionString" => "mysql:host=localhost;dbname=database",
                    "username" => "",
                    "password" => "",
                    "charset" => "utf8",
                ),
            ),
        );
    }

    public function setup()
    {
        //1st Row
        $this->src('data')
        ->query("-- SELECT from table for Calculations ;
        SELECT 
        -- Calculate Max Tolerable Loss (0.2%) ;
        ROUND(Own*(-0.2/100), 2)  AS MxTol02P,
        -- Calulate Scenario fig /0.2% ;
        ROUND((Discharge.Actual - Load.Own)-(Own*(-0.2/100)), 3) AS Scenario
        
        
        FROM Loading
        INNER JOIN Discharge
        ON Load.idNo = Discharge.idNo
        WHERE Load.idNo = 1")
        ->pipe($this->dataStore('analysis_figures'));

Is there a way instead of putting WHERE Load.idNo = 1 have something like WHERE Load.idNo = '$idNo' and get this idNo from the calling controller ? (all the queries will use this idNo).

I've looked into a couple of similar questions in the forum but I haven't got a clear direction from the answers as in https://www.koolreport.com/forum/topics/1023 where the question describes something similar I guess but I somehow can't really follow the answer and I'm guessing he speaks about variables between koolreports controller and view file whereas I want to pass this variable from my CI controller to koolreport controller .

Sorry for the long post ,I hope I explained this clearly and thanks anyway for any hint.

George

George commented on Apr 15, 2020

OK I figured out how to pass the variable but although is correct the query throws error :

Message: Undefined property: Analysis::$var1

but when I echo $this->var1 on the page it has the correct value . Do I need to escape differently the WHERE clause ? now it is : WHERE Load.idNo = '$this->var1' "

David Winterburn commented on Apr 15, 2020

Hi George,

Please try to fill in parameters in your query like this:

        $this->src('data')
        ->query("-- SELECT from table for Calculations ;
        SELECT 
        -- Calculate Max Tolerable Loss (0.2%) ;
        ROUND(Own*(-0.2/100), 2)  AS MxTol02P,
        -- Calulate Scenario fig /0.2% ;
        ROUND((Discharge.Actual - Load.Own)-(Own*(-0.2/100)), 3) AS Scenario
        
        
        FROM Loading
        INNER JOIN Discharge
        ON Load.idNo = Discharge.idNo
        WHERE Load.idNo = :idNo")
        ->params(array(
            ":idNo" => $this->var1
        ))
        ...

Let us know how it works for you. Thanks!

George commented on Apr 15, 2020

Thanks David ,

at first it throwed these errors on the page : Message: Use of undefined constant var1 - assumed 'var1' (this will throw an Error in a future version of PHP) Message: Object of class Analysis could not be converted to number Message: A non-numeric value encountered but the query worked and then I saw that I made this typo error on the query :

WHERE Load.idNo = :idNo ")
        ->params(array(
            ":idNo" => $this-var1
        ))

then I fixed the error ( $this->var1 ) and I got this message and query doesn't work :

Message: Undefined property: Analysis::$var1

Something is wrong in my function maybe I will try again , thanks anyway your help was precious!!!

George

David Winterburn commented on Apr 15, 2020

Hi George,

Back to your previous question "how to pass variable to a report" I think the following method could work:

$report = new Analysis(array(
    "param1" => $value1,
    "idNo" => $id,
    ...
));

//Analysis.php
function setup()
{
    // echo $this->params["param1"];
    $this->src(...)
    ->query($query1)
    ->params(array(
        ":idNo" => $this->params["idNo"]
    ))
}

Hope this helps in your case. Thanks!

George commented on Apr 15, 2020

You're absolutely right !! I guess it has to do with how you escape values passed in functions

->params(array(
            ":id" => $this->var1
        ))

this above works like a charm !!

Thank you so much George

I hope the tip is enough!!

David Winterburn commented on Apr 16, 2020

Dear George, thank you so much for your tip! We appreciate it very much, especially as a token of showing our customers' satisfactory with our product and service. Don't hesitate to let us know whenever you need help or guide for using our reporting framework. Thanks again!

soumiya commented on Jul 12, 2022

i need help to want api key url in koolreport

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
solved

None