PHP dynamic table with content grouping

Question

What am i trying to do

I have an array that looks like this:

Array
(
[0] => Array
    (
        [id] => 1
        [date] => 23-09-2015
        [name] => Public Transport
        [group_id] => 1
    )

[1] => Array
    (
        [id] => 2
        [date] => 23-09-2015
        [name] => Parking
        [group_id] => 1
    )

[2] => Array
    (
        [id] => 4
        [date] => 23-09-2015
        [name] => Food
        [group_id] => 2
    )

[3] => Array
    (
        [id] => 3
        [date] => 23-09-2015
        [name] => Fix
        [group_id] => 3
    )

[4] => Array
    (
        [id] => 4
        [date] => 23-09-2015
        [name] => Entertainment
        [group_id] => 3
    )

)

I want to list it in a table with grouping done according to group id

The group has transport [1], accommodation [2] and allowance [3]. [4] ungrouped.

The table structure should look like the one shown below.

  ID| Date     | Name             |
  ---------------------------------
  ---------------------------------
  1 |23-09-2015|Public Transport
  2 |23-09-2015|Parking
  ---------------------------------
          TRANSPORTATION
  ---------------------------------
  3 |23-09-2015|Food
  ---------------------------------
          ACCOMMODATION
  ---------------------------------
  4 |23-09-2015|Fix
  ---------------------------------
          ALLOWANCE
  ---------------------------------
  5 |23-09-2015|Entertainment

As you can see here, id 1 and 2 fall under Transportation, 3 under housing, 4 under supervision, and entertainment does not have a group. If any dosenot group has data in the array, then it should show NO DATA AVAILABLE

  ---------------------------------
  3 |23-09-2015|Food
  ---------------------------------
          ACCOMMODATION
  ---------------------------------
       No data available
  ---------------------------------
          ALLOWANCE
  ---------------------------------

What i tried

     <?php
     $j = 1;
     foreach ($summary as $list) {
        if ($j != $list['group_id']) {

             if ($j == 1) {
                   $group = 'Transportation';
             } else if ($j == 2) {
                    $group = 'Accomodation';
              } else if ($j == 3) {
                    $group = 'Allowances';
              } else {
                    $group = '';
              }
          ?>

                        <tr class="summary-title">
                            <td colspan="7"><?php echo $group; ?></td>
                        </tr>
                    <?php } ?>
      <tr>

           <td><?php echo $list['id']; ?></td>
           <td><?php echo $list['date']; ?></td>
           <td><?php echo $list['name']; ?></td>
      </tr>
        <?php
            $j = $list['group_id'];
        }
        ?>

The array is ordered according to group_id as ascending. The group identifier will always be 1, 2, 3, 4.

Problem with my code

. NO DATA AVAILABLE, group_id. , group_id = 4 , ALLOWANCE , , .

Answer 1

EDIT: , .

$summary = array();
$summary[] = array('id'=>1, 'date'=>"23-09-2915", 'name'=>"Public transpor1", 'group_id'=> 1);
$summary[] = array('id'=>2, 'date'=>"24-09-2915", 'name'=>"Public transpor2", 'group_id'=> 3);
$summary[] = array('id'=>3, 'date'=>"25-09-2915", 'name'=>"Public transpor3", 'group_id'=> 1);
$summary[] = array('id'=>4, 'date'=>"26-09-2915", 'name'=>"Public transpor4", 'group_id'=> 2);
$summary[] = array('id'=>5, 'date'=>"27-09-2915", 'name'=>"Public transpor5", 'group_id'=> 1);
$newarray = array('Transportation'=>array(),'Accomodation'=>array(),'Allowances'=>array(),'Not found'=>array());
foreach ($summary as $list) {
    if(isset($list['group_id'])){
         $testid = $list['group_id'];
    }else{
         $testid = 4;
    }
    if ($testid  == 1) {
        $group = 'Transportation';
    } else if ($testid  == 2) {
        $group = 'Accomodation';
    } else if ($testid  == 3) {
        $group = 'Allowances';
    } else {
        $group = 'Not found';
    }
    $newarray[$group][] = $list;
}
echo "<table>";
foreach($newarray as $key => $array){
    echo "<tr><th>".$key."</th></tr>";
    if(count($array) > 0){
        foreach($array as $row){
            echo "<tr><td>".$row['name']."</td></tr>";
        }
    }else{
        echo "<tr><td>Not data</td></tr>";
    }
}
echo "</table>";

, , , LOL. , , , .

Answer 2

, . .

Dump:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE DATABASE IF NOT EXISTS `my` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `my`;


CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `FK_category_category_group` (`group_id`),
  CONSTRAINT `FK_category_category_group` FOREIGN KEY (`group_id`) REFERENCES `category_group` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*!40000 ALTER TABLE `category` DISABLE KEYS */;
INSERT INTO `category` (`id`, `name`, `group_id`) VALUES
    (1, 'Public Transport', 1),
    (2, 'Parking', 1),
    (3, 'Food', 2),
    (4, 'Fix', 3),
    (5, 'Entertainment', 3),
    (6, 'Category without group 1', NULL),
    (7, 'Category without group 2', NULL);
/*!40000 ALTER TABLE `category` ENABLE KEYS */;

CREATE TABLE IF NOT EXISTS `category_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*!40000 ALTER TABLE `category_group` DISABLE KEYS */;
INSERT INTO `category_group` (`id`, `name`) VALUES
    (2, 'Accommodation '),
    (3, 'Allowance '),
    (1, 'TRANSPORTATION');
/*!40000 ALTER TABLE `category_group` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

PHP:

$categoriesByGroups = array();
$pdo = new PDO('mysql:host=localhost;dbname=my', 'root');
$query = $pdo->prepare('
    SELECT c.id AS category_id,
           c.name AS category_name,
           c.group_id,
           g.name AS group_name
      FROM category AS c
      LEFT JOIN category_group AS g ON g.id = c.group_id
');

$query->execute();

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    if ($row['group_id'] != null) {
        $groupId = $row['group_id'];
    } else {
        $groupId = 0;
    }

    if (!isset($categoriesByGroups[$groupId])) {
        $categoriesByGroups[$groupId] = array(
            'categories' => array(),
            'group' => $groupId == null ? 'Without group' : $row['group_name']
        );
    }

    $categoriesByGroups[$groupId]['categories'][] = $row['category_name'];
}

foreach ($categoriesByGroups as $group) {
    echo 'Group: ' . $group['group'] . '<br/>';
    echo 'Categories: <br/>';
    foreach ($group['categories'] as $category) {
        echo $category . '<br/>';
    }
    echo '--------------------------------<br/>';
}