PHP/MySql HTML Table Grouping

Question

I have a problem I can't seem to to wrap my head around.

I have a mySQL table setup similar to this:

userid          date          rating1        rating2

1               5/1/2013      5              5
2               5/1/2013      4              4
3               5/1/2013      3              3
2               5/7/2013      5              5
1               5/7/2013      5              5
3               5/7/2013      2              2

I have my php code to query the data:

$con=mysqli_connect("localhost","root","password","db");
$result = mysqli_query($con,"SELECT userid,date,rating1,rating2 FROM db");

But when I try and output to a table using:

while($row = mysqli_fetch_array($result))
  {
echo '<table>';
    echo '<thead>';
    echo '<tr>';
        echo '<th>UserID</th>';
        echo '<th>Date</th>';
        echo '<th>First Rating</th>';
        echo '<th>Second Rating</th>';
            echo '</tr>';
    echo '</thead>';
    echo '<tbody>';
    echo '<tr>';
        echo '<td>'.$row['userid'].'</td>';
        echo '<td>'.$row['date'].'</td>';
        echo '<td>'.$row['rating1'].'</td>';
        echo '<td>'.$row['rating2'].'</td>';
            echo '</tr>';
    echo '</tbody>';
 echo '</table>';
  }

It outputs a unique table for each entry. What I need is 1 HTML table per UserID Ex:

UserID 1

Date          First Rating        Second Rating

5/1/2013      5                   5
5/7/2013      5                   5



UserID 2

Date          First Rating        Second Rating

5/1/2013      4                   4
5/7/2013      5                   5



UserID 3

Date          First Rating        Second Rating

5/1/2013      3                   3
5/7/2013      2                   2

I am honestly stuck... Thanks in advance for your help!

weenoid · Accepted Answer · 2013-08-16 20:35:28Z

1

$con=mysqli_connect("localhost","root","password","db");
$result = mysqli_query($con,"SELECT userid,date,rating1,rating2 FROM db ORDER BY userid");
$id = -1;

while($row = mysqli_fetch_array($result)) {
    if ($id != -1 && $id != $row['userid']) {
        echo '</tbody>';
        echo '</table>';
    }

    if ($id != $row['userid']) {
        echo '<table>';
        echo '<thead>';
        echo '<tr>';
        echo '<th>UserID</th>';
        echo '<th>Date</th>';
        echo '<th>First Rating</th>';
        echo '<th>Second Rating</th>';
        echo '</tr>';
        echo '</thead>';
        echo '<tbody>';

        $id = $row['userid'];
    }

    echo '<tr>';
    echo '<td>'.$row['userid'].'</td>';
    echo '<td>'.$row['date'].'</td>';
    echo '<td>'.$row['rating1'].'</td>';
    echo '<td>'.$row['rating2'].'</td>';
    echo '</tr>';
 }

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

edited Aug 16, 2013 at 20:35

answered Aug 16, 2013 at 19:47

weenoid

1,1962 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

stephen Over a year ago

Wouldn't this loop, at its very first iteration, start and end the table regardless of whether or not the first ID had more entries?

Kevin Johnson Over a year ago

When I use your code, I still get a unique table for each (I did have to make 1 change though, added a ; to the line $id != $row['userid']; Is there a way to combine into one table per unique user id? thanks for your time/help!

stephen Over a year ago

@kevin I think he typo'd at that line. Change != to = and it should be working properly. $id != $row['userid'] should be $id = $row['userid'];

user2417483 · Accepted Answer · 2013-08-16 19:42:34Z

You need something like:

echo '<table>';
echo '<thead>';
echo '<tr>';
    echo '<th>UserID</th>';
    echo '<th>Date</th>';
    echo '<th>First Rating</th>';
    echo '<th>Second Rating</th>';
echo '</tr>';
echo '</thead>';
echo '<tbody>';
$old_id = 0;
while($row = mysqli_fetch_array($result))
{
    if( $old_id != $row['userid'] ) {
          echo "<tr><td colspan=3>".$row['userid']."</td>";
          $old_id = $row['userid'];
    } else {
        echo "<tr>";
        echo '<td>'.$row['userid'].'</td>';
        echo '<td>'.$row['date'].'</td>';
        echo '<td>'.$row['rating1'].'</td>';
        echo '<td>'.$row['rating2'].'</td>';
    }
    echo '</tr>';
}

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

BWS · Accepted Answer · 2013-08-16 19:36:36Z

0

something like this?

echo '<table>';
echo '<thead>';
echo '<tr>';
    echo '<th>UserID</th>';
    echo '<th>Date</th>';
    echo '<th>First Rating</th>';
    echo '<th>Second Rating</th>';
echo '</tr>';
echo '</thead>';
echo '<tbody>';

while($row = mysqli_fetch_array($result))
{
    echo '<tr>';
        echo '<td>'.$row['userid'].'</td>';
        echo '<td>'.$row['date'].'</td>';
        echo '<td>'.$row['rating1'].'</td>';
        echo '<td>'.$row['rating2'].'</td>';
    echo '</tr>';
}

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

answered Aug 16, 2013 at 19:36

BWS

3,85620 silver badges26 bronze badges

Collectives™ on Stack Overflow

PHP/MySql HTML Table Grouping

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related