Parameterize the Data Source

Ravin Wijesinghe commented on Oct 28, 2020

Hi Support

We are drawing the data from MSSql. We want to call the database connection string from an external text file. We tried what is outlined here : https://thisinterestsme.com/php-return-variable-from-include-file/ but it did not work. The lines of code we want to call from an external .txt or php file is below:

return array(
            "dataSources"=>array(
                'sqlserver' => array(
                    'connectionString' => 'sqlsrv:Server=tcp:localhost;Database=sample-db',
                    'username' => 'sa',
                    'password' => 'ABC#123',
                ),            
            )
        );

Kindly help.

Markus Mohr commented on Oct 28, 2020

Hello, Ravin,

I have solved this in an analogous way for MySQL like this:

External file "DBSettings_odsreadall.php" (whatever YOU call it then):

<?php
	$DATABASESERVER   = 'localhost';
	$DATABASENAME     = 'databasename';
	$DATABASEUSERNAME = 'hellohereami';
	$DATABASEPASSWORD = 'whatever';
	$DATABASETYPE     = 'MySQL';
?>

And then the "index.php" file, part of which contains:

[...]
	if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
		require_once("../../DBSettings.php");
	} else {
		include_once("DBSettings_odsreadall.php");
	}

	global $DATABASESERVER, $DATABASEUSERNAME, $DATABASEPASSWORD, $DATABASENAME;
[...]

And then, still in "index.php":

	if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
		$report = new Report_global(array(
			"connectionString"=>"mysql:host=127.0.0.1;port=8889;dbname=$DATABASENAME",
			"username"=>$DATABASEUSERNAME,
			"password"=>$DATABASEPASSWORD
		));
	} else {
		$report = new Report_global(array(
			"connectionString"=>"mysql:host=127.0.0.1;port=3306;dbname=$DATABASENAME",
			"username"=>$DATABASEUSERNAME,
			"password"=>$DATABASEPASSWORD
		));
	}

	$report->run()->render();

And finally the file "Report.php" which contains the mode to address the database:

	if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
		class Report_global extends \koolreport\KoolReport {
		    use \koolreport\clients\Bootstrap;
		    protected function settings() {
		        return array(
		            "dataSources"=>array(
		                "patient"=>array(
		                    "connectionString"=>$this->params["connectionString"],
		                    "username"=>$this->params["username"],
		                    "password"=>$this->params["password"],
		                    "charset"=>"utf8"
		                ),
		            ),
		            "assets"=>array(
		                "path"=>"assets",
		                //"url"=>"http://localhost:8888/adipositas/admin/centers/koolreport/assets"
		                "url"=>"http://172.172.10.10:8888/adipositas/admin/centers/koolreport/assets"
		                //"url"=>"http://" . $_SERVER['SERVER_NAME'] . ":8888" . "/adipositas/admin/koolreport/assets"
		                //"url"=>"https://" . $_SERVER['SERVER_NAME'] . "/adipositas/admin/koolreport/assets"
		            )
		        );
		    }
		}
	} else {
		class Report_global extends \koolreport\KoolReport {
		    use \koolreport\clients\Bootstrap;
		    protected function settings() {
		        return array(
		            "dataSources"=>array(
		                "patient"=>array(
		                    "connectionString"=>$this->params["connectionString"],
		                    "username"=>$this->params["username"],
		                    "password"=>$this->params["password"],
		                    "charset"=>"utf8"
		                ),
		            ),
		            "assets"=>array(
		                "path"=>"assets",
		                //"url"=>"http://localhost:8888/adipositas/admin/centers/koolreport/assets"
		                //"url"=>"http://172.172.10.10:8888/adipositas/admin/centers/koolreport/assets"
		                //"url"=>"http://" . $_SERVER['SERVER_NAME'] . ":8888" . "/adipositas/admin/koolreport/assets"
		                "url"=>"https://" . $_SERVER['SERVER_NAME'] . "/adipositas/admin/centers/koolreport/assets"
		            )
		        );
			}
		}
	}

I hope that helps you. Admittedly a bit complicated, but I have to take care of different OS, different versions of PHP, and different versions of databases and credentials.

KoolReport commented on Oct 28, 2020

You do this:

Make your structure look like this

reports
├── MyReport.php
├── MyReport.view.php
├── DBConnection.php

The DBConnection.php contains following code:

<?php
return array(
            "dataSources"=>array(
                'sqlserver' => array(
                    'connectionString' => 'sqlsrv:Server=tcp:localhost;Database=sample-db',
                    'username' => 'sa',
                    'password' => 'ABC#123',
                ),            
            )
        );

and now in your MyReport.php, you can do:

class MyReport extends \koolreport\KoolReport
{
    protected function settings()
    {
        return include "DBConnection.php";
    }
}

Hope that helps.

P/S: If you are looking for making multiple resources but sharing same database connection, we prefer this way.

Ravin Wijesinghe commented on Oct 29, 2020

Hi Markus

Thanks very much for your prompt reply. We will give it a try. Yes we are making multiple reports (thus multiple folders) for the same database connection, so we will look at the alternative way suggested as well. It may take a little time to digest what is happening here.

Appreciate your Help. Ravin

Markus Mohr commented on Oct 29, 2020

Hello, Ravin,

my solution takes care of a lot of aspects. The KoolReport solution here is way shorter, so you might rather give this one a try.

Happy Coding!

KoolReport's Forum

Parameterize the Data Source #1690

Build Your Excellent Data Report