Group data from two database tables

Question

I have table in database:

Group:
    | id | Category | title  | 
    | 1  | 1        | group1 | 
    | 2  | 2        | group2 | 
    | 3  | 1        | group3 |
    | 4  | 3        | group4 | 
    | 5  | 2        | group5 | 
    | 6  | 1        | group6 | 

News:
    | id | Group    | title  | body  |
    | 1  | 3        | title1 | body1 |
    | 2  | 2        | title2 | body2 |
    | 3  | 1        | title3 | body3 |
    | 4  | 4        | title4 | body4 |
    | 5  | 1        | title5 | body5 |
    | 6  | 5        | title6 | body6 |
    | 7  | 3        | title7 | body7 |
    | 8  | 2        | title8 | body8 |
    | 9  | 1        | title9 | body9 |
    | 10 | 6        | title10| body10 |
    | 11 | 1        | title11| body11 |
    | 12 | 5        | title12| body12 |

How can I show this as:

-GROUP1, GROUP3 and GROUP6
//GROUP1 (category1)
--title3
--title5
--title9
//GROUP3 (category1)
--title1
--title7
//GROUP6 (category1)
--title10
-GROUP2 and GROUP5 
//GROUP2 (category2)
--title2
--title8
//GROUP5 (category2)
--title6
--titl12
-GROUP4 
//GROUP4 (category3)
--title4

I will make this in foreach.

Answer 1

Your exact requested output makes this complicated.

$sql = 'SELECT n.title, n.Group AS group_id, g.Category AS cat_id
        FROM News AS n
        JOIN Group AS g ON g.id = group_id
        ORDER BY cat_id, group_id, n.id';

$result = mysql_query($query);

$categories = array();

while ($row = mysql_fetch_assoc($result)) {
    $catID = $row['cat_id'];
    $groupID = $row['group_id'];
    $title = $row['title'];

    $categories[$catID]['groups'][$groupID]['titles'][] = $title;
}

foreach ($categories as $catID => $groups) {

    $catGroups = '-GROUP'.implode(', GROUP',array_keys($groups)).PHP_EOL;
    $lastComma = strrpos($catGroups,',');
    if ($lastComma !== false) {
        $catGroups = substr($catGroups,0,$lastComma-1).
                     ' AND ' .substr($catGroups,$lastComma+1);
    }
    echo $catGroups;

    foreach ($groups as $groupID => $titles) {
        echo "//GROUP$groupID (category$catID)".PHP_EOL;
        foreach ($groups as $group => $titles) {
            echo '--'.$title.PHP_EOL;
        }
    }
}

If you didn't need such fancy output, this would be much simpler.

$sql = 'SELECT n.title, n.Group AS group_id, g.Category AS cat_id
        FROM News AS n
        JOIN Group AS g ON g.id = group_id
        ORDER BY cat_id, group_id, n.id';

$result = mysql_query($query);

$lastCatID = null;
$lastGroupID = null;

while ($row = mysql_fetch_assoc($result)) {
    $catID = $row['cat_id'];
    $groupID = $row['group_id'];
    $title = $row['title'];

    if ($catID !== $lastCatID){
        echo "*** CATEGORY $catID\n";
        $lastCatID = $catID;
    }
    if ($groupID !== $lastGroupID){
        echo "GROUP $groupID\n";
        $lastGroupID = $groupID;
    }
    echo "-- $title\n";
}