[SOLVED] Grouping the columns

[immanuelx2]

Let's say I have a very simple Table:

 

+---------------------+
| id |  name  | level |
+---------------------+
|  1 |  Adam  |   1   |
|  2 |  Jack  |   7   |
|  3 |  Bill  |   1   |
|  4 |  Pete  |   3   |
|  5 |  Ross  |   7   |
+---------------------+

 

but I want the PHP to output their id's and names, but group their Levels together like so:

 

Level 1:
Adam (id: 1)
Bill (id: 3)

Level 3:
Pete (id: 4)

Level 7:
Jack (id: 2)
Ross (id: 5)

 

And then afterwards, If I create someone with a new level, PHP will automatically create a new heading (e.g. Level 10) and put the corresponding names under it.

 

How should I approach this? Should my PHP contain two "while"s? Or do I use the SQL <group by> function? Any help is appreciated!

[trq]

Not tested.

 

<?php

  $sql = "SELECT id,name,level FROM tbl GROUP BY level ORDER by level";
  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($result)) {
      $level = '';
      while ($row = mysql_fetch_assoc($result)) {
         if (empty($level) {
           $level = $row['level'];
         }
         if ($level != $row['level']) {
           echo  "Level $level<br />"
           $level = $row['level'];
         } else {
           $level = $row['level'];
         }
         echo "{$row['name']} (id: {$row['id']})<br />";
      }
    }
  }

?>

[Barand]

I think you should remove

 

         if (empty($level) {
           $level = $row['level'];
         }

 

and this is superfluous

         else {
           $level = $row['level'];
         }

[Wildbug]

You don't want to use "GROUP BY level" -- you won't get all the names.  Just use ORDER BY.

 

With GROUP BY you're going to get something like this:

+---------------------+
| id |  name  | level |
+---------------------+
|  1 |  Adam  |   1   |
|  4 |  Pete  |   3   |
|  2 |  Jack  |   7   |
+---------------------+

 

<?php

$result = mysql_query('SELECT * FROM table ORDER BY level,name,id');
if ($result && mysql_num_rows($result)) {
$current_level = -1;
while ($row = mysql_fetch_assoc($result)) {
	if ($row['level'] != $currentl_level) {
		$current_level = $row['level'];
		echo "<b>Level $row[level]</b><br>\n";
	}
	echo "$row[name]<br>\n";
}
}
?>

[Trium918]

Not tested.

 

<?php

  $sql = "SELECT id,name,level FROM tbl GROUP BY level ORDER by level";
  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($result)) {
      $level = '';
      while ($row = mysql_fetch_assoc($result)) {
         if (empty($level) {
           $level = $row['level'];
         }
         if ($level != $row['level']) {
           echo  "Level $level<br />"
           $level = $row['level'];
         } else {
           $level = $row['level'];
         }
         echo "{$row['name']} (id: {$row['id']})<br />";
      }
    }
  }

?>

 

You have a neat way of programming!

[pocobueno1388]

It's because it's the "right" way, haha. Looks nice and neat when you follow the indention method.

[Wildbug]

Oops, I made a typo -- 5th line of code, "$currentl_level" should be "$current_level".

[trq]

I think you should remove

 

         if (empty($level) {
           $level = $row['level'];
         }

 

and this is superfluous

         else {
           $level = $row['level'];
         }

 

Agreed... I was in a hurry.

[immanuelx2]

hmm...... it is not displaying the first Level... only the 2nd through the last...... any ideas?

 

$query = mysql_query("SELECT S.name, S.level, S.desc, C.name as class_name
                      FROM Skills as S join Classes as C
                      ON S.class_id = C.id
                      ORDER BY S.level ASC, C.name ASC , C.id_name ASC , S.name ASC, S.id ASC");

$current_level = -1;
$i = 0;
while ($skill = mysql_fetch_assoc($query))
{
  $i++;
  if ($skill['level'] != $current_level)
  {
    if ($i != 1) echo "</table>";
    $current_level = $skill['level'];
    echo "<h1>Level <b>$skill[level]</b></h1><table class='skills'>\n";
  }
  echo "<tr><td>$skill[name</td></tr>\n";
}

[Barand]

try something like this

<?php
$query = mysql_query("SELECT S.name, S.level, S.desc, C.name as class_name
                      FROM Skills as S join Classes as C
                      ON S.class_id = C.id
                      ORDER BY S.level , C.name , S.name ");

$current_level = -1;
while (list($name, $level, $desc, $cname) = mysql_fetch_row($query)) {
    if ($level != $current_level) {
        if ($current_level != -1) {
            echo "</table>\n";  // end previous table if there was one
        }
        echo "<h3>Level $level</h3>\n<table border='1'>\n";
        $current_level = $level;
    }
    echo "<tr>
            <td>$cname</td>
            <td>$name</td>
            <td>$desc</td>
          </tr>\n";
}
echo "</table>\n";   // end last table  
?>

[immanuelx2]

Got it!

 

I used the do {} while (); function