Print data from database to html table

Question

I am trying to print data from database to html table. I want my table to be like this:

| username | studio APEX | studio CANVAS | studio ORBIT |
+----------+-------------+---------------+--------------+
| Aaron    |       x     |       x       |              |
| Adel     |             |               |       x      |
| John     |       x     |       x       |              |
| James    |       x     |       x       |              |
| Kate     |             |               |       x      |
| Peter    |       x     |               |              |

Where studio name is taken from databse. What I got is query:

$sql2 = '
            select
              p2.proc_leader as username,
                   max(case when studio = "APEX" then "x" else "" end) as APEX,
                   max(case when studio = "BASECAMP" then "x" else "" end) as BASECAMP,
                   max(case when studio = "CANVAS" then "x" else "" end) as CANVAS,
                   max(case when studio = "HORIZON" then "x" else "" end) as HORIZON,
                   max(case when studio = "LAUNCHPAD" then "x" else "" end) as LAUNCHPAD,
                   max(case when studio = "NEBULA" then "x" else "" end) as NEBULA,
                   max(case when studio = "ORBIT" then "x" else "" end) as ORBIT,
                   max(case when studio = "PALETTE" then "x" else "" end) as PALETTE,
                   max(case when studio = "SANDBOX" then "x" else "" end) as SANDBOX,
                   max(case when studio = "STELLAR" then "x" else "" end) as STELLAR,
                   max(case when studio = "THE CLIMB" then "x" else "" end) as THECLIMB,
                   max(case when studio = "TOONIGAMI" then "x" else "" end) as TOONIGAMI,
                   max(case when studio = "TREEHOUSE" then "x" else "" end) as TREEHOUSE
            from process p1
            left join (
              select *
              from proc_leader
              union all
              select *
              from proc_checker
              union all
              select *
              from proc_staff    
            ) p2 on p1.projectNo = p2.projectNo
            and p1.process = p2.process
            group by p2.proc_leader
';

Then I am trying to print to html table:

 $query2 = mysqli_query($conn, $sql2);

  while ($data2 = mysqli_fetch_assoc($query2)) {
    $username = $data2['username'];
    $apex = $data2['APEX'];
    $basecamp = $data2['BASECAMP'];
    $canvas = $data2['CANVAS'];
    $horizon = $data2['HORIZON'];
    $launchpad = $data2['LAUNCHPAD'];
    $nebula = $data2['NEBULA'];
    $orbit = $data2['ORBIT'];
    $palette = $data2['PALETTE'];
    $sandbox = $data2['SANDBOX'];
    $stellar = $data2['STELLAR'];
    $theclimb = $data2['THECLIMB'];
    $toonigame = $data2['TOONIGAMI'];
    $treehouse = $data2['TREEHOUSE'];   

            $header =
                '<th>Username</th>
                <th>Studio'.$apex.'</th>
                <th>Studio'.$basecamp.'</th>
                <th>Studio'.$canvas.'</th>
                <th>Studio'.$horizon.'</th>
                <th>Studio'.$launchpad.'</th>
                <th>Studio'.$nebula.'</th>
                <th>Studio'.$orbit.'</th>
                <th>Studio'.$palette.'</th>
                <th>Studio'.$sandbox.'</th>
                <th>Studio'.$stellar.'</th>
                <th>Studio'.$theclimb.'</th>
                <th>Studio'.$toonigame.'</th>
                <th>Studio'.$treehouse.'</th>';

            //body
            $body = '';
                $row = '<td>' . htmlspecialchars($username) . '</td>';
                $body .= "<tr>$row</tr>";
            echo "<thead>$header</thead><tbody>$body</tbody>";      
}

But it seems not working as I want it to. Here is how my table look like now:

| username | studiox     | studiox       | studio       |
+----------+-------------+---------------+--------------+
| Aaron    |             |               |              |
| username | studio      | studio        | studiox      |
| Adel     |             |               |              |
| username | studiox     | studiox       | studio       |
| John     |             |               |              |
| username | studiox     | studiox       | studio       |
| James    |             |               |              |
| username | studio      | studio        | studiox      |
| Kate     |             |               |              |
| username | studiox     | studio        | studio       |
| Peter    |             |               |              |

What is the problem and can I print table correctly? Thank you

How about updating the question with rendered HTML? And why echoing the header each time? — mplungjan
– mplungjan, Commented Jan 19, 2017 at 7:21

Philipp · Accepted Answer · 2017-01-19 07:52:13Z

If you want to keep the while loop:

$query2 = mysqli_query($conn, $sql2);

$header = '<tr><th>Username</th>
            <th>Studio APEX</th>
            <th>Studio BASECAMP</th>
            <th>Studio CANVAS</th>
            <th>Studio HORIZON</th>
            <th>Studio LAUNCHPAD</th>
            <th>Studio NEBULA</th>
            <th>Studio ORBIT</th>
            <th>Studio PALETTE</th>
            <th>Studio SANDBOX</th>
            <th>Studio STELLAR</th>
            <th>Studio THECLIMB</th>
            <th>Studio TOONIGAMI</th>
            <th>Studio TREEHOUSE</th></tr>';
$body = '';
while ($data2 = mysqli_fetch_assoc($query2)) {
    $username = $data2['username'];
    $apex = $data2['APEX'];
    $basecamp = $data2['BASECAMP'];
    $canvas = $data2['CANVAS'];
    $horizon = $data2['HORIZON'];
    $launchpad = $data2['LAUNCHPAD'];
    $nebula = $data2['NEBULA'];
    $orbit = $data2['ORBIT'];
    $palette = $data2['PALETTE'];
    $sandbox = $data2['SANDBOX'];
    $stellar = $data2['STELLAR'];
    $theclimb = $data2['THECLIMB'];
    $toonigame = $data2['TOONIGAMI'];
    $treehouse = $data2['TREEHOUSE'];

    $row = '<td>' . htmlspecialchars($username) . '</td>
                <td>' . $apex . '</td>
                <td>' . $basecamp . '</td>
                <td>' . $canvas . '</td>
                <td>' . $horizon . '</td>
                <td>' . $launchpad . '</td>
                <td>' . $nebula . '</td>
                <td>' . $orbit . '</td>
                <td>' . $palette . '</td>
                <td>' . $sandbox . '</td>
                <td>' . $stellar . '</td>
                <td>' . $tdeclimb . '</td>
                <td>' . $toonigame . '</td>
                <td>' . $treehouse . '</td>';
    $body .= '<tr>' . $row . '</tr>';
}
echo '<table><thead>' . $header . '</thead><tbody>' . $body . '</tbody></table>';

Just make sure you don't loop the wrong parts. In you case you only want to build the body of the table in your loop. So build the head before or after you execute the loop, so you only do it once and not in every iteration.

mplungjan · Accepted Answer · 2017-01-19 07:26:11Z

0

The issue is you are making the table header in loop, so try this:

echo '<table>';

// This is table header    
echo '<tr> <th>Column Name 1</th> <th>Column Name 2</th> <th>Column Name 3</th></tr><tbody>';

// This is the table body
foreach( .. )
{
    echo '<tr>';
    echo '<td>'.$var.'<td>';
    echo '<td>'.$var.'<td>';
    ...
    echo '</tr>';
}

echo '</tbody></table>';

edited Jan 19, 2017 at 7:26

mplungjan

180k29 gold badges183 silver badges246 bronze badges

answered Jan 19, 2017 at 7:22

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

4 Comments

Kirill Nagiev Over a year ago

what should i put inside foreach?

mplungjan Over a year ago

And your header:

echo '<tr><th>Username</th>                 <th>Studio apex</th>                 <th>Studio basecamp</th>                 <th>Studio canvas</th>                 <th>Studio horizon</th>                 <th>Studio launchpad</th>                 <th>Studio nebula</th>                 <th>Studio orbit</th>                 <th>Studio palette</th>                 <th>Studio sandbox</th>                 <th>Studio stellar</th>                 <th>Studio theclimb</th>                 <th>Studio toonigame</th>                 <th>Studio treehouse</th></tr><tbody>';

Kirill Nagiev Over a year ago

I meant inside brackets foreach (?) Should it be username?

Mayank Pandeyz Over a year ago

foreach($data as $variable){ } some thing like this

Strawberry · Accepted Answer · 2017-01-19 12:32:17Z

0

Here's something to think about - although I should stress that I'm no PHP coder...

<?php

$my_array = array(
    0 => 1,
    1 => 2,
    2 => 3,
    3 => 5,
    4 => 7
);

for($i=0;$i<10;$i++){
echo $i;
if(in_array($i,$my_array)){echo " yes <br>\n";} else {echo " no <br>\n";}
}

?>

Outputs...

0 no
1 yes
2 yes
3 yes
4 no
5 yes
6 no
7 yes
8 no
9 no

answered Jan 19, 2017 at 12:32

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

Print data from database to html table

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related