I have created an PostgreSQL database(with pg-admin) which contains more than 10 tables filled with Arsenic test of Different district. i need to generate the html table as per the user input from html form.
Additionally I have put, under my html forms, some check boxes which contains names of districts to be selected and by checking them you can restrict the data of which district to select and which one not.
Each checkbox represent the name of District (which are individual table or schema in PostgreSQL) of my database; so along with the selection of check boxes (user can select one checkbox or multiple), anything that is checked is displayed.
So far I managed to write a php script that connects to the database, display error message when none of my check boxes.
Problem: i know how to select individual district (i.e. District here is individual table) but not able to give multiple choices to user.
Information: All tables have same column name and only data differs as per change in district name.
what i have done:- I have visited many solutions in Stakoverflow but all of the suggest me to select the fields of same table dynamicaly but not of tables i have visited:-
http://stackoverflow.com/questions/25527975/how-to-use-checkboxes-to-retrieve-specific-data-in-a-database
This is a simple html form for only Two district to check.
<html>
<head>
<title>Jasper Report</title>
</head>
<body>
<h1 align="center">ARSCENIC TEST REPORT</h1>
<b>Districts: </b> <br>
<form method="post" action="app.php">
<input type="checkbox" name="reg1" value="Banke" checked /> Banke<br>
<input type="checkbox" name="reg2" value="Bara" /> Bara <br><br>
<input type="submit" value="Generate"/>
</form>
</body>
</html>
the PHP code for the selection of individual district table is
<?php
require_once __DIR__ . "/vendor/autoload.php";
use Jaspersoft\Client\Client;
$c = new Client(
"localhost",
"8080",
"jasperadmin",
"jasperadmin",
"/jasperserver"
);
//$dist = $_GET['dist'];
//echo $dist;
$db = pg_connect('host=localhost port=5433 dbname=BankeDB user=postgres password=admin');
$userclass = array('0-5','6-10','11-15', '>15','Total');
$btotal = array();
$query = "select
sum(case when ".'"district_n"'." ='Banke' AND ".'"vdc_name"'."='Belahari' AND ".'"NO_OF_USERS"'." <= '5' AND ".'"conc_arsc"'." <= '10' then 1 else 0 end),
sum(case when ".'"district_n"'."='Banke' AND ".'"vdc_name"'."='Belahari' AND ".'"NO_OF_USERS"'." >= '6' AND ".'"NO_OF_USERS"'." <= '10' AND ".'"conc_arsc"'." <= '10' then 1 else 0 end),
sum(case when ".'"district_n"'."='Banke' AND ".'"vdc_name"'."='Belahari' AND ".'"NO_OF_USERS"'." >= '11' AND ".'"NO_OF_USERS"'." <= '15' AND ".'"conc_arsc"'." <= '10' then 1 else 0 end),
sum(case when ".'"district_n"'."='Banke' AND ".'"vdc_name"'."='Belahari' AND ".'"NO_OF_USERS"'." > '15' AND ".'"conc_arsc"'." <= '10' then 1 else 0 end),
sum(case when ".'"district_n"'."='Banke' AND ".'"vdc_name"'."='Belahari' AND (".'"NO_OF_USERS"'."<='5' or (".'"NO_OF_USERS"'." >='6' and ".'"NO_OF_USERS"'." <='10') or (".'"NO_OF_USERS"'." >='11' and ".'"NO_OF_USERS"'." <='15') or ".'"NO_OF_USERS"'." >'15') AND ".'"conc_arsc"'." <= '10' then 1 else 0 end)
from public.".'"Arsenic_Test_Banke"'."";
//echo $query;
$btresult = pg_query($db, $query);
while($btresults = pg_fetch_row($btresult)){
$count = count($btresults);
$y = 0;
while ($y < $count)
{
$c_row = current($btresults);
$btotal[] = $c_row;
next($btresults);
$y = $y + 1;
}
}
?>
/*
Other related queries here as above
*/
<table width="600" height="300" cellspacing="2" border="1" align="center">
<tr>
<th colspan=13>Tubewells by Arsenic Concentration Levels</th>
</tr>
<tr>
<th>User Class (No of Users)</th>
<th>No.(0-10)</th>
<th>%(0-10)</th>
<th>No.(11-50)</th>
<th>%(11-50)</th>
<th>No.(51-100)</th>
<th>%(51-100)</th>
<th>No.(101-300)</th>
<th>%(101-300)</th>
<th>No.(>300)</th>
<th>%(>300)</th>
<th>Total</th>
<th>%</th>
</tr>
<tr>
<?php
for($i=0; $i<5; $i++){
?>
<tr>
<td><?php echo $userclass[$i];?></td>
<td><?php echo $btotal[$i];?></td>
<td><?php echo $perb10[$i];?></td>
<td><?php echo $bettotal[$i];?></td>
<td><?php echo $pbet[$i];?></td>
<td><?php echo $b51_100total[$i];?></td>
<td><?php echo $pb51_100[$i];?></td>
<td><?php echo $bt101_300total[$i];?></td>
<td><?php echo $pb101_300[$i];?></td>
<td><?php echo $abov300total[$i];?></td>
<td><?php echo $pabov300[$i];?></td>
<td><?php echo $total[$i];?></td>
<td><?php echo $ptotal[$i];?></td>
</tr>
<?php
}
?>
</table>
<?
pg_close($db);
?>
Now the problem is how to make the multiple choices for table so that user can select multiple district at a time to analyze the merged data.
thank you all.
