KoolReport's Forum

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

MultiSelect - Pdo Error #446

Closed Dimitry opened this topic on on Sep 4, 2018 - 12 comments
Dimitry commented on Sep 4, 2018

Hi. I am having some trouble with 2 MultiSelect widgets. Pdo is throwing error "PDOStatement::bindValue(): SQLSTATE[HY093]: Invalid parameter number: :cashbox_param0"

Only one of them is working fine. Can You pls help me, what am I doing wrong.

Here is the code from the model:

   protected function defaultParamValues()
    {
        return array(
            "dateRange" => array(
                "2018-10-10",
                "2018-10-11"
            ),
            "cashbox" => array(),
            "moneyTypes" => array(),
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "dateRange" => "dateRange",
            "cashbox" => "cashbox",
            "moneyTypes" => "moneyTypes",
        );
    }
function setup()
    {

        $sql = $this->buildSql();

        $this->src('mydata')
            ->query($sql)
            ->params(array(
                ":startDate" => $this->params["dateRange"][0],
                ":endDate" => $this->params["dateRange"][1],
                ":cashbox" => $this->params["cashbox"],
                ":moneyTypes" => $this->params["moneyTypes"],
            ))
            ->pipe(new Pivot(array(
                "dimensions" => array(
                    "column" => "",
                    "row" => "cashbox, currency_type, date",
                ),
                "aggregates" => array(
                    "sum" => "init_balance, income, outcome, final_balance",
                ),
            )))
            ->pipe($this->dataStore('mydata'));

        $this->src('mydata')
            ->query('SELECT id, name FROM kassa')
            ->pipe($this->dataStore('cashboxData'));
        $this->src('mydata')
            ->query('SELECT id, name FROM money_types')
            ->pipe($this->dataStore('moneyTypes'));
    }

Code from view:

                  <div class="form-group">
                        <?php
                        DateRangePicker::create(array(
                            "name"=>"dateRange"
                        ))
                        ?>
                    </div>
                    <div class="form-group">
                        <?php
                        MultiSelect::create(array(
                            "name"=>"cashbox",
                            "dataStore"=>$this->dataStore("cashboxData"),
                            "dataBind"=>array("text"=>"name","value"=>"id"),
                            "multiple"=>true,
                            "attributes"=>array(
                                "class"=>"form-control",
                                "size"=>4,
                            )
                        ));
                        ?>
                    </div>
                    <div class="form-group">
                        <?php
                        MultiSelect::create(array(
                            "name"=>"moneyTypes",
                            "dataStore"=>$this->dataStore("moneyTypes"),
                            "dataBind"=>array("text"=>"name","value"=>"id"),
                            "multiple"=>true,
                            "attributes"=>array(
                                "class"=>"form-control",
                                "size"=>2,
                            )
                        ));
                        ?>
                    </div>
KoolReport commented on Sep 4, 2018

May I see the code in your SQL statement where :cashbox is used.

Dimitry commented on Sep 4, 2018

' . (($this->params["cashbox"]!=array())?" AND m.kassa_id IN (:cashbox)":"") . ' ' . (($this->params["moneyTypes"]!=array())?" AND m.money_type_id IN (:moneyTypes)":"") . '

group by k.name, m.kassa_id, mt.name, m.money_type_id, m.date

KoolReport commented on Sep 4, 2018

It happens because when $this->params["cashbox"] is empty, then the SQL statement does not have :cashbox to bind. Pdo looks for :cashbox to bind but it does not find it, causing this error.

The solution is that you only bind the cashbox if there $this->params["cashbox"] is not empty:

$params = array(
    ":startDate" => $this->params["dateRange"][0],
    ":endDate" => $this->params["dateRange"][1],
);

if($this->params["cashbox"]!=array())
{
    $params[":cashbox"] = $this->params["cashbox"];
}

...

$this->src("mydata")
->query($sql)
->params($params);

You apply the same for "moneyTypes".

Dimitry commented on Sep 4, 2018

function setup()

{
    $params = array(
        ":startDate" => $this->params["dateRange"][0],
        ":endDate" => $this->params["dateRange"][1],
    );

    if($this->params["cashbox"]!=array())
    {
        $params[":cashbox"] = $this->params["cashbox"];
    }

    if($this->params["moneyTypes"]!=array())
    {
        $params[":moneyTypes"] = $this->params["moneyTypes"];
    }

    $sql = $this->buildSql();

    $this->src('mydata')
        ->query($sql)
        ->params($params)

With this code if I chose one of the MultiSelect values it works perfect, but if I chose both, it throwing same exception.

when I dd($params):

array:4 [▼ ":startDate" => "2018-10-10 00:00:00" ":endDate" => "2018-10-11 00:00:00"

":cashbox" => array:3 [▼

0 => "1"
1 => "2"
2 => "3"

]

":moneyTypes" => array:2 [▼

0 => "1"
1 => "2"

] ]

Dimitry commented on Sep 4, 2018

Can You please revert!

KoolReport commented on Sep 4, 2018

Please echo the $sql as well to see if SQL generated correctly. The correct SQL should have :cashbox and :moneyTypes inside.

Dimitry commented on Sep 4, 2018

$sql = '

            select k.name as cashbox, mt.name as currency_type, :startDate as date, 
            (SELECT m3.final_balance from money_aggregate_table  AS m3 
                WHERE date <= :startDate AND m3.kassa_id = m.kassa_id AND m3.money_type_id = m.money_type_id 
                ORDER BY date DESC LIMIT 1) as init_balance, 
                0.00 as income, 0.00 as outcome, 
                (SELECT m4.final_balance from money_aggregate_table AS m4 
                    WHERE date <= :endDate AND m4.kassa_id = m.kassa_id AND m4.money_type_id = m.money_type_id 
                    ORDER BY date DESC LIMIT 1) as final_balance 
                    from money as m 
                     inner join kassa as k on m.kassa_id = k.id 
                     inner join money_types as mt on m.money_type_id = mt.id 
                     WHERE true = true
                     ' . (($this->params["cashbox"]!=array())?" AND m.kassa_id IN (:cashbox)":"") . '
                     ' . (($this->params["moneyTypes"]!=array())?" AND m.money_type_id IN (:moneyTypes)":"") . '
                    group by k.name, m.kassa_id, mt.name, m.money_type_id, m.date 
                    UNION 
                    SELECT uk.name as cashbox, 
                        umt.name as currency_type, 
                            date as date, 
                            0.00 AS init_balance, 
                            sum(um.summ) FILTER (WHERE um.summ > 0 and um.date between :startDate and :endDate) as income,
                            sum(um.summ) FILTER (WHERE um.summ < 0 and um.date between :startDate and :endDate) as outcome,
                            0.00 AS final_balance
                            from money as um
                                inner join kassa as uk on um.kassa_id = uk.id
                                inner join money_types as umt on um.money_type_id = umt.id
                            WHERE um.date between :startDate and :endDate 
                            ' . (($this->params["cashbox"]!=array())?" AND um.kassa_id IN (:cashbox)":"") . '
                            ' . (($this->params["moneyTypes"]!=array())?" AND um.money_type_id IN (:moneyTypes)":"") . '
                            group by uk.name, um.kassa_id, umt.name, um.money_type_id, um.date
    ';
KoolReport commented on Sep 4, 2018

I know but please use echo $sql; to print out exactly sql that will implement.

Dimitry commented on Sep 4, 2018

select k.name as cashbox, mt.name as currency_type, :startDate as date, \n

            (SELECT m3.final_balance from money_aggregate_table  AS m3 \n
                WHERE date <= :startDate AND m3.kassa_id = m.kassa_id AND m3.money_type_id = m.money_type_id \n
                ORDER BY date DESC LIMIT 1) as init_balance, \n
                0.00 as income, 0.00 as outcome, \n
                (SELECT m4.final_balance from money_aggregate_table AS m4 \n
                    WHERE date <= :endDate AND m4.kassa_id = m.kassa_id AND m4.money_type_id = m.money_type_id \n
                    ORDER BY date DESC LIMIT 1) as final_balance \n
                    from money as m \n
                     inner join kassa as k on m.kassa_id = k.id \n
                     inner join money_types as mt on m.money_type_id = mt.id \n
                     WHERE true = true\n
                      AND m.kassa_id IN (:cashbox)\n
                      AND m.money_type_id IN (:moneyTypes)\n
                    group by k.name, m.kassa_id, mt.name, m.money_type_id, m.date \n
                    UNION \n
                    SELECT uk.name as cashbox, \n
                        umt.name as currency_type, \n
                            date as date, \n
                            0.00 AS init_balance, \n
                            sum(um.summ) FILTER (WHERE um.summ > 0 and um.date between :startDate and :endDate) as income,\n
                            sum(um.summ) FILTER (WHERE um.summ < 0 and um.date between :startDate and :endDate) as outcome,\n
                            0.00 AS final_balance\n
                            from money as um\n
                                inner join kassa as uk on um.kassa_id = uk.id\n
                                inner join money_types as umt on um.money_type_id = umt.id\n
                            WHERE um.date between :startDate and :endDate \n
                             AND um.kassa_id IN (:cashbox)\n
                             AND um.money_type_id IN (:moneyTypes)\n
                            group by uk.name, um.kassa_id, umt.name, um.money_type_id, um.date\n
KoolReport commented on Sep 4, 2018

Everything seems fine hmm... let me ask dev.team if they can find sth. I will come back to you.

David Winterburn commented on Sep 4, 2018

Hi Dimitry,

It seems there's a bug with PdoDataSource when binding 2 or more array parameters. Please open the file koolreport/datasources/PdoDataSource.php and replace the following line:

$resultQuery = str_replace($paName,implode(",", $paramList),$query);

with this one:

$resultQuery = str_replace($paName,implode(",", $paramList),$resultQuery);

Let us know if it fixes the issue for you. Thanks!

Dimitry commented on Sep 4, 2018

Thanks a lot. That really helps :)

Solved for me!

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
solved
Inputs

Designed and built with by KoolPHP Inc.

Code licensed MIT.