Select2 not working properly

Letalba commented on Jun 9, 2022

Every time I select the parameters in the Select2 boxes and hit submit the page shows me an HTTP ERROR 500.

This is what I have in my Report.php setup function:

		$query_params = array();
        if($this->params["bot_type"]!=array())
        {
            $query_params[":bot_type"] = $this->params["bot_type"];
        }
        if($this->params["bot_name"]!=array())
        {
            $query_params[":bot_name"] = $this->params["bot_name"];
        }
        if($this->params["event"]!=array())
        {
            $query_params[":event"] = $this->params["event"];
        }

		
		$this->src("bots")->query("select
                bot_type,
                bot_name,
                event
				from botfacts;
            ".(($this->params["bot_type"]!=array())?"and bot_type in (:bot_type)":"")."
            ".(($this->params["bot_name"]!=array())?"and bot_name in (:bot_name)":"")."
            ".(($this->params["event"]!=array())?"and event in (:event)":"")."
        ")->params($query_params)
        ->pipe($this->dataStore("botfacts"));

This is what I have in my Report.view.php:

        <form method="post">
          <div class="row">
            <div class="col-md-6">
              <div class="form-group" style="width:30%;">
                  <b>Select BotType</b>
                    <?php 
                        Select2::create(array(
                            "multiple"=>true,
                            "name"=>"bot_type",
                            "dataSource"=>$this->src("bots")->query("
                                select bot_type
                                from botfacts
                                group by bot_type
                            "),
                            "attributes"=>array(
                                "class"=>"form-control"
                            )
                        ));
                    ?>
              </div>    
      
              <div class="form-group" style="width:30%;">
                <b>Select BotName</b>
                  <?php 
                    Select2::create(array(
                        "multiple"=>true,
                        "name"=>"bot_name",
                        "dataSource"=>$this->src("bots")->query("
                            select bot_name
                            from botfacts
                            group by bot_name
                        "),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                  ?>                
              </div>
              
              <div class="form-group" style="width:30%;">
                <b>Select Event</b>
                  <?php 
                    Select2::create(array(
                        "multiple"=>true,
                        "name"=>"event",
                        "dataSource"=>$this->src("bots")->query("
                            select event
                            from botfacts
                            group by event
                        "),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                  ?>                
              </div> 
               
              <div class="form-group">
                  <button class="btn btn-primary">Submit</button>
              </div>    
            </div>
          </div>
            
        </form>
        <div style="width:25%;">
          <?php
            Table::create(array(
                "dataSource"=>$this->dataStore("botfacts"),
                "columns"=>array(
                    "bot_type",
                    "bot_name",
                    "event"
                ),
                "paging"=>array(
                    "pageSize"=>9
                ),
                "cssClass"=>array(
                    "table"=>"table-bordered"
                )
            ));
          ?>
        </div>
  </div>

Sebastian Morales commented on Jun 9, 2022

Do you use any framework like Laravel, Symphony, etc? If that's the case you might want to add CSRF token to your form before submitting or ajax requesting.

Letalba commented on Jun 9, 2022

No. Everything else in my page is working, I have some ColumCharts and DonutCharts getting the same data. The only thing not working is the submit button to the filters.

Part of the page looks like this:

The issue is with that Submit button.

Letalba commented on Jun 9, 2022

Okay I just found out that the problem is coming from here:

$this->src("bots")->query("select
                bot_type,
                bot_name,
                event
				from botfacts
            ".(($this->params["bot_type"]!=array())?"and bot_type in (:bot_type)":"")."
            ".(($this->params["bot_name"]!=array())?"and bot_name in (:bot_name)":"")."
            ".(($this->params["event"]!=array())?"and event in (:event)":"")."
        ")->params($query_params)
        ->pipe($this->dataStore("botfacts"));

The code crashes in this line : .(($this->params["bot_type"]!=array())?"and bot_type in (:bot_type)":"").

Basically I copied this from "Multiple Data Filters" example they have here in KoolReport, and I don't really understand what am I doing in here:

		$query_params = array();
        if($this->params["bot_type"]!=array())
        {
            $query_params[":bot_type"] = $this->params["bot_type"];
        }
        if($this->params["bot_name"]!=array())
        {
            $query_params[":bot_name"] = $this->params["bot_name"];
        }
        if($this->params["event"]!=array())
        {
            $query_params[":event"] = $this->params["event"];
        }

So, when I hit submit, the code crashes when it tries to the query:

		$this->src("bots")->query("select
                bot_type,
                bot_name,
                event
				from botfacts
            ".(($this->params["bot_type"]!=array())?"and bot_type in (:bot_type)":"")."
            ".(($this->params["bot_name"]!=array())?"and bot_name in (:bot_name)":"")."
            ".(($this->params["event"]!=array())?"and event in (:event)":"")."
        ")->params($query_params)
        ->pipe($this->dataStore("botfacts"));

Why? And how do I solve this?

Sebastian Morales commented on Jun 10, 2022

I think your sql query construction results in incorrect query. There's no WHERE keyword. Maybe try to add where 1=1 and echo it first like this:

$query = "select
                bot_type,
                bot_name,
                event
				from botfacts
                where 1=1
            ".(($this->params["bot_type"]!=array())?"and bot_type in (:bot_type)":"")."
            ".(($this->params["bot_name"]!=array())?"and bot_name in (:bot_name)":"")."
            ".(($this->params["event"]!=array())?"and event in (:event)":"")."
        ";
echo "query = $query <br>"; exit;
$this->src("bots")->query($query)->params($query_params)
...

In any case, copy the echoed query, replace the parameters :bot_type, :bot_name, :event with sample values and paste it into your database interface to make sure it runs correctly.

Letalba commented on Jun 14, 2022

I guess adding "where 1=1" worked, thanks!! :)

KoolReport's Forum

Select2 not working properly #2717

Build Your Excellent Data Report