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();
?>

I think you'd have to use javascript to return the checked chkboxes - I'd pursue jQuery's .is() method - api.jquery.com/is. You could do something like $("#checkbox").is( ":selected" ) — Jack
– Jack, Commented Apr 7, 2014 at 20:52

Zini · Accepted Answer · 2014-04-11 13:07:00Z

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.

edited Apr 11, 2014 at 13:07

answered Apr 7, 2014 at 21:00

Zini

9147 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Zini Over a year ago

Sorry about the syntax errors! The syntax is fixed now.

Lef_Chef Over a year ago

I fixed Warning:Invalid argument supplied for foreach() on line 19. You have to add the if (is_array(... line there. The right code: if (is_array($columns_entries)) { foreach ($columns_entries as $i) { if(array_key_exists($i, $column_names)) { $sql_columns[] = $column_names[$i]; }

Zini Over a year ago

Sorry @Lef_Chef. Now I left the condition in a not good order of verification. I changed now and add is_array verification like you observed. Now for case where there is no entry arrays at all and you have $all = true the in_array condition will not be verified any more.

Lef_Chef Over a year ago

Thanks again for your direct respond. But checkboxes still don't work even though things got better. I get these notices: A)Notice: Undefined variable: all on line 41 B)Notice: Undefined variable: column_entries on line 41, C)Notice: Undefined variable: all on line 52 D)Notice: Undefined variable: column_entries on line 52 I get 4 times A and B. And I get countless times (=with the values displayed propably) the C and D notices.

Lef_Chef Over a year ago

If I don't check any checkbox, then all is displayed(as I wished) but there are a notice and a warning too: Notice: Undefined index: columns on line 17 Warning: Invalid argument supplied for foreach() on line 19

|

user2585076 · Accepted Answer · 2014-04-07 20:59:26Z

0

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'.'').'/>';

answered Apr 7, 2014 at 20:59

user2585076

Collectives™ on Stack Overflow

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

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related