在HTML表中显示MySQL查询

The following code displays table on the form but I need to format the table in a specific fashion.
The desired output is displayed in image. So I need to manipulate the query someway where I can convert Sprinkler_ID columns to 1,2,3,4,5,6,7,8. So, if there is no Sprinkler_ID let say "2" in our original table then the desired table should print "no" in the "2" field otherwise YES.

以下代码显示表单上的表,但我需要以特定方式格式化表。所需的输出显示在图像中。所以我需要操纵查询,我可以将Sprinkler_ID列转换为1,2,3,4,5,6,7,8。因此,如果没有Sprinkler_ID,请在我们的原始表中说“2”,那么所需的表应在“2”字段中打印“no”,否则为YES。

<?php
include('config.php');
echo "<h4>Current Schedules</h4>";
echo "<table border=2 >"; 
echo "<tr>"; 

echo "<td><b>Sprinkler_Id</b></td>"; 
echo "<td><b>Schedule_Id</b></td>"; 
echo "<td><b>Starttime</b></td>"; 
echo "<td><b>Stoptime</b></td>"; 
echo "<td><b>Monday</b></td>"; 
echo "<td><b>Tuesday</b></td>"; 
echo "<td><b>Wednesday</b></td>"; 
echo "<td><b>Thursday</b></td>"; 
echo "<td><b>Friday</b></td>"; 
echo "<td><b>Saturday</b></td>"; 
echo "<td><b>Sunday</b></td>"; 
echo "</tr>"; 

$sql = "SELECT daystime.*, Sprinkler_ID FROM daystime, scheduler WHERE daystime.id = scheduler.DaysTime_ID ORDER BY Sprinkler_ID, daystime.id";
$results=mysql_query($sql) or die(mysql_error());
while($row=mysql_fetch_array($results))
{
$id = $row['id']; 
echo "<tr>";
echo "<td valign='top'>" . nl2br( $row['Sprinkler_ID']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['id']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['starttime']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['stoptime']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['mon']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['tue']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['wed']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['thu']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['fri']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['sat']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['sun']) . "</td>";  
echo "</tr>"; 
} 
echo "</table>"; 

?>

2 个解决方案

#1

You have to first custom create the 1-8 columns not present so for that do:

您必须首先自定义创建1-8列不存在,以便:

//...continued from code
echo "<td><b>Saturday</b></td>";
echo "<td><b>Sunday</b></td>"; 
for($i=1;$i<=8;$i++){
echo "<td><b>".$i."</b></td>"; 
}
echo "</tr>"; 

Then, we use the following:

然后,我们使用以下内容:

$ids=array();
#we'll first save all the ids into an array
while($row=mysql_fetch_array($results))
{
    $ids[$row['id']][]=$row['Sprinkler_ID'];
}
$listed=array();
while($row=mysql_fetch_array($results))
{
    $id = $row['id'];

    if(!in_array($id,$listed)){
      echo "<tr>";
      echo "<td valign='top'>" . nl2br( $row['id']) . "</td>";
      //...all your other rows


      for($i=1;$i<=8;$i++){
        if(in_array($i,$ids[$id])){
            echo "<td valign='top'>yes</td>";
        }
        else{
            echo "<td valign='top'>no</td>";
        }
      }


      echo "</tr>";
      $listed[]=$id;

    }
}

The logic is, we first group the repeating Sprinkler_id by the id as:

逻辑是,我们首先将id重复的Sprinkler_id分组为:

    19 => 
       [0] => 4,
       [1] => 5, 

    20 => 
       [0] => 5,

    21 => 
       [0] => 4,
       [1] => 6 

Then while it's looping through the id we make sure that row is printed only once using $listed[] array. As for the 'yes' and 'no', we use the previously grouped array to check if the sprinkler_id for that id exists. If it does, it's a 'yes' else 'no'.

然后,当它循环遍历id时,我们确保使用$ listed []数组只打印一行。至于'是'和'否',我们使用先前分组的数组来检查该id的sprinkler_id是否存在。如果确实如此,则为“是”,否则为“否”。

#2

In order to achieve this maintain a static array for Sprinkle Ids in your php file and then check if value coming from Db exists in this array

为了实现这一点,在php文件中为Sprinkle Ids维护一个静态数组,然后检查来自Db的值是否存在于此数组中

    <?php
    include('config.php');
     $sprinkleIds = range(1,8);
    echo "<h4>Current Schedules</h4>";
    echo "<table border=2 >"; 
    echo "<tr>"; 

    echo "<td><b>Sprinkler_Id</b></td>"; 
    echo "<td><b>Schedule_Id</b></td>"; 
    echo "<td><b>Starttime</b></td>"; 
    echo "<td><b>Stoptime</b></td>"; 
    echo "<td><b>Monday</b></td>"; 
    echo "<td><b>Tuesday</b></td>"; 
    echo "<td><b>Wednesday</b></td>"; 
    echo "<td><b>Thursday</b></td>"; 
    echo "<td><b>Friday</b></td>"; 
    echo "<td><b>Saturday</b></td>"; 
    echo "<td><b>Sunday</b></td>"; 
    echo "</tr>"; 

    $sql = "SELECT daystime.*, Sprinkler_ID FROM daystime, scheduler WHERE daystime.id = scheduler.DaysTime_ID ORDER BY Sprinkler_ID, daystime.id";
    $results=mysql_query($sql) or die(mysql_error());
    while($row=mysql_fetch_array($results))
    {
    $id = $row['id']; 
    echo "<tr>";
     if( in_array(  $row['Sprinkler_ID'], $sprinkleIds ) {
          $text = 'Yes';
        } else {
          $text = 'No';
        }
        echo "<td valign='top'>" . $text . "</td>";  
    echo "<td valign='top'>" . nl2br( $row['id']) . "</td>";  
    echo "<td valign='top'>" . nl2br( $row['starttime']) . "</td>";  
    echo "<td valign='top'>" . nl2br( $row['stoptime']) . "</td>";  
    echo "<td valign='top'>" . nl2br( $row['mon']) . "</td>";  
    echo "<td valign='top'>" . nl2br( $row['tue']) . "</td>";  
    echo "<td valign='top'>" . nl2br( $row['wed']) . "</td>";  
    echo "<td valign=

'top'>" . nl2br( $row['thu']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['fri']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['sat']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['sun']) . "</td>";  
echo "</tr>"; 
} 
echo "</table>"; 

?>