I have multiple select options. however, i want when i select 1 option or two or whatever to change the columns <th></th> of html dynamically and get data from database
Currently I have created the multiple select option and html table
<select class="selectpicker" id="sensor" name="sensor[]" multiple>
<option
value="temperature">Temperature</option>
<option value="humidity">Humidity</option>
<option value="presure">Presure</option>
<option
value="level">Level</option>
</select>
$query = "SELECT ".$selectedSensorOption." from p1";
$result = $db_handle->runQuery($query);
foreach ($result as $key => $value) {
<table>
<tr>
<th>$result</th>
</tr>
<tr>
<td>$result</td>
I want to get dynamic columns and fields as well from MySQL db based on multiple select options
first you have to get the headers, you can use explode to get the header keys. you'll use two loops, the first one to build the header and the second one to build the body. inside the body loop, you have to use another loop to get the correct value for each column
<select class="selectpicker" id="sensor" name="sensor[]" multiple>
<option value="temperature">Temperature</option>
<option value="humidity">Humidity</option>
<option value="presure">Presure</option>
<option value="level">Level</option>
</select>
<?php
$query = "SELECT ".$selectedSensorOption." from p1";
$results = $db_handle->runQuery($query);
//get the headers if $selectedSensorOption is not an array
$headers = explode(',', $selectedSensorOption)
?>
<table>
<thead>
<tr>
<!-- loop and fill the header -->
<?php foreach ($headers as $header) : ?>
<th><?= ucfirst($header) ?></th>
<?php endforeach; ?>
</tr>
</thead>
<tbody>
<!-- loop and fill the body-->
<?php foreach ($results as $result) : ?>
<tr>
<!-- loop and fill each column -->
<?php foreach ($headers as $header) : ?>
<th><?= $result[$header] ?></th>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
A straight forward way of doing this is to use the key values of the result to act as the headers and then output each row of data as the values (comments in code)...
// Output table start and header row
echo "<table><tr>";
// Use the first rows results as the header values
foreach ( $result[0] as $header => $value ) {
echo "<th>{$header}</th>";
}
echo "</tr>";
// Output each data row
foreach ($result as $values) {
echo "<tr>";
// Loop over each item in the row and output the value
foreach ( $values as $value ) {
echo "<td>{$value}</td>";
}
echo "</tr>";
}
echo "</table>";
You can create shorter code (using implode() etc.), but sometimes simpler code is easier to start with and maintain.
Using the returned column names means you may be able to use specific headings using column aliases - something like
id as `User ID`, fullname as `Name`
I used AJAX. Hope this helps ;D
read.php
<?php
//include header
header('Content-Type: application/json');
$conn= mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno()){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$type = $_GET["type"];
if($type == "GetCategory"){
//SAMPLE QUERY
$sql = "SELECT CategoryID,CategoryName from Category";
$data = array();
$results = $db -> query($sql);
while($row = mysqli_fetch_assoc($results)){
$data[] = $row;
}
echo json_encode($data);
}
if($type == "GetSubCategory"){
$CategoryID = $_POST["CategoryID"];
//SAMPLE QUERY
//LET'S ASSUME THAT YOU HAVE FOREIGN KEY
$sql = "SELECT SubCategoryID,SubCategoryName from SubCategory where CategoryID = '".$CategoryID."'";
$data = array();
$results = $db -> query($sql);
while($row = mysqli_fetch_assoc($results)){
$data[] = $row;
}
echo json_encode($data);
}
?>
index.html
<select id="category"></select>
<select id="subcategory"></select>
<!--PLEASE INCLUDE JQUERY RIGHT HERE E.G. <script src='jquery.min.js'></script>-->
<!--DOWNLOAD JQUERY HERE https://jquery.com/-->
<script>
LoadCategory();
function LoadCategory(){
$.ajax({
url:"read.php?type=GetCategory",
type:"GET",
success:function(data){
var options = "<option selected disabled value="">Select
Category</option>";
for(var i in data){
options += "<option value='"+data[i].CategoryID+"'>" + data[i].CategoryName + "</option>";
}
$("#category").html(options);
}
});
}
function LoadSubCategory(CategoryID){
$.ajax({
url:"read.php?type=GetSubCategory",
type:"POST",
data:{
CategoryID : CategoryID
},
success:function(data){
var options = "<option selected disabled value="">Select Sub-Category</option>";
for(var i in data){
options += "<option value='"+data[i].SubCategoryID+"'>" + data[i].SubCategoryName + "</option>";
}
$("#subcategory").html(options);
}
});
}
$("#category").change(function(){
LoadSubCategory(this.value);
});