Question 19989 in Mysql Question posted in
The official MySQL documentation can be found here.

I want to split the data by department – Mysql

good greeting

I have a PHP query that brings data, name, and department into a table, how can I split into tables by departments, which means each department is in a table

$employee_qry=$conn->query("SELECT * FROM `employee` ") or die(mysqli_error());

while($row=$employee_qry->fetch_array()){                                       

<tr>
<td><?php echo $row['idnumber']?></td>
                                    
<td><?php echo $row['EmpleName']?></td>
<td><?php echo $row['Nationality']?></td>
Tags:

2

Answers


  1. 0

    Given a basic table schema and dummy data as follows:

    mysql> describe employee;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| idnumber    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| emplename   | varchar(64)      | NO   |     | 0       |                |
| city        | varchar(64)      | NO   |     | 0       |                |
| postcode    | varchar(64)      | NO   |     | 0       |                |
| job_title   | varchar(64)      | NO   |     | 0       |                |
| nationality | varchar(64)      | NO   |     | 0       |                |
| department  | varchar(64)      | NO   |     | 0       |                |
+-------------+------------------+------+-----+---------+----------------+


mysql> select * from employee order by department;
+----------+-------------+-----------+----------+---------------+-------------+------------------+
| idnumber | emplename   | city      | postcode | job_title     | nationality | department       |
+----------+-------------+-----------+----------+---------------+-------------+------------------+
|        3 | Rusty Nail  | Glasgow   | G12 9TY  | Visual Artist | French      | Baguette Bashing |
|        4 | Dusty Bin   | Glasgow   | G12 8LL  | Student       | Spanish     | Exploring        |
|        6 | Dora        | Glasgow   | G12 8EX  | Explorer      | Spanish     | Exploring        |
|        1 | Bogus Mcgee | Glasgow   | G12 9TY  | Worm Charmer  | British     | Infernal Affairs |
|        2 | Flynn Boyle | Edinburgh | EH7 1AH  | Trout Tickler | British     | Infernal Affairs |
|        5 | Pickle Lily | Glasgow   | G3 6HN   | Life Model    | German      | Infernal Affairs |
+----------+-------------+-----------+----------+---------------+-------------+------------------+

    The task then of displaying the employees in separate tables depending upon the department in which they work is straightforward if you create an array to hold department names. Using in_array as a test you can determine if the current record within the while loop requires to be placed in a new table if you sort the table, in the sql query, by department.

    $sql='select * from `employee` order by `department`';
$res=$conn->query( $sql );
if( $res ){

    $depts=array();
    $index=0;
    
    while( $rs=$res->fetch_object() ){
        # new department, create new table
        if( !in_array( $rs->department, $depts ) ){
            #save department name
            $depts[]=$rs->department;

            # close previous table
            if( $index > 0 ) echo '
                </table>';

            # begin new table
            printf(
                '
                <table border=1 cellpadding="5px" cellspacing="5px" style="border-collapse:none; margin:2rem 0; width:50%%;background:silver">
                    <colgroup>
                        <col width="5%%" />
                        <col width="65%%" />
                        <col width="30%%" />
                    </colgroup>
                    <caption>Department: %s</caption>
                    <tr style="background:azure">
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                    </tr>',
                    $rs->department,
                    $rs->idnumber,
                    $rs->emplename,
                    $rs->nationality
            );
            
            $index++;
            
        }else{
            # continue existing table/department.
            printf(
                '
                    <tr style="background:azure">
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                    </tr>',
                $rs->idnumber,
                $rs->emplename,
                $rs->nationality
            );
        }
    }
}

    The above then yields (inline styles added for clarity) – view the snippet for rendered html

    <table border=1 cellpadding="5px" cellspacing="5px" style="border-collapse:none; margin:2rem 0; width:50%;background:silver">
  <colgroup>
    <col width="5%" />
    <col width="65%" />
    <col width="30%" />
  </colgroup>
  <caption>Department: Baguette Bashing</caption>
  <tr style="background:azure">
    <td>3</td>
    <td>Rusty Nail</td>
    <td>French</td>
  </tr>
</table>
<table border=1 cellpadding="5px" cellspacing="5px" style="border-collapse:none; margin:2rem 0; width:50%;background:silver">
  <colgroup>
    <col width="5%" />
    <col width="65%" />
    <col width="30%" />
  </colgroup>
  <caption>Department: Exploring</caption>
  <tr style="background:azure">
    <td>4</td>
    <td>Dusty Bin</td>
    <td>Spanish</td>
  </tr>
  <tr style="background:azure">
    <td>6</td>
    <td>Dora</td>
    <td>Spanish</td>
  </tr>
</table>
<table border=1 cellpadding="5px" cellspacing="5px" style="border-collapse:none; margin:2rem 0; width:50%;background:silver">
  <colgroup>
    <col width="5%" />
    <col width="65%" />
    <col width="30%" />
  </colgroup>
  <caption>Department: Infernal Affairs</caption>
  <tr style="background:azure">
    <td>1</td>
    <td>Bogus Mcgee</td>
    <td>British</td>
  </tr>
  <tr style="background:azure">
    <td>2</td>
    <td>Flynn Boyle</td>
    <td>British</td>
  </tr>
  <tr style="background:azure">
    <td>5</td>
    <td>Pickle Lily</td>
    <td>German</td>
  </tr>
    Login or Signup to reply.
  2. 0

    First you can fetch each unique department via GROUP BY or DISTINCT. And store it in Array.
    After that run foreach() loop and run query in foreach() loop and create table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search