Generate HTML table from SQL query

Question

Results

From the image above, I am trying to generate this table dynamically with PHP. Below is my tables in the mysql DB

MySQL Table MySQL Table 2

Here is the SQL that I am using to pull the data from the database

SELECT DISTINCT execution_date,class_name,method_name,status FROM test_cases INNER JOIN test_case_executions ON test_cases.id=test_case_executions.test_case_id WHERE version='$platform' AND execution_date >= ( CURDATE() - INTERVAL 2 DAY ) ORDER BY execution_date DESC;

This is returning the data that need but I am struggling to figure out how to build the table. I was thinking of using arrays and when I have all the data I need, then echo out the table code. The thing I need to guard is that a test is not always guaranteed that there will be three dates for a test. Thanks for the help in advance.

You sound on the right path, you should either store the data into an array/object and then echo out the HTML code around the data. you could also look at doing it in the actual fetch loop. Now, go, have a try :) — Fluffeh
– Fluffeh, Commented Sep 30, 2013 at 21:59
Yeah, give it a shot first and then if it fails we can help you further. SO is not a place for people to do it for you. You got this :) — CP510
– CP510, Commented Sep 30, 2013 at 22:01

Cody Caughlan · Accepted Answer · 2013-09-30 22:07:04Z

You will have to do a couple passes on your data set to generate that output. That is, you'll have lets say 4 rows representing all of the status values and you will have to iterate over it a couple of times to extract out the date column headers and the "Class" row identifiers.

You can perform this in PHP. So on the 1st pass you grab the dates for the header. And also store the "Class" for the first column.

On the 2nd pass you then iterate over the data again but this time its wrapped in a loop so you can pull out the records for that cell.

Here is some psuedo-code:

$records = $db->query("select * from your_query here...");

$dates = [];
$classes = [];

// first pass is to pull out the distinct dates & classes which represent our bounds
foreach($records AS $record) {
   $dates[] = $record['execution_date'];
   $classes[] = $record['class_name'];
}

// distinct the date set and sort them from lowest to highest
$dates = array_unique($dates);
$dates = sort($dates);
$classes = array_unique($classes);

// display the date row
echo "<tr><td>&nbsp;</td>"
foreach($dates AS $date) {
  echo $date;
}
echo "</tr>";

// start displaying each class+date pair

foreach($classes AS $klass) {
  echo "<tr>";
  echo "<td>" . $klass . "</td>";
  // display each date record for this class
  foreach($dates AS $date) {
    $class_and_date_record = filter($records, $klass, $date);
    if($class_and_date_record) {
      echo "<td>" . $class_and_date_record['status'] . "</td>";
    }
  }
  echo "</tr>";
}


function filter($records, $klass, $date) {
  foreach($records AS $row) {
    if($row['class_name'] == $klass && $row['execution_date'] == $date) {
      return $row;
    }
  }
  return NULL;
}

Ok, I am having an issue in that filter function. The records variable appears to be an array of select statements. Is that what it is suppose to be?
In the filter function its not doing anything in that foreach statement so it appears that its not doing the query of the PDO statement that is in the records variable. I checked it is a single select statement. Hmmm
@jrock2004 what I provided was 90% PHP and 10% pseudo-code, there are syntax issues and what-not. Also I have no idea what your SQL layer is, so my db->query is just a general idea of what you would need to do. Consider the whole thing pseudo-code and just take forth the ideas it presents.
Understood, I kinda figured that while I was implementing. Thanks again
Ok, I figured it out. I needed to re-execute that statement in the sql variable that did the trick. Thanks

lmno · Accepted Answer · 2013-09-30 22:07:12Z

If I understand your question correctly, you only want to output data to the table when there is a value in "execution_date"

$query = "SELECT DISTINCT execution_date,class_name,method_name,status FROM test_cases INNER JOIN test_case_executions ON test_cases.id=test_case_executions.test_case_id WHERE version='$platform' AND execution_date >= ( CURDATE() - INTERVAL 2 DAY ) ORDER BY execution_date DESC;";

    if ($result = $mysqli->query($query)) {

        /* fetch associative array */
        while ($row = $result->fetch_assoc()) {
            echo '<table>';
            if(isset($result["execution_date") && !empty($result["execution_date"])){
                 echo '<tr><td>' . $result["execution_date"] . '</td></tr>';
                 ...
            }
            echo '</table>';
        }

        /* free result set */
        $result->free();
    }

The line to note is :

 if(isset($result["execution_date") && !empty($result["execution_date"])){

will check your returned row for execution_date having a value.
You can then print the rest of the items using the same $result["<column name>"] formatting.

Collectives™ on Stack Overflow

Generate HTML table from SQL query

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related