Hi,

I'm have a site on 5.9.0. Upgrading direct to the latest 6.5.0 was a disaster. However, I narrowed down one of my issues to a problem with 5.16.2.

When I upgraded to 5.16.1, all appeared to work. However, on 5.16.2, no data shows in the DataGrid. I don't get any errors, just no data except for data in my BSelect.

I'm using MySQLDataSource with PHP 8.2 and DataGrids.

Upgraded to 5.16.2 and followed your instructions. There are no errors in the console and no data in the DataGrid. There are warnings, but there are warnings on 5.15.1 when things work perfectly, too.

It's also not a caching issue. I removed all assets from /assets/kool-assets [my custom assets folder] , ran the report again and still no data nor errors.

I've tried PHP 7.4, 8.1 and 8.2. Problem persists with all of these PHP versions.

So, using array, everything worked well. With this in mind, I started looking at vendor/koolreprt/core/src/datasources/MySQLDataSource.php

The problem is in this file and specifically the prepareAndBind function. This is the only function that changed in this file between 5.16.1 and 5.16.2.

If I upgrade to 5.16.2 and replace MySQLDataSource.php with the 5.16.1 version, everything works perfectly. I'll dig a little deeper to find out why 5.16.2 is broken for MySQL.

Upgrading to the latest KR Pro 6.5.0 and copying over 5.16.1 MySQLDataSource.php appears to fix all problems too but it's a very brief look over a few reports.

Any update on this?

Unfortunately not. The problem persists in the latest version. Using MySQL from 5.16.1 would be a short term fix but future upgrades would be very difficult to automate.

Is there an ETA for the fix?

Sorry for the delayed response, I've narrowed down the issue to using the DateTimePicker as parameters in a query. It doesn't seem to be affected by a BSelect.

If I replace :start and :end with actual dates, the report runs and shows data properly. If I leave in the :start and :end parameters, the report runs but shows no data in the DataGrid.

15.6.2 appears to have introduced this issue and it remains in the latest version.

A simple example is below...

Create a table called table and put in at least two columns, names and last_updated. Populate one or two rows and run the report.

test.php `

include ("../../../vendor/autoload.php");

class Test extends koolreport\KoolReport {

use \koolreport\clients\Bootstrap;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;

protected function defaultParamValues()
{
    $this->src('query_source')
    ->query("Select name from table;")
    ->pipe($this->dataStore('select_names_result'))
    ->requestDataSending();
    
    $data = $this->dataStore("select_names_result")->data();      
    
    $list = array();        
    foreach ($data as $item)
        $list[] = $item["name"];
           
    return array(
        "nameSelect"=>$list,
        "startDatePicker"=>date('Y-m-d 00:00:00', strtotime('-1 days')),
        "endDatePicker"=>date('Y-m-d 23:59:59', strtotime('-1 days')),
    );
}

protected function bindParamsToInputs()
{
    return array(
        "nameSelect"=>"nameSelect",
        "startDatePicker",
        "endDatePicker",
        "options"
    );
}

public function settings()
{
    $config = include "../../includes/config.php";
    return $config;
}

public function setup()
{

    $this->src('query_source')
    ->query("Select * from table
                Where last_updated between :start and :end
                    And name IN (:names)
                ;")
    ->params(array(
        ":names"=>$this->params["nameSelect"],
        ":start"=>$this->params["startDatePicker"],
        ":end"=>$this->params["endDatePicker"]
    ))
    ->pipe($this->dataStore('result_names'));
}

} `

Test.view.php `

use \koolreport\datagrid\DataTables;
use \koolreport\inputs\BSelect;    
use \koolreport\inputs\DateTimePicker;    

		BSelect::create(array(
			"name"=>"nameSelect",
			"placeholder"=>"Select Name",
			"multiple"=>true,
			"dataStore"=>$this->dataStore("select_names_result"),
			"dataBind"=>"name",
			"attributes"=>array(
				"size"=>10,
			),
			'options' => array(
				'numberDisplayed' => 5,
				'includeSelectAllOption' => true
			)
		));

		DateTimePicker::create(array(
			"name"=>"startDatePicker",
			"maxDate"=>"@endDatePicker",
			"format"=>"DD/MM/YYYY"
		));

		DateTimePicker::create(array(
			"name"=>"endDatePicker",
			"minDate"=>"@startDatePicker",
			"format"=>"DD/MM/YYYY"
		));

	DataTables::create(array(
	    "dataStore"=>$this->dataStore('result_names')
	));

config.php ` return array(

"dataSources"=>array(
        "query_source"=>array(
        "host"=>"ip address",
        "dbname"=>"database",
        "username"=>"user",
        "password"=>"password",
        "charset"=>"utf8",
        "class" => "\koolreport\datasources\MySQLDataSource"
    ),
),
"assets"=>array(
    "path"=>"F:\Work\htdocs\example.domain.com\public_html\assets\kool-reports",
    "url"=>"/assets/kool-reports"
),

); `

Sorry, the code tags don't like intermixed php and html. I stripped out all html formatting.

Ok, I've investigated more. I implemented your test "order_list", which was a little painful as that report is incorrect relating to the sample data. It's employee data and not order / order detail / products data. That report DID NOT work with the MySQLDataSource.

To be clear, when I run the report, it works fine but when I select a customer and LOAD, no data displays.

I'm not sure how you can't replicate this issue with my test report above. I tested it both on MySQL 5.7.31 and MariaDB 10.11.6 with the exact same results but you have to select data from all three widgets as your orders report ignores one of the widgets entirely if nothing is selected. In my report, I select ALL enteries by default when the report runs. This is handled automatically in my test report above.

I'm running PHP 8.2 but I doubt that this has anything to do with it.

What I did find, is that this may not be related to the DateTimePicker only.

I ran a few tests with interesting results. There are three parameters in this query; :start. :end and :names

The SQL query that I'm going to alter for these tests `

$this->src('query_source')
->query("Select * from table
            Where last_updated between :start and :end
                And name IN (:names)
            ;")

1. If I remove the ```And name IN (:names)``` to leave the below query, everything works fine

$this->src('query_source')
->query("Select * from table
            Where last_updated between :start and :end
            ;")

2. If I use hardcoded dates, to leave the below query, everything works fine

$this->src('query_source')
->query("Select * from table
            Where last_updated between '2024-01-01' and '2025-01-01'
                And name IN (:names)
            ;")

3. If I remove the dates entirely, to leave the below query, everything works fine

$this->src('query_source')
->query("Select * from table
            Where name IN (:names)
            ;")

4. However, the original query just fails to run without error

Sebastian,

That now works. Well done on finding and fixing the bug.

Let me apologise for my words in an earlier message. I was having a bad day unrelated to this issue. What I said was unprofessional, did nothing to help resolve the issue and shouldn't have been said. Please also send on my apologies to your dev team.

"I'm not sure how you can't replicate this issue with my test report above. I tested it both on MySQL 5.7.31 and MariaDB 10.11.6 with the exact same results"