Retrieve data from sql database and display in tables - Display certain data according to checkboxes checked

Question

I have created an sql database(with phpmyadmin) filled with measurements from which I want to call data between two dates( the user selects the DATE by entering in the HTML forms the "FROM" and "TO" date) and display them in a table.

Additionally I have put, under my html forms, some checkboxes and by checking them you can restrict the amount of data displayed.

Each checkbox represent a column of my database; so along with the date and hour column, anything that is checked is displayed(if none is checked then everything is displayed).

So far I managed to write a php script that connects to the database, display everything when none of my checkboxes is checked and also managed to put in order one of my checkboxes.

Problem: The data that I call for are been displayed twice.

Question: I want to have four checkboxes.

Do I need to write an sql query for every possible combination or there is an easier way?

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_Database_Test = "localhost";
$database_Database_Test = "database_test";
$table_name = "solar_irradiance";
$username_Database_Test = "root";
$password_Database_Test = "";
$Database_Test = mysql_pconnect($hostname_Database_Test, $username_Database_Test,  $password_Database_Test) or trigger_error(mysql_error(),E_USER_ERROR); 


//HTML forms -> variables
$fromdate = $_POST['fyear'];
$todate = $_POST['toyear'];

//DNI CHECKBOX + ALL
$dna="SELECT DATE, Local_Time_Decimal, DNI FROM $database_Database_Test.$table_name   where DATE>=\"$fromdate\" AND DATE<=\"$todate\"";
$tmp ="SELECT * FROM $database_Database_Test.$table_name where DATE>=\"$fromdate\" AND DATE<=\"$todate\""; 

$entry=$_POST['dni'];
if (empty($entry))
{
$result = mysql_query($tmp);
echo 
"<table border='1' style='width:300px'>
<tr>
<th>DATE</th>
<th>Local_Time_Decimal</th>
<th>Solar_time_decimal</th>
<th>GHI</th>
<th>DiffuseHI</th>
<th>zenith_angle</th>
<th>DNI</th>
";

while( $row = mysql_fetch_assoc($result))
{
echo "<tr>";  
echo "<td>" . $row['DATE'] . "</td>";   
echo "<td>" . $row['Local_Time_Decimal'] . "</td>";  
echo "<td>" . $row['Solar_Time_Decimal'] . "</td>";  
echo "<td>" . $row['GHI'] . "</td>";  
echo "<td>" . $row['DiffuseHI'] . "</td>";  
echo "<td>" . $row['Zenith_Angle'] . "</td>";  
echo "<td>" . $row['DNI'] . "</td>";  
echo "</tr>";
}

echo '</table>';}

else
{
$result= mysql_query($dna);
echo
"<table border='1' style='width:300px'>
<tr>
<th>DATE</th>
<th>Local_Time_Decimal</th>
<th>DNI</th>
";

while($row = mysql_fetch_assoc($result))
{
echo "<tr>";  
echo "<td>" . $row['DATE'] . "</td>";  
echo "<td>" . $row['Local_Time_Decimal']."</td>";
echo "<td>" . $row['DNI'] . "</td>";  
echo "</tr>";
}
echo '</table>';
}
if($result){
        echo "Successful";
    }
    else{
    echo "Enter correct dates";
    }
?>
<?php
mysql_close();
?>

Answer 1

Try to create your checkbox like below:

Solar_Time_Decimal<checkbox name='columns[]' value='1'>
GHI<checkbox name='columns[]' value='2'>
DiffuseHI<checkbox name='columns[]' value='3'>
Zenith_Angle<checkbox name='columns[]' value='4'>
DNI<checkbox name='columns[]' value='5'> 

And try to hange your PHP code to this:

<?php
//HTML forms -> variables
$fromdate = isset($_POST['fyear']) ? $_POST['fyear'] : data("d/m/Y");
$todate = isset($_POST['toyear']) ? $_POST['toyear'] : data("d/m/Y");
$all = false;
$column_names = array('1' => 'Solar_Time_Decimal', '2'=>'GHI', '3'=>'DiffuseHI', '4'=>'Zenith_Angle','5'=>'DNI');
$column_entries = isset($_POST['columns']) ? $_POST['columns'] : array();
$sql_columns = array();
foreach($column_entries as $i) {
   if(array_key_exists($i, $column_names)) {
    $sql_columns[] = $column_names[$i];
   }
}
if (empty($sql_columns)) {
 $all = true;
 $sql_columns[] = "*";
} else {
 $sql_columns[] = "DATE,Local_Time_Decimal";
}

//DNI CHECKBOX + ALL
$tmp ="SELECT ".implode(",", $sql_columns)." FROM $database_Database_Test.$table_name where DATE>=\"$fromdate\" AND DATE<=\"$todate\""; 

$result = mysql_query($tmp);
echo "<table border='1' style='width:300px'>
<tr>
<th>DATE</th>
<th>Local_Time_Decimal</th>";
foreach($column_names as $k => $v) { 
  if($all || (is_array($column_entries) && in_array($k, $column_entries)))
     echo "<th>$v</th>";
}
echo "</tr>";
while( $row = mysql_fetch_assoc($result))
{
    echo "<tr>";  
    echo "<td>" . $row['DATE'] . "</td>";   
    echo "<td>" . $row['Local_Time_Decimal'] . "</td>";  
    foreach($column_names as $k => $v) { 
      if($all || (is_array($column_entries) && in_array($k, $column_entries))) {
         echo "<th>".$row[$v]."</th>";
       }
    }
    echo "</tr>";
}
echo '</table>';

if($result){
        echo "Successful";
    }
    else{
    echo "Enter correct dates";
    }
?>
<?php
mysql_close();?>

This solution consider your particular table columns but if your wish a generic solution you can try to use this SQL too:

$sql_names = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$database_Database_Test' AND TABLE_NAME = '$table_name'";

and use the result to construct the $column_names array.

Answer 2

Solution for your problem : Change the mysql_fetch_assoc with mysql_fetch_array If you have the same problem try to print your result with print_r

Answer : Use the bit datatype in mysql for store and read your checkboxes. When you're receiving thr value from the database then you can use

in the parameter checked you can use the php code as exist :

   $value = ...get the value from db ( 1 or 0 )
   echo '<input type="checkbox" name="thename" value="thevalue" '.($value==1?'checked=checked'.'').'/>';