KoolReport's Forum

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

Using CheckBoxList in MySQL WHERE IN clause #3265

Open Adolfo Alonso opened this topic on on Mar 13 - 1 comments

Adolfo Alonso commented on Mar 13

Hello, im trying to use CheckBoxList to select the items that will be used on a MySQL WHERE IN clause. So far I have populated the CheckBoxList but have the following problems.

  1. Everytime I click Submit the selected items clear
  2. How can I default all the items when first loading the site?
  3. How would I use the array on a WHERE IN clause?

MyReport.php

<?php
	// Require autoload.php from koolreport library
	require_once "../../koolreport/core/autoload.php";
	
	use \koolreport\processes\CalculatedColumn;
	use \koolreport\processes\Sort;
	use \koolreport\processes\AggregatedColumn;
	use \koolreport\processes\AccumulativeColumn;
	
	//Step 2: Creating Report class
	class MyReport extends \koolreport\KoolReport
	{
		use \koolreport\amazing\Theme;
		use \koolreport\inputs\Bindable;
		use \koolreport\inputs\POSTBinding;
		
		protected function defaultParamValues()
		{
			return array(
				"startDatePicker"=>date("Y-m-d 00:00:00"),
				"checkBoxList"=>array(),
			);
		}
		
		protected function bindParamsToInputs()
		{
			return array(
			"startDatePicker",
			"checkBoxList",
			);
		}
                ....
                public function setup()
		{
			
			$this->src('automaker')
			->query("SELECT 
					c.nombre AS conductor,
					g.confirmacion_local_conductor AS sucursal,
					SUM(IF(MONTH(g.eventtimemx) = 1, 1, 0)) AS Ene,
					SUM(IF(MONTH(g.eventtimemx) = 2, 1, 0)) AS Feb,
					SUM(IF(MONTH(g.eventtimemx) = 3, 1, 0)) AS Mar,
					SUM(IF(MONTH(g.eventtimemx) = 4, 1, 0)) AS Abr,
					SUM(IF(MONTH(g.eventtimemx) = 5, 1, 0)) AS May,
					SUM(IF(MONTH(g.eventtimemx) = 6, 1, 0)) AS Jun,
					SUM(IF(MONTH(g.eventtimemx) = 7, 1, 0)) AS Jul,
					SUM(IF(MONTH(g.eventtimemx) = 8, 1, 0)) AS Ago,
					SUM(IF(MONTH(g.eventtimemx) = 9, 1, 0)) AS Sep,
					SUM(IF(MONTH(g.eventtimemx) = 10, 1, 0)) AS Oct,
					SUM(IF(MONTH(g.eventtimemx) = 11, 1, 0)) AS Nov,
					SUM(IF(MONTH(g.eventtimemx) = 12, 1, 0)) AS Dic
					FROM tbl_guardian g
					INNER JOIN tbl_conductor c ON c.conductorid = g.conductor
					WHERE YEAR(g.eventtimemx) = :anio
					AND confirmacion_clasificacion IN ('somnolencia', 'bostezar')
					AND c.estatus = 1
					AND g.cla_fp <> 1
					AND g.conductor NOT IN (0, 22)
					AND g.confirmacion_local_conductor IN (:sucursales)
					GROUP BY conductor, sucursal")
			->params(array(
                        ":anio"=>		        $this->params["startDatePicker"],
		        ":sucursales"=>	$this->params["checkBoxList"]
                        ))
			->saveTo($source);
Sebastian Morales commented on Mar 14

Pls post your report view file and check our example code at the end of the following example for using array parameter in sql select query:

https://www.koolreport.com/examples/reports/inputs/order_list/

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
None yet

None