Multiple Inputs in Query

David Vanacore commented on Jun 3, 2020

Is it possible to have more then one input parameter in the view.php file? I'm able to get one input(startQtrPicker) to work, but I'm not sure how to add the second one.

This is what my view.php file looks like.

<?php

use \koolreport\datagrid\DataTables;
use \koolreport\inputs\Select2;

?> <html>

<div style="margin:30px;">
<head>
<title>Report</title>
</head>
<body>
    <h1>Report</h1>
    <form method="get">
    <div class="row"> 
        <div class="col-sm-1">
        From Quarter:
            <?php
            Select2::create(array(
                "name"=>"startQtrPicker",
                "dataStore"=>$this->dataStore("yearQtr"),
                "attributes"=>array(
                    "class"=>"form-control"),
                ));
            ?>
        </div>    
        <div class="col-sm-1">
        To Quarter:
            <?php
            Select2::create(array(
                "name"=>"endQtrPicker",
                "dataStore"=>$this->dataStore("yearQtr"),
                "attributes"=>array(
                    "class"=>"form-control"),
                ));
            ?>
        </div>
        <div class="form-group" style="margin-top:22px;">
        <button class="btn btn-md btn-primary">Search</button>
        </div>
    </form>
    </div>
    <pre><code><?php echo json_encode($this->params['startQtrPicker']) ?></code></pre>
    </div>
    <?php
    DataTables::create(array(
        'name' => 'Report',
        'dataSource' => function() {
            return $this->src('mysql2')
            ->query('
            SELECT * FROM Examples
            WHERE YearQtr = :startQtrPicker
            ')
            ->params(array(
                ":startQtrPicker"=>$this->params["startQtrPicker"],
            ));

        },
        "options" => array(
            "searching" => true,
            "paging" => true,
            "colReorder" => true,
            "order"=>array(
                array(0,"asc")),
            "pageLength" => 15, 
            ),
        "serverSide"=> true,
    ));
    ?>
</body>

</html>

KoolReport commented on Jun 3, 2020

The way you add "endQtrPicker" is correct

David Vanacore commented on Jun 3, 2020

When I add

            ->params(array(
                ":startQtrPicker"=>$this->params["startQtrPicker"],
                ":endQtrPicker"=>$this->params["endQtrPicker"],
            ));

To the query params, I get the error:

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (View: ...reports.blade.php)

The reports.blade.php looks like this:

@extends('layouts.app')

@section('content')
<div style="margin:30px;">
<?php $reports->render(); ?>
@endsection

David Vanacore commented on Jun 3, 2020

I figured it out. If I don't use the second params in the query I get the error. Once I added into the query, it works fine. Thank you.

KoolReport commented on Jun 3, 2020

Awesome, under the hood, it is because PDO requires available parameters to be in the query. Great that you have figured it out.

KoolReport's Forum

Multiple Inputs in Query #1469

Build Your Excellent Data Report