0

I have below table:

CREATE TABLE `test` (
`id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`due_date` date NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test`(`id`, `title`, `due_date`) VALUES (1,'name1','2017-02-02');
INSERT INTO `test`(`id`, `title`, `due_date`) VALUES (2,'name2','2017-06-01');
INSERT INTO `test`(`id`, `title`, `due_date`) VALUES (3,'name3','2017-02-27');
INSERT INTO `test`(`id`, `title`, `due_date`) VALUES (4,'name4','2017-03-10');
INSERT INTO `test`(`id`, `title`, `due_date`) VALUES (5,'name5','2016-06-09');

And I have php code for print my data:

$database = new Database();
$database->query("SELECT * from test");
$database->execute();
$arr = $database->resultset(); ?>
<table>
    <thead>
    <tr>
        <th>id</th>
        <th>name</th>
        <th>due_date</th>
    </tr>
    </thead>
    <tbody>
    <?php foreach ($arr as $row): array_map('htmlentities', $row); ?>
        <tr>
            <td><?php echo implode('</td><td>', $row); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

Now I print all rows. I have due_date column, date format. How can I group my data for three catagorees: this week, next week and other week ( depends from due_date) . And do that my code print:

This week
id name  due_date
3  name3 2017-02-27
Next week
4  name4  2017-03-10
Other week
1 ...
2 ...
5 ...
Александр Горобец
  • 113
  • 1
  • 2
  • 12
  • Possible duplicate of [Using group by on multiple columns](http://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns) – Shishil Patel Feb 28 '17 at 13:18

1 Answers1

-1

You are asking about SQL Group, but this is not what you need. You just have to sort your query and then, in the loop, break the table as you need.

$database->query("SELECT * from test WHERE due_date >= NOW() ORDER BY due_date");

The WHERE clause will not show you the past tasks. Check if you need it or not.

Then in the FOREACH statement, you need to check always the current element with the last element you already parsed

$lastDiff = -1;
$current_week = date('W') ;
foreach ($arr as $row):
    array_map('htmlentities', $row);
    $currentWeek = date('W', strtotime($row['due_date']));
    $diff = $week - $current_week;
    if ($lastDiff != $diff && $lastDiff < 2):
        $lastDiff = $diff;
        ?><tr><td><?php echo week_name($diff) ?></td></tr>
        <?php
    endif;
    ?>
    <tr>
        <td><?php echo implode('</td><td>', $row); ?></td>
    </tr><?php
endforeach;

And fix the title separator with a custom function like the following one

function week_name($week)
{
    switch ($diff) {
        case 0:
        $name = 'This Week';
        break;
        case 1:
        $name = 'Next Week';
        break;
        default:
        $name = 'Future Weeks';
        break;
    }

    return $name;
}
Simone Cabrino
  • 901
  • 9
  • 24