Search code examples
phpjasper-reportsparameter-passing

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
Solution

  • 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.