问题描述
我创建了一个 sql 数据库(使用 phpmyadmin)，其中填充了测量值，我想从中调用两个日期之间的数据(用户通过在 HTML 表单中输入FROM"和TO"日期来选择日期)并显示他们在一张桌子上.
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.
此外，我在我的 html 表单下放置了一些复选框，通过选中它们，您可以限制显示的数据量.
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).
到目前为止，我设法编写了一个连接到数据库的 php 脚本，在没有选中任何复选框时显示所有内容，并设法将我的一个复选框排序.
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.
我是否需要为每种可能的组合编写一个 sql 查询，或者有更简单的方法吗?
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();
?>
推荐答案
尝试创建您的复选框，如下所示:
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'>
并尝试将您的 PHP 代码挂起:
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();?>
此解决方案考虑您的特定表列，但如果您希望通用解决方案，您也可以尝试使用此 SQL:
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'";
并使用结果构造$column_names数组.
这篇关于从 sql 数据库中检索数据并显示在表中 - 根据选中的复选框显示某些数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！