CODEWITHSUNDEEP
phpjasper-reportsparameter-passing
Blessy Khauhelo
Blessy Khauhelo

Reputation: 145

How can I pass a parameter value from PHP to jasper report query?

I want to pass student number '2014000030' as parameter value so that i only get the report for that student. How do i do that using the sql query below the code. The below code is how i have done it, but its not working, where did I go it wrong?

PHP code

<?php
 //Import the PhpJasperLibrary
include_once('PhpJasperLibrary/tcpdf/tcpdf.php');
include_once("PhpJasperLibrary/PHPJasperXML.inc.php");

//database connection details

$server="localhost";
$db="student_portal";
$user="root";
$pass="";
$version="0.8b";
$pgport=5432;
$pchartfolder="./class/pchart2";

$value = '2014000030';

//display errors should be off in the php.ini file
ini_set('display_errors', 0);

//setting the path to the created jrxml file
$xml =  simplexml_load_file("test.jrxml");

$PHPJasperXML = new PHPJasperXML();
//$PHPJasperXML->debugsql=true;
$PHPJasperXML->arrayParameter=array("parameter1"=>$value);
$PHPJasperXML->xml_dismantle($xml);
$PHPJasperXML->transferDBtoArray($server,$user,$pass,$db);
$PHPJasperXML->outpage("I");    //page output method I:standard output  D:Download file
?>

SQL Query

SELECT i.stid,i.surname,i.first_name,i.other_names,i.dob,i.course,b.branch_name,r.exam_no,r.modules,m.module_name,r.result,rr.remark FROM modules m,info i,results r,branches b,result_remarks rr WHERE r.modules=m.module_code AND i.campus_code=b.branch_code AND i.stid=r.stid AND rr.result=r.remark

Upvotes: 1

Views: 5339

Answers (2)

Sergiu Costas
Sergiu Costas

Reputation: 560

I faced the same question last days too. I am using the same library for displaying Reports nicely in PHP based on reports generated in iReports. The question for me was: I needed to use some filters to refine the Report according to my needs. Usually, parameters I have used are dynamically according to the data from DB.

My approach is totally not inline with Documentation but it is more easy and applicable. I hope my approach will help someone else who would look for similar solution like mine too ;) It is not needed to add additional Parameters in report to refine report, it is simple just to overwrite the SQL parsed by PHP adding new parameters directly in SQL... Just a simple as my bellow example:

$customer = $_GET['ID'];
$xml =  simplexml_load_file("reports/customers.jrxml");

$PHPJasperXML = new PHPJasperXML();
$PHPJasperXML->xml_dismantle($xml);
$PHPJasperXML->sql ="SELECT ID, NAME, ADDRESS, IMAGE FROM CUSTOMERS WHERE ID = '$customer'";
$PHPJasperXML->transferDBtoArray($server,$user,$pass,$db);
$PHPJasperXML->outpage("I");

Upvotes: 0

Petter Friberg
Petter Friberg

Reputation: 21710

In this command

$PHPJasperXML->arrayParameter=array("parameter1"=>$value);

You are passing the value to jasperReport as an parameter with the parameter name "parameter1"

Define the parameter inside of your jrxml (use the class corresponding to your database)

<parameter name="parameter1" class="java.lang.String"/>

Then use it in query (i.stid=$P{parameter1}).

SELECT i.stid,i.surname,i.first_name,i.other_names,i.dob,i.course,b.branch_name,r.exam_no,r.modules,m.module_name,r.result,rr.remark 
FROM modules m,info i,results r,branches b,result_remarks rr 
WHERE i.stid=$P{parameter1} AND r.modules=m.module_code AND i.campus_code=b.branch_code AND i.stid=r.stid AND rr.result=r.remark

Its important to set correct class (same as database column) of $P{parameter1} and pass same class as parameter, since jasper report will use prepared statement when query is executed.

Note: you can use query expression i.stid='$P!{parameter1}' and jasper report will do string substitution, but this is not recommend since your code will allow sql injection.

Upvotes: 1

Related Questions