I want to show column from the checked data. For example, I checked Lump and Web Break so there's only column Lump and Web Break. I searching about this but i cant found it.
heres my code for checkbox
</div>
<label>Select Defect</label>
<table>
<tr>
<td width='300px'><p><input type="checkbox" name="chk[]" value=".1"> Big Hole</p>
<p><input type="checkbox" name="chk[]" value=".2"> Big Pin Hole</p>
<p><input type="checkbox" name="chk[]" value=".3"> Detection Off</p>
<p><input type="checkbox" name="chk[]" value=".4"> Extreme Hole</p>
<p><input type="checkbox" name="chk[]" value=".5"> Pin Hole</p>
</td>
<td width='300px'><p><input type="checkbox" name="chk[]" value=".6"> Pin Light Spot</p>
<p><input type="checkbox" name="chk[]" value=".7"> Small Hole</p>
<p><input type="checkbox" name="chk[]" value=".8"> Uninspected</p>
<p><input type="checkbox" name="chk[]" value=".9"> Very Small Dark Spot</p>
<p><input type="checkbox" name="chk[]" value=".10"> Very Small Light Spot</p>
</td>
<td width='300px'><p><input type="checkbox" name="chk[]" value=".11"> Web Break</p>
<p><input type="checkbox" name="chk[]" value=".12"> Filter Small</p>
<p><input type="checkbox" name="chk[]" value=".13"> Edge Filter Small</p>
<p><input type="checkbox" name="chk[]" value=".14"> Filter Light Spot</p>
<p><input type="checkbox" name="chk[]" value=".15"> Mini Dark Spot</p>
</td>
<td width='300px'><p><input type="checkbox" name="chk[]" value=".16"> Small Dark Spot</p>
<p><input type="checkbox" name="chk[]" value=".17"> Lump</p>
<p><input type="checkbox" name="chk[]" value=".18"> Intensity Filter</p>
<p><input type="checkbox" name="chk[]" value=".19"> Big Dark Spot</p>
<p> </p>
</td>
</table>
</div>
here's datatable
<thead>
<th class="text-center" >Date Process</th>
<th class="text-center" >Big Hole</th>
<th class="text-center">Big Pin Hole</th>
<th class="text-center" >Detection Off</th>
<th class="text-center" >Extreme Hole</th>
<th class="text-center" >Pin Hole</th>
<th class="text-center" >Pin Light Spot</th>
<th class="text-center" >Small Hole</th>
<th class="text-center" >Uninspected</th>
<th class="text-center" >Very Small Dark Spot</th>
<th class="text-center" >Very Small Light Spot</th>
<th class="text-center" >Web Break</th>
<th class="text-center" >Filter Small</th>
<th class="text-center" >Edge Filter Small</th>
<th class="text-center" >Filter Light Spot</th>
<th class="text-center" >Mini Dark Spot</th>
<th class="text-center" >Small Dark Spot</th>
<th class="text-center" >Lump</th>
<th class="text-center" >Intensity Filter</th>
<th class="text-center" >Big Dark Spot</th>
</thead>
I get the data from database and using join
<?php
$no = 0;
$modal=mysqli_query($mysqli,"
SELECT t_name_file.PROCESS_TIME,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 1 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `1`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 2 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `2`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 3 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `3`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 4 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `4`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 5 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `5`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 6 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `6`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 7 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `7`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 8 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `8`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 9 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `9`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 10 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `10`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 11 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `11`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 12 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `12`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 13 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `13`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 14 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `14`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 15 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `15`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 16 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `16`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 17 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `17`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 18 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `18`,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 19 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `19`
FROM t_transaction
INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID
INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID
GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME");
while($defect=mysqli_fetch_array($modal)){
$no++;
?>
<tr>
<td class="text-center"><?php echo $defect['PROCESS_TIME']; ?></td>
<td class="text-center"><?php echo $defect['1']; ?></td>
<td class="text-center"><?php echo $defect['2']; ?></td>
<td class="text-center"><?php echo $defect['3']; ?></td>
<td class="text-center"><?php echo $defect['4']; ?></td>
<td class="text-center"><?php echo $defect['5']; ?></td>
<td class="text-center"><?php echo $defect['6']; ?></td>
<td class="text-center"><?php echo $defect['7']; ?></td>
<td class="text-center"><?php echo $defect['8']; ?></td>
<td class="text-center"><?php echo $defect['9']; ?></td>
<td class="text-center"><?php echo $defect['10']; ?></td>
<td class="text-center"><?php echo $defect['11']; ?></td>
<td class="text-center"><?php echo $defect['12']; ?></td>
<td class="text-center"><?php echo $defect['13']; ?></td>
<td class="text-center"><?php echo $defect['14']; ?></td>
<td class="text-center"><?php echo $defect['15']; ?></td>
<td class="text-center"><?php echo $defect['16']; ?></td>
<td class="text-center"><?php echo $defect['17']; ?></td>
<td class="text-center"><?php echo $defect['18']; ?></td>
<td class="text-center"><?php echo $defect['19']; ?></td>
</td>
</tr>
<?php } ?>
</div>
It works when i try to filter the column but not for fields.
Thanks, it works now.
here's my code
if (isset($_POST['chk'])){
$defect_query = "";
foreach ($_POST['chk'] as $id => $data) {
$defect_query = $defect_query.", COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN ".$data." THEN t_defect_class.DEFECT_CLASS_NAME END) AS `".$data."`";
}
$machine = $_POST['machine'];
$start_date = $_POST['start_date'];
$end_date = $_POST['end_date'];
$grade = $_POST['grade'];
$chk = $_POST['chk'];
?>
<thead>
<th class="text-center" >Date Process</th>
<?php
foreach ($_POST['chk'] as $id => $data) {
$get_defect_name = mysqli_query($mysqli, "SELECT * FROM `t_defect_class` WHERE `DEFECT_CLASS_ID` = $data");
$defect_name_table = mysqli_fetch_assoc($get_defect_name);
?>
<th class="text-center"><?php echo $defect_name_table['DEFECT_CLASS_NAME']; ?></th>
<?php } ?>
</thead>
<?php
$modal=mysqli_query($mysqli,"SELECT t_name_file.PROCESS_TIME".$defect_query." FROM t_transaction INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME");
$no = 0;
while($defect=mysqli_fetch_array($modal)){
$no++;
?>
<tr>
<td class="text-center"><?php echo $defect['PROCESS_TIME']; ?></td>
<?php foreach ($_POST['chk'] as $id => $data) { ?>
<td class="text-center"><?php echo $defect[$data]; ?></td>
<?php } ?>
</tr>
<?php
}
} ?>
You have to parse the values from the $_POST array and build your query with only the columns the user selected.
Remember to check that SOME values were entered, otherwise you'll select nothing which will generate an error. Also I'm not sure on the values you have on those checkboxes, so I made them integer numbers instead of decimals to match the SQL you provided.
Example of confirming the user checked at least one box -
if(isset($_POST['chk']){
// do the example stuff below and build your query
}else{
// use your existing query string as-is
}
?>
And then the building of a query based on which checkboxes were checked. Using the array example values as shown in the comment
<?php
// this would be $_POST['chk'] if the user checked
// those 3 particular checkboxes
$arr=array(7,8,9);
$queryString="SELECT t_name_file.PROCESS_TIME,";
foreach($arr as $conditionValue){
$queryString.="
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN ".$conditionValue." THEN t_defect_class.DEFECT_CLASS_NAME END) AS '".$conditionValue."' ,";
}
$queryString=trim($queryString,",");
$queryString.="FROM t_transaction
INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID
INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID
GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME";
print("
".$queryString."
");
?>
Generates
SELECT t_name_file.PROCESS_TIME,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 7 THEN t_defect_class.DEFECT_CLASS_NAME END) AS '7' ,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 8 THEN t_defect_class.DEFECT_CLASS_NAME END) AS '8' ,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 9 THEN t_defect_class.DEFECT_CLASS_NAME END) AS '9' FROM t_transaction
INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID
INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID
GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME
Finally, after making sure the queries are all good, I'd take out hte hard returns in the query string...