KoolReport's Forum

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

How to use input variable in card sql? #2182

Open Niall McGuirk opened this topic on on Jul 5, 2021 - 14 comments
Niall McGuirk commented on Jul 5, 2021

I'm trying to use a select input box to select data for a card by SQL. So I need to use the Input variable in the SQL query of the Card value.

I need to use the Dropdown Search bar value as a Where clause for the YearMonth clause that the Card value uses. How do I use a variable within an SQL statement?

Individual Card Creation Code

<?php
			

            Card::create(array(

				"dataSource"=>$this->dataStore("metrics"),
                "value"=>$this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and YearMonth=DATE_FORMAT(CURDATE(), '%y%m') and MetricID=1"),
                "baseValue"=>$this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and YearMonth=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 MONTH), '%y%m') and MetricID=1"),
                "title"=>"New Clients Acquired",
                "cssClass"=>array(
                    "card"=>"bg-info",
                    "title"=>"text-white",
                    "value"=>"text-white"
                )
            ));
            ?>

Drop Down Creation code

<?php 
					//DropDown which lists Year/Months

					
					Select2::create(array(
                            "name"=>"searchfor",
                            "dataStore"=>$this->dataStore("searchfor"),
			    "dataSource"=>$this->src("delv_kpi")->query("
                            Select Distinct YearMonth from tbl_kpimetrics  "),
                            "dataBind"=>"YearMonth",
                            "attributes"=>array(
                                "class"=>"form-control",
                            )
                        ));
                    ?>
Sebastian Morales commented on Jul 6, 2021

You could access the input value by $this->params["searchfor"]. However, your Card create wouldn't work because its value and baseValue need to be a numeric value. Using $this->src(...)->query(...) wouldn't work in a report view. It only works in a report setup. For your case the code should roughly be like this:

//MyReport.php
function setup()
{
    //Use $this->params["searchfor"] (and check for its existence) to build sql queries, then pipe to datastores
    ...
    ->pipe($this->dataStore("cardValue"));
    ...
    ->pipe($this->dataStore("cardBaseValue"));
}

//MyReport.view.php
Card::create(array(
    "value" => $this->dataStore("cardValue")->data()[0]["MetricValue"], //get data's first row's column "MetricValue", this column name depends on your built sql query select
    "baseValue" => $this->dataStore("cardBaseValue")->data()[0]["MetricValue"],
    ...
Niall McGuirk commented on Jul 6, 2021

Hi Sebastian, thanks for the response. Does that mean that I'll have to create a dataStore in setup for cardValue and cardBaseValue for each card on the report?

Niall McGuirk commented on Jul 6, 2021

I've added the code, and it gives me an Notice: Undefined offset: 0 in C:\Apache24\htdocs\examples\reports\Niall\KPI\MyReport.view.php on line 62. Does this require an sql output of more than 1 row?

I've currently got the sql returning one row, and am duplicating the code again for the base value. Is there an easier way to do this?

protected function setup()
    {
		//Value
        $query_params = array();
        if($this->params["searchfor"]!=array())
        {
            $query_params[":searchfor"] = $this->params["searchfor"];
        }

        $this->src('delv_kpi')->query("
            SELECT * FROM delv.tbl_kpimetrics
            Where 
				MetricID = 1 and
                Client = 'DELV'
                
                ".(($this->params["searchfor"]!=array())?" and tbl_kpimetrics.YearMonth in (:searchfor)":"")."
        ")->params($query_params)
       ->pipe($this->dataStore("metricsValOne"));

		//BaseValue 
        $query_params = array();
        if($this->params["searchfor"]!=array())
        {
            $query_params[":searchfor"] = $this->params["searchfor"];
        }

        $this->src('delv_kpi')->query("
            SELECT * FROM delv.tbl_kpimetrics
            Where 
				MetricID = 1 and
                Client = 'DELV'
                
                ".(($this->params["searchfor"]!=array())?" and tbl_kpimetrics.YearMonth in (:searchfor)":"")."
        ")->params($query_params)
       ->pipe($this->dataStore("metricsBValOne"));

    }

This is the Card code, using the data from the dataStore:

Card::create(array(

				"dataSource"=>$this->dataStore("metrics"),
                "value"=>$this->dataStore("cardValue")->data()[0]["metricsValOne"],
				/* $this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and YearMonth=DATE_FORMAT(CURDATE(), '%y%m') and MetricID=1"), */
                "baseValue"=>$this->dataStore("cardBaseValue")->data()[0]["MetricValue"],
				/* $this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and YearMonth=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 MONTH), '%y%m') and MetricID=1"), */
                "title"=>"New Clients Acquired",
                "cssClass"=>array(
                    "card"=>"bg-info",
                    "title"=>"text-white",
                    "value"=>"text-white"
                )
            ));
Sebastian Morales commented on Jul 7, 2021

You can retrieve data anyway you want using $this->params["searchfor"] and fill in for Card's "value" and "baseValue". The way I showed you uses datastores in setup. You could check for its first row existence and fill in a default value if there's no row:

    "value"=>isset($this->dataStore("cardValue")->data()[0]) ? $this->dataStore("cardValue")->data()[0]["metricsValOne"] : $defaultCardValue,
Niall McGuirk commented on Jul 8, 2021

Hi Sebastian, I've tried using an if statement to determine which value the card shows. The page loads with the default value. (in Blue)

However, when I input a vaue into the input box and search, it gives a PDoDataSource.php Error. What is the cause of the error; What parameter is not being bound/set?

I am trying to avoid use dataStores, as I have to have the MetricID for each Card hardcoded in the Value/Basevalue where clause.

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: no parameters were bound in C:\Apache24\htdocs\koolreport\core\src\datasources\PdoDataSource.php on line 428

<?php
			$searchfor = $this->params["searchfor"];
			$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')";
		if (!empty($this->params["searchfor"])) { //if param "searchfor" has a value
				$searchCondition = " and YearMonth=(:searchfor)";
		
				$params = [
					":searchfor" => $this->params["searchfor"]
				];
		
	} 	else {
			$customerCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')"; //else use default             currentdate value
			$params = [];
		}
            Card::create(array(

				"dataSource"=>$this->dataStore("metrics"),
				 "value"=>$this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchCondition),  //my Original code
				"value"=>$this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchCondition), 
				/* $this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and YearMonth=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 MONTH), '%y%m') and MetricID=1"), */ //my Orginal Code
                "title"=>"New Clients Acquired",
                "cssClass"=>array(
                    "card"=>"bg-info",
                    "title"=>"text-white",
                    "value"=>"text-white"
                )
            ));
            ?>
Sebastian Morales commented on Jul 9, 2021

Pls replace this line:

$searchCondition = " and YearMonth=(:searchfor)";

with either this one:

$searchCondition = " and YearMonth=:searchfor"; // = require no parenthesis ()

or this one:

$searchCondition = " and YearMonth IN (:searchfor)"; // use parenthesis () for IN

Let us know how it works for you. Tks,

Niall McGuirk commented on Jul 9, 2021

Update

I've replaced the top line with your second one. It now uses the default date value, of 2105 which is 25. I also changed the variable $customerCondition to $search condition in the else statement:

$searchfor = $this->params["searchfor"];
//$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')";
if (!empty($this->params["searchfor"])) { //if param "searchfor" has a value
				$searchCondition = " and YearMonth=:searchfor"; // = require no parenthesis ()
		
				$params = [
					":searchfor" => $this->params["searchfor"]
				];
		
		} 	else {
			$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')"; //else use default currentdate value
			$params = [];
		}

Which makes it show 25, the value of currentdate (2107), instead of selected date, which would be 5. So it seems to be using the else condition only, and never getting into the if statement.

Problem

The main problem is when I search, with any value, it gives me this error:

"Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: no parameters were bound in C:\Apache24\htdocs\koolreport\core\src\datasources\PdoDataSource.php on line 428

Fatal error: Uncaught Exception: Query Error >> [] >> SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1 and YearMonth=:searchfor in C:\Apache24\htdocs\koolreport\core\src\datasources\PdoDataSource.php:433 Stack trace: #0 C:\Apache24\htdocs\koolreport\core\src\core\DataSource.php(108): koolreport\datasources\PdoDataSource->start() #1 C:\Apache24\htdocs\koolreport\core\src\core\Node.php(365): koolreport\core\DataSource->requestDataSending() #2 C:\Apache24\htdocs\koolreport\core\src\core\Widget.php(520): koolreport\core\Node->requestDataSending() #3 C:\Apache24\htdocs\koolreport\core\src\widgets\koolphp\Card.php(130): koolreport\core\Widget->standardizeDataSource(Object(koolreport\datasources\PdoDataSource), Array) #4 C:\Apache24\htdocs\koolreport\core\src\widgets\koolphp\Card.php(87): koolreport\widgets\koolphp\Card->processScalar(Object(koolreport\datasources\PdoDataSource)) #5 C:\Apache24\htdocs\koolreport\core\src\core\Widget.php(157): koolreport\widgets\koolphp\Card->onInit() #6 C:\Apache24\htdoc in C:\Apache24\htdocs\koolreport\core\src\datasources\PdoDataSource.php on line 433"

What might be causing this?

Sebastian Morales commented on Jul 9, 2021

This is most likely a mismatch between your parameterized query and your parameters. Pls print out your query and params right before you query and bind them and pipe to a datastore like this:

    echo "query=$query<br>";
    echo "params="; print_r($params); echo "<br>";
    $this->src(...)
    ->query($query)
    ->params($params)
    ...

Post the output result for us to check it for you. Tks,

Niall McGuirk commented on Jul 9, 2021

My setup () function in MyReport.php is empty, so I'm not currently using dataStores. I'm not sure how to set the query up in the dataStore so I can just append the MetricID = x for each card on the .view.php. Do I need to use a dataStore for this to work?

This is the code for the card in MyReport.view.php:

	<!--New Clients Acquired-->
        <div class="col-md-3">
            <?php
			$searchfor = $this->params["searchfor"];
			//$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')";
			if (!empty($this->params["searchfor"])) { //if param "searchfor" has a value
				$searchCondition = " and YearMonth=:searchfor"; // = require no parenthesis ()
		
				$params = [
					":searchfor" => $this->params["searchfor"]
				];
		
		} 	else {
			$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')"; //else use default currentdate value
			$params = [];
		}
            Card::create(array(

				"dataSource"=>$this->dataStore("metrics"),
				 "value"=>$this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchCondition),  //my Original code
				"value"=>$this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchCondition), 
				/* $this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and YearMonth=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 MONTH), '%y%m') and MetricID=1"), */ //my Orginal Code
                "title"=>"New Clients Acquired",
                "cssClass"=>array(
                    "card"=>"bg-info",
                    "title"=>"text-white",
                    "value"=>"text-white"
                )
            ));
            ?>

        </div>
Sebastian Morales commented on Jul 9, 2021

I think you forgot to use ->params($params) after the query method. To use the basic structure pls try this:

//MyReport.php
function setup()
{
			$searchfor = $this->params["searchfor"];
			//$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')";
			if (!empty($this->params["searchfor"])) { //if param "searchfor" has a value
				$searchCondition = " and YearMonth=:searchfor"; // = require no parenthesis ()
		
				$params = [
					":searchfor" => $this->params["searchfor"]
				];
		
		} 	else {
			$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')"; //else use default currentdate value
			$params = [];
		}
                $this->src("delv_kpi")
                ->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchCondition)
                ->params($params)
                ->pipe($this->dataStore("cardValue"));

                $this->src("delv_kpi")
                ->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchCondition)
                ->params($params)
                ->pipe($this->dataStore("baseCardValue"));
...


//MyReport.view.php
Card::create(array(
    "value"=>isset($this->dataStore("cardValue")->data()[0]) ? $this->dataStore("cardValue")->data()[0]["MetricValue"] : $defaultCardValue, // define your $defaultCardValue previously
    "baseValue"=>isset($this->dataStore("baseCardValue")->data()[0]) ? $this->dataStore("baseCardValue")->data()[0]["MetricValue"] : $defaultBaseCardValue, // define your $defaultBaseCardValue previously
    ...
Niall McGuirk commented on Jul 9, 2021

Update

Thanks. That works. So it shows the metricID for the selected and searched YearMonth. However, the baseValue changes to be the same as the Value, if I input a variable. I've created a variable for the BaseCondition which -1 from the currentdate. However, I don't know how to do this with the searchfor (input) variable. I'm not sure what the ":searchfor" is or what the significance of the colon is.

Screenshot showing empty input, showing 8 as basevalue, and 25 as CardValue.

Problem

I've created a searchConditionBase, which is the currentdate -1 month. Showing last months value. I'm unsure how to replicate this for inside the if statement, when the Value is searched for. When I search for a value, and then another, it uses the previous input value, instead of the one in the DB table.

Screenshot after searching for 2105; baseValue 5, CardValue 5 So if I input 2105 and search, then input 2107 and search. For 2107, The baseValue of the Card will show the MetricID for 2105 (5), instead of 2106 (metricID 8) which it should be showing.

Screenshot after seraching for 2107; BaseValue 5 instead of 8, CardValue 25

Here is the MyReport.php code:

protected function setup()

{
	$searchfor = $this->params["searchfor"];
		//$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')";
		if (!empty($this->params["searchfor"])) { //if param "searchfor" has a value
			$searchCondition = " and YearMonth=:searchfor"; // = require no parenthesis ()
	
			$params = [
				":searchfor" => $this->params["searchfor"]
			];
	
	} 	else {
		$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')"; //else use default currentdate value
		$searchConditionBase = " and YearMonth=DATE_FORMAT(Date_ADD(CURDATE(), Interval -1 Month),  '%y%m')"; //else use default currentdate value
		
		$params = [];
	}
            $this->src("delv_kpi")
            ->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchCondition)
            ->params($params)
            ->pipe($this->dataStore("cardValue"));

            $this->src("delv_kpi")
            ->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchConditionBase)
            ->params($params)
            ->pipe($this->dataStore("baseCardValue"));

}
Sebastian Morales commented on Jul 12, 2021

So you know the last 2 digits represent month in a YearMonth string. We could add/substract the month in param to use for search base query like this:

if (!empty($this->params["searchfor"])) { //if param "searchfor" has a value
			$searchCondition = " and YearMonth=:searchfor"; // = require no parenthesis ()
	
			$params = [
				":searchfor" => $this->params["searchfor"]
			];

			$searchConditionBase = " and YearMonth=:searchforBase"; 
			$searchfor = $this->params["searchfor"];
			$year = substr($searchfor, 0, 2);
			$month = substr($searchfor, 2, 2);
			$searchforBase = $year . (1 * $month - 1); 
			$paramsBase = [
				":searchforBase" => $searchforBase
			];	
	} 	else { ...
...
            $this->src("delv_kpi")
            ->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchConditionBase)
            ->params($paramsBase)
            ->pipe($this->dataStore("baseCardValue"));

Hope this helps. Tks,

Niall McGuirk commented on Jul 14, 2021

Hi Sebastian,

I've implemented your code. Which works in the else condition, when the default variable is set to current, and current -1. However, the if condition gives these errors:

Warning: A non-numeric value encountered in C:\Apache24\htdocs\koolreport\core\src\widgets\koolphp\Card.php on line 184

Warning: A non-numeric value encountered in C:\Apache24\htdocs\koolreport\core\src\widgets\koolphp\Card.php on line 184

Warning: Division by zero in C:\Apache24\htdocs\koolreport\core\src\widgets\koolphp\Card.php on line 184

Warning: number_format() expects parameter 1 to be float, string given in C:\Apache24\htdocs\koolreport\core\src\core\Utility.php on line 148

If Condition Output Screenshot showing 2107 selected, and errors with baseValue as infinite?

Else Condition Output Screenshot showing empty input, with BaseValue as 8 (2106)

This is the Myreport,php code:

protected function setup()
    {		//Sebastians code 
		$searchfor = $this->params["searchfor"];
		//$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')";
		if (!empty($this->params["searchfor"])) { //if param "searchfor" has a value
			$searchCondition = " and YearMonth=:searchfor"; // = require no parenthesis ()
	
			$params = [
				":searchfor" => $this->params["searchfor"]
			];

			$searchConditionBase = " and YearMonth=:searchforBase"; 
			$searchfor = $this->params["searchfor"];
			$year = substr($searchfor, 0, 2);
			$month = substr($searchfor, 2, 2);
			$searchforBase = $year . (1 * $month - 1); 
			$paramsBase = [
				":searchforBase" => $searchforBase
				];	
		} 		
		
		else { 
				$searchCondition = " and YearMonth=DATE_FORMAT(CURDATE(), '%y%m')";
				$searchConditionBase = " and YearMonth=DATE_FORMAT(Date_ADD(CURDATE(), Interval -1 Month),  '%y%m')";
				echo "else is working";	
		}

		$this->src("delv_kpi")
		->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchCondition)
		->params($params)
		->pipe($this->dataStore("cardValue")); //dataStore CardValue

		$this->src("delv_kpi")
		->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and MetricID=1" . $searchConditionBase)
		->params($paramsBase)
		->pipe($this->dataStore("baseCardValue")); //dataStore cardBaseValue

This is the view.php for the card I'm using:

        <div class="col-md-3">
            <?php
			
			$defaultCardValue = "";
			$defaultBaseCardValue = "";
			
			Card::create(array(
				"dataSource"=>$this->dataStore("metrics"),
                "value"=>isset($this->dataStore("cardValue")->data()[0]) ? $this->dataStore("cardValue")->data()[0]["MetricValue"] : $defaultCardValue, // define your $defaultCardValue previously
				"baseValue"=>isset($this->dataStore("baseCardValue")->data()[0]) ? $this->dataStore("baseCardValue")->data()[0]["MetricValue"] : $defaultBaseCardValue, // define your $defaultBaseCardValue previously
                "title"=>"New Clients Acquired",
                "cssClass"=>array(
                    "card"=>"bg-info",
                    "title"=>"text-white",
                    "value"=>"text-white"
                )
            ));
Sebastian Morales commented on Jul 15, 2021

Pls echo your sql query (and copy, paste it to your database interface to run) and print out your datastore's data and find out the erred values. Rgds,

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
help needed
Inputs

Designed and built with by KoolPHP Inc.

Code licensed MIT.