Grouping results by category

[1042]

Hello all,

 

i have this code that shows the department categories in a 4 column format, my problem is how do i insert the names of the people that belong to that category to be shown under the category name? i have 2 tables, one is the department categories and the other one is users, under users there is a field that is called staff_department..here is the code that shows the categories only;

 

 

PHP Code:

$sqlCat   = MySQL_Query("SELECT dep_name FROM staff_departments ORDER BY dep_name");
$columns  = 4;
$numRows     = MySQL_Num_Rows ($sqlCat);
$rows = ceil($numRows / $columns);

while($category = MySQL_Fetch_Array($sqlCat)) {
  $cat[] = $category['dep_name'];
}
echo '<br><br>'.
     '<table cellpadding="0" cellspacing="0" align="center" width="100%" border="1">';
  for($i = 0; $i < $rows; $i++) {
    echo '<tr>';
    for($j = 0; $j < $columns; $j++) {
      if(isset($cat[$i + ($j * $rows)])) {
        echo '<td><strong>'. $cat[$i + ($j * $rows)] . '</strong></td>';
      }
    }
    echo '</tr>';
  }
echo '</table>';

 

 

i guess im missing a query to show the names under the category example SELECT * FROM users WHERE staff_department = '$sqlCat [dep_name]' but where do i do this?

 

thanks to all in advance

[Psycho]

Well, you don't state how the tables are linked (i.e. what field in the departments table does the staff_department field in the user link to? (I assumed dept_id).

 

Anyway, I think this code is what you are looking for. Didn't test this so there might be some typos.

<?php
$sql = "SELECT users.*, sd.dep_name
          FROM users
            LEFT JOIN staff_departments sd
            ON users.staff_department = sd.dept_id
          ORDER BY sd.dep_name";

$result = mysql_query($sql);
$columns  = 4;
$deptName = "";
$currentColumn = 0;

echo "<table>\n";
while ($row = mysql_fetch_array($result)) {

  if ($currentColumn == $columns) {
    echo "<\tr>";
    $currentColumn = 0;
  }

  if ($row['dep_name'] != $deptName) {
    $deptName = $row['dep_name'];
    echo "<tr><td colspan=\"{$columns}\"><b>{$deptName}</b></td></tr>
    $currentColumn = 0;
  }

  if ($currentColumn == 0) {
    echo "<tr>";
  }

  echo "<td>{$row['name']}</td>";
  $currentColumn++;

}

echo "</tr></table>";
?>

[1042]

Hello mjdamato , thanks for the help, here is the code;

$sql = "SELECT users.*, sd.staff_office
          FROM users
            LEFT JOIN staff_departments sd
            ON users.staff_office = sd.dep_id
          ORDER BY sd.dep_name";

$result = mysql_query($sql);
$columns  = 4;
$deptName = "";
$currentColumn = 0;

echo "<table>\n";
while ($row = mysql_fetch_array($result)) {

  if ($currentColumn == $columns) {
    echo "<\tr>";
    $currentColumn = 0;
  }

  if ($row['dep_name'] != $deptName) {
    $deptName = $row['dep_name'];
    echo "<tr><td colspan=\"{$columns}\"><b>{$deptName}</b></td></tr>";
    $currentColumn = 0 ;
  }

  if ($currentColumn == 0) {
    echo "<tr>";
  }

  echo "<td>{$row['staff_name']}</td>";
  $currentColumn++;

}

echo "</tr></table>";

 

however im getting this error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in path\to\site\staffonly_new\print2.php on line 104

 

any ideas? thanks

[Psycho]

You are most likely getting a SQL error. Change this line

$result = mysql_query($sql);

 

To this

$result = mysql_query($sql) or die (mysql_error());

 

Did you change the field names in the query to what they should be? I stated that I had to guess the relationship between the two tables.

[1042]

You are most likely getting a SQL error. Change this line

$result = mysql_query($sql);

 

To this

$result = mysql_query($sql) or die (mysql_error());

 

Did you change the field names in the query to what they should be? I stated that I had to guess the relationship between the two tables.

 

this is what i got

Unknown column 'sd.staff_office' in 'field list'

 

what does sd. stand for, sorry if is a dumb question

[Psycho]

The "sd" is a pointer to the "staff_departments" table. When creating a query you can create a shorthand pointer to tables to make the code more manageable. You will notice that when I referenced the "staff_departments" table in the query I included a "sd" directly after it. That created the pointer.

 

So, the error is telling you that there is no column called "staff_office" in the "staff_departments" table. You modified the SELECT portion of the query from what I had posted - which was based upon your initial post. The select statement I wrote was grabing all the records fromt he user table in addition to the "dep_name" of the "staff_departments" table. I'm not sure where you are trying to go here.

 

Anyway, here is my original query without the shorthand pointer. maybe this will be more clear to you

 

$sql = "SELECT users.*, staff_departments.dep_name
          FROM users
            LEFT JOIN staff_departments
            ON users.staff_department = staff_departments.dept_id
          ORDER BY staff_departments.dep_name";