Display MySQL query in a HTML table

Question

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.
enter image description here

<?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>"; 

?>

current results

I'm not sure I understood that well, what are the existing values for Sprinkler_ID? — AyB
– AyB, Commented May 7, 2014 at 6:48
so the values stored in the database are 1...8. but the query will not necessary return all the values. so when a SPKr_ID is not fetched by the query it means that sprinkler is not yet assigned and so its value for that Schedule (id,start,stop,days of week) will be no otherwise yes — Prerak Diwan
– Prerak Diwan, Commented May 7, 2014 at 6:50
So in cases where the value doesn't exist, does it show null or empty? — AyB
– AyB, Commented May 7, 2014 at 6:51

AyB · Accepted Answer · 2014-05-07 08:09:40Z

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

//...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:

    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'.

so I am not sure everybody is getting this or not? so if you see the query results above there is no spr7 or 8 . But there has to be a column created for them but their values will be no for Schedule_ID records? Am I making sense??
@PrerakDiwan Ah, missed that, ok another thing is, the Sprinkler_ID 5 has both id 19(repeating) and 20(not repeating). So is it still a yes?
so think it like our main ID for the table is id or Schedule_ID. this specific thing is required so the table size does not increase if I have all the Schedule ID assigned to say Spkr 1,2,3 so 24 records which is not feasible to display on the table.
@PrerakDiwan This is getting more complicated, I presume the first image is not the final output, could you please make an image of the final output table to make things clear?
updated so now compare the two tables the top one is desired and the bottom one is I have

sshet · Accepted Answer · 2014-05-07 07:00:15Z

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
    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>"; 

?>

can you please add this code to my code so I understand well. thanks
do i need to repeat this code 8 times for each Sprkr number??
yes but u did not create 8 columns for each sprinkler so how is this supposed to work as if I understand the code it will still print in one column. right??
@PrerakDiwan "while($row=mysql_fetch_array($results))" loops through all the rows retrieved from the database, which is stored in the "$results" variable

Collectives™ on Stack Overflow

Display MySQL query in a HTML table

2 Answers 2

13 Comments

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

6 Comments

Related