Display mySQL records as HTML table columns

Question

I want to generate an HTML table and display records from MySQL tables as columns, not rows, like:

enter image description here

The week names are in table 'week' and each week record also contains the number of sessions for that week:

+---------+-----------+----------+-----------+
| week_pk | week_name | sessions | cohort_fk |
+---------+-----------+----------+-----------+
|       1 | Week 1    |        3 |         1 |
|       2 | Week 2    |        2 |         1 |
|       3 | Week 3    |        1 |         1 |
+---------+-----------+----------+-----------+

The cohort table is:

+-----------+-------------+-------------+-------------+
| cohort_pk | cohort_name | cohort_code | cohort_year |
+-----------+-------------+-------------+-------------+
|         1 | Some name   | MICR8976    |        2014 |
+-----------+-------------+-------------+-------------+

I found some code which generates records in a table as HTML table columns, OK (I'm sure this code could be improved...).

So, the question is how can I modify this code to generate the session columns in the HTML table for each week coloumn? For example, for the Week 1 column in the HTML table, the 3 session columns, and so on for the other week columns?

Any help towards a solution appreciated.

   $query = "SELECT * FROM cohort, week 
            WHERE week.cohort_fk = cohort.cohort_pk 
            AND cohort.cohort_year = '$year' 
            AND cohort.cohort_pk = '$cohort'";
   $result = mysql_query($query, $connection) or die(mysql_error());

    echo "<table width = 50% border = '1' cellspacing = '2' cellpadding = '0'>";

    $position = 1;
    while ($row = mysql_fetch_array($result)){

    if($position == 1){
        echo "<tr>";
        }

    echo " <td width='50px'>" . $row['week_name'] . "</td> ";

    if($position == 3){
        echo "</tr> "; 
        $position = 1;
        }else{ 
        $position++;
        }
    }

    $end = "";
    if($position != 1){
    for($z=(3-$position); $z>0; $z--){
    $end .= "<td></td>";
    }
    $end .= "</tr>";
    }

    echo $end."</table> ";

Please [don't use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1); the `mysql_*` functions are outdated, [deprecated](http://us3.php.net/manual/en/intro.mysql.php), and insecure. Use [`MySQLi`](http://us3.php.net/manual/en/book.mysqli.php) or [`PDO`](http://us3.php.net/manual/en/intro.pdo.php) instead. Also, you are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). As for how to solve your problem, this is really basic: just use `$row['whatever_column_name']` where you want that data. — elixenide, Feb 19 '14 at 03:28

Tun Zarni Kyaw · Accepted Answer · 2014-02-19T04:26:54.867

mysqli_* functions are used in the following example.

$year = 2014; //for the testing purpose
$cohort = 1;  //for the testing purpose
$query = "SELECT * FROM cohort, week 
WHERE week.cohort_fk = cohort.cohort_pk 
AND cohort.cohort_year = '$year' 
AND cohort.cohort_pk = '$cohort'";

$dblink = mysqli_connect("localhost", "root", "", "test");
$result = mysqli_query($dblink, $query);

echo "<table border='1'>";
echo "<tr><td>Name</td>";
$second_row = "<tr><td>Session</td>";
while( $row = mysqli_fetch_assoc($result) ){
    $weekname   = $row["week_name"];
    $n_session  = $row["sessions"];
    echo "<td colspan='$n_session'>$weekname</td>";
    for($i=1; $i<=$n_session; $i++){
        $second_row .= "<td>S$i</td>";
    }
}//end while
echo "</tr>";
echo "$second_row</tr>";
echo "</table>";

ADDED: to generate third row -

$year = 2014;
$cohort = 1;
$query = "SELECT * FROM cohort, week 
WHERE week.cohort_fk = cohort.cohort_pk 
AND cohort.cohort_year = '$year' 
AND cohort.cohort_pk = '$cohort'";

$dblink = mysqli_connect("localhost", "root", "", "test");
$result = mysqli_query($dblink, $query);

echo "<table border='1'>";
echo "<tr><td>Name</td>";
$second_row = "<tr><td>Session</td>";
$totalcolumn = 1;                               //for the third row
while( $row = mysqli_fetch_assoc($result) ){
    $weekname   = $row["week_name"];
    $n_session  = $row["sessions"];
    $totalcolumn += $n_session;                 //for the third row
    echo "<td colspan='$n_session'>$weekname</td>";
    for($i=1; $i<=$n_session; $i++){
        $second_row .= "<td>S$i</td>";
    }
}//end while
echo "</tr>";
echo $second_row . "</tr>";

echo "<tr>";                            //for the third row
for($i=1; $i<=$totalcolumn; $i++){      //for the third row
    echo "<td>data-set</td>";           //for the third row
}                                       //for the third row
echo "</tr>";                           //for the third row

echo "</table>";

you can add another HTML <table> in the place of data-set

I'm going to ask another question where I expand this question to show attendees names as additional rows. — IlludiumPu36, Feb 19 '14 at 04:17
See http://stackoverflow.com/questions/21871614/adding-rows-to-a-html-table-with-dynamic-columns for extension to this question — IlludiumPu36, Feb 19 '14 at 05:12

score 0 · Answer 2 · answered Feb 19 '14 at 03:57

$query = "SELECT * FROM cohort, week WHERE week.cohort_fk = cohort.cohort_pk AND cohort.cohort_year = '$year' AND cohort.cohort_pk = '$cohort'";
$result = mysql_query($query, $connection) or die(mysql_error());

$table = array("<table width = 50% border = '1' cellspacing = '2' cellpadding = '0'>");
$table[] = "</tr>";
$table[] = "<th>Name</th>";
$rows = array();
while ($rows[] = $row = mysql_fetch_array($result, MYSQL_ASSOC))
    $table[] = "<td colspan='" . (int) $row['sessions'] . "'>" . $row['week_name'] . "</td>";

$table[] = "</tr>";
$table[] = "<tr>";
$table[] = "<th>Sessions</th>";
foreach ($rows as $row) {
    for ($i = 1; $i <= (int) $row['sessions']; $i++)
        $table[] = "<td>S" . $i . "</td>";
}
$table[] = "</tr>";
$table[] = "</table>";

echo join("", $table);

Display mySQL records as HTML table columns

2 Answers2

Linked