Hi all,

I am trying to write multi query and execute SET variables and than use them in next query. I am not sure if this is correct way for using with reports and binding. I know this isn't package related topic.

function setup()
    { 
$this->src('automaker')
            ->query("SET @DatumOD:=:start")
            ->query("SET @DatumDO:=:end")
            ->query("SET @Ukupno:= 0")
            ->query("select @Ukupno:=SUM(Kolicina) as Ukupno from my_table where DispatchDate between @DatumOD and @DatumDO")
            ->query("select NazivDobavljaca, sum(Kolicina) as Kolicina, (SUM(Kolicina))/(@Ukupno/100) as Procenat from my_table where DispatchDate between :start and :end")
            ->params(array(
                ":start"=>$this->params["dateRange"][0],
                ":end"=>$this->params["dateRange"][1],
            ))
            ->pipe($this->dataStore("my_data_store"));

}

I m trying to set ->query("SET @Ukupno:= 0") and with next step get calculated value which will be used in last query statement. In this case, (SUM(Kolicina))/(@Ukupno/100) as Procenat - @Ukupno isn't accessed and calculation it wrong. This example above does not throw any errors, only column "Procenat" does not have any data in DataTables table.

Can someone point me in right direction pls.

Hi Sebastian, I've place them into one query as you suggested but it does not give me any results what so ever. No errors but no data as well :D

->query("SET @DatumOD:=:start;
            SET @Ukupno:= 0;
            select @Ukupno:=SUM(Kolicina) as Ukupno........;
            select NazivDobavljaca, ........;
            ")

Thanks Sebastian, I will keep trying as well. I have found this but that's totally different from what I gave for query and usage of ->pipe

https://www.php.net/manual/en/mysqli.quickstart.multiple-statement.php

Hi Sebastian, have you found any way to make this work? I still have this problem and don't know what to do really. :/

Hi Sebastian, I did not really get this. Looks a was complicated.

Maybe I could run different php query and "pipe" results to it. Would that be possible? I do not understand what ->pipe does here.

I did try this:

 $var_ukupno = 0;
            
            // 1st exec query
            $this->src('automaker')
            ->query("select SUM(Kolicina) as Ukupno from cm_prodaja where DatumOtprem between :start and :end")
            ->params(array(
                ":start"=>$this->params["dateRange"][0],
                ":end"=>$this->params["dateRange"][1],
            ))
            ->pipe($var_ukupno);
            
            
            
            // ## run second final query
            $this->src('automaker')
            ->query("select NazivDobavljaca, Grupa, NazivGrupe, sum(Kolicina) as Kolicina, (SUM(Kolicina))/(.$var_ukupno./100) as Procenat from cm_prodaja where DatumOtprem between :start and :end group by NazivDobavljaca, Grupa, NazivGrupe order by NazivDobavljaca, NazivGrupe")
            
            ->params(array(
                ":start"=>$this->params["dateRange"][0],
                ":end"=>$this->params["dateRange"][1],
            ))
            ->pipe($this->dataStore("u_grupe_dobavljaci_period"));

I wanted to run first query and than assign result to var $var_ukupno and use it in second query. But this throws an error Message:

Type: Error

Message: Call to a member function source() on integer

Filename: /home/cmbihco/public_html/prometi/fuel/modules/reporting/libraries/koolreport/core/src/core/Node.php

Line Number: 87

I am still stuck on this and can't figure out the way around it.

Hi Sebastian,

This is something what actually helped me to get solution for my problem. I am using do while, not smartest way but works for me.

Here is final code, in case if someone needs.

function setup()
    { 
$var_ukupno = 0;
            
        do {
            // 1st exec query, get first int value which will be used in 2nd stage and calculation
            $this->src('automaker')
            ->query("select SUM(Kolicina) as Ukupno from cm_prodaja where DatumOtprem between :start and :end")
            ->params(array(
                ":start"=>$this->params["dateRange"][0],
                ":end"=>$this->params["dateRange"][1],
            ))
            ->pipe($this->dataStore("temp"))
            ->requestDataSending();
            
            $var_ukupno = $this->dataStore("temp")->data()[0]["Ukupno"];
            
        } while($var_ukupno == 0);
           
            // 2nd stage query
            $this->src('automaker')
            ->query("select NazivDobavljaca, Grupa, NazivGrupe, sum(Kolicina) as Kolicina, (SUM(Kolicina))/($var_ukupno/100) as Procenat from cm_prodaja where DatumOtprem between :start and :end group by NazivDobavljaca, Grupa, NazivGrupe order by NazivDobavljaca, NazivGrupe")
                
            ->params(array(
                ":start"=>$this->params["dateRange"][0],
                ":end"=>$this->params["dateRange"][1],
            ))
            ->pipe($this->dataStore("u_grupe_dobavljaci_period"));
}