Hoe kan ik bestellingen met een timestamp groeperen eerst per jaar en dan per maand?
Nou wil ik graag een overzicht wat er zo uitziet:
2021
- Jan
-- Orderinfo
-- Orderinfo
-- Orderinfo
- Feb
-- Orderinfo
- Mar
2020
- Jan
-- Orderinfo
-- Orderinfo
-- Orderinfo
- Feb
- Mar
-- Orderinfo
-- Orderinfo
-- Orderinfo
Hoe kan ik zoiets voor elkaar krijgen?
Dat hij de oudste datum pakt en vanaf daar begint met groeperen naar de nieuwste datum.
Ik heb nu deze query:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT *
FROM orders
WHERE korting_id IN (SELECT korting_id FROM kortingcodes WHERE account_id = "59")
AND tmp = 1
ORDER BY datum
FROM orders
WHERE korting_id IN (SELECT korting_id FROM kortingcodes WHERE account_id = "59")
AND tmp = 1
ORDER BY datum
Die de volgende data ophaalt (alleen relevante info voor deze post):
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Array
(
[id] => 1359
[order_id] => 59.1359
[totaalprijs] => 27
[account_id] => 59
[datum] => 2021-03-01 11:05:47
[korting_id] => 32
[tmp] => 1
)
Array
(
[id] => 1360
[order_id] => 59.1360
[totaalprijs] => 27
[account_id] => 59
[datum] => 2020-04-02 12:05:47
[korting_id] => 32
[tmp] => 1
)
Array
(
[id] => 1361
[order_id] => 59.1361
[totaalprijs] => 27
[account_id] => 59
[datum] => 2020-02-07 15:02:47
[korting_id] => 32
[tmp] => 1
)
(
[id] => 1359
[order_id] => 59.1359
[totaalprijs] => 27
[account_id] => 59
[datum] => 2021-03-01 11:05:47
[korting_id] => 32
[tmp] => 1
)
Array
(
[id] => 1360
[order_id] => 59.1360
[totaalprijs] => 27
[account_id] => 59
[datum] => 2020-04-02 12:05:47
[korting_id] => 32
[tmp] => 1
)
Array
(
[id] => 1361
[order_id] => 59.1361
[totaalprijs] => 27
[account_id] => 59
[datum] => 2020-02-07 15:02:47
[korting_id] => 32
[tmp] => 1
)
Hoe kan ik bovenstaande data zo groeperen?
Dat zou dus een
ORDER BY YEAR(datum) DESC,
datum ASC
betekenen.
En voor de weergave met "kopjes" voor jaartal een maand zul je iets in PHP moeten regelen, zoals Ward al zegt.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SET lc_time_names = 'nl_NL'; -- Nederlandse namen
WITH -- Testdata
RECURSIVE `bereik` (`nr`) AS (
SELECT 0 UNION ALL SELECT `nr` + 1
FROM `bereik` WHERE `nr` + 1 <= 64),
`datums` AS (
SELECT CURRENT_DATE - INTERVAL `nr` WEEK AS `datum`
FROM `bereik`),
`orders` AS (
SELECT
YEAR(`datum`) AS `jaar`,
MONTH(`datum`) AS `maand`,
`datum`,
DATE_FORMAT(`datum`, '%b') AS `maandnaam`
FROM `datums`)
SELECT -- Overzicht
`jaar` AS `Jaar`,
CONCAT(UPPER(LEFT(`maandnaam`, 1)),
SUBSTR(`maandnaam`, 2, 2)) AS `Maand`,
`datum` AS `Orderinfo`
FROM `orders`
ORDER BY `jaar` DESC, `maand` ASC, `datum` DESC;
WITH -- Testdata
RECURSIVE `bereik` (`nr`) AS (
SELECT 0 UNION ALL SELECT `nr` + 1
FROM `bereik` WHERE `nr` + 1 <= 64),
`datums` AS (
SELECT CURRENT_DATE - INTERVAL `nr` WEEK AS `datum`
FROM `bereik`),
`orders` AS (
SELECT
YEAR(`datum`) AS `jaar`,
MONTH(`datum`) AS `maand`,
`datum`,
DATE_FORMAT(`datum`, '%b') AS `maandnaam`
FROM `datums`)
SELECT -- Overzicht
`jaar` AS `Jaar`,
CONCAT(UPPER(LEFT(`maandnaam`, 1)),
SUBSTR(`maandnaam`, 2, 2)) AS `Maand`,
`datum` AS `Orderinfo`
FROM `orders`
ORDER BY `jaar` DESC, `maand` ASC, `datum` DESC;
Als je dan de rijen uit MySQL ophaalt, kan je een array gebruiken om een boomstructuur op te zetten, iets als:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php
// ...
$aOverzicht = [];
foreach ($aData as $aRij) {
// initialiseren
if (!isset($aOverzicht[$aRij['Jaar']])) {
$aOverzicht[$aRij['Jaar']] = NULL;
}
if (!isset($aOverzicht[$aRij['Jaar']][$aRij['Maand']])) {
$aOverzicht[$aRij['Jaar']][$aRij['Maand']] = [];
}
$aOverzicht[$aRij['Jaar']][$aRij['Maand']][] = $aRij['Orderinfo'];
}
foreach ($aOverzicht as $sJaar => $aMaand) {
foreach ($aMaand as $sMaand => $aOrders) {
foreach {$aOrders as $sOrderinfo) {
// regel weergeven
}
}
}
// ...
?>
// ...
$aOverzicht = [];
foreach ($aData as $aRij) {
// initialiseren
if (!isset($aOverzicht[$aRij['Jaar']])) {
$aOverzicht[$aRij['Jaar']] = NULL;
}
if (!isset($aOverzicht[$aRij['Jaar']][$aRij['Maand']])) {
$aOverzicht[$aRij['Jaar']][$aRij['Maand']] = [];
}
$aOverzicht[$aRij['Jaar']][$aRij['Maand']][] = $aRij['Orderinfo'];
}
foreach ($aOverzicht as $sJaar => $aMaand) {
foreach ($aMaand as $sMaand => $aOrders) {
foreach {$aOrders as $sOrderinfo) {
// regel weergeven
}
}
}
// ...
?>
Dat gedoe met arrays kan je overslaan als je in de database gebruik weet te maken van window functies om te bepalen of het om een eerste rij of laatste rij van een groep gaat.
Ik heb nu de volgende code:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?PHP
$getresellerinfo = '
SELECT *
FROM orders
WHERE korting_id IN (SELECT korting_id FROM kortingcodes WHERE account_id = "'.$conn->real_escape_string($_GET['account_id']).'")
AND tmp = 1
ORDER BY datum DESC';
$getresellerinfocon = $conn->query($getresellerinfo);
while($getresellerinfo = $getresellerinfocon->fetch_assoc()){
$datestr = strtotime($getresellerinfo['datum']);
if (date('Y', $datestr) !== $lastFrom) {
$dategrouphtml .= date('Y', $datestr).'<br>';
if (date('M', $datestr) !== $lastFrom1) {
$dategrouphtml .= date('M', $datestr).'<br>';
$dategrouphtml .= $getresellerinfo['order_id'].'<br>';
}
$lastFrom1 = date('M', $datestr);
}
$lastFrom = date('Y', $datestr);
}
echo $dategrouphtml;
?>
$getresellerinfo = '
SELECT *
FROM orders
WHERE korting_id IN (SELECT korting_id FROM kortingcodes WHERE account_id = "'.$conn->real_escape_string($_GET['account_id']).'")
AND tmp = 1
ORDER BY datum DESC';
$getresellerinfocon = $conn->query($getresellerinfo);
while($getresellerinfo = $getresellerinfocon->fetch_assoc()){
$datestr = strtotime($getresellerinfo['datum']);
if (date('Y', $datestr) !== $lastFrom) {
$dategrouphtml .= date('Y', $datestr).'<br>';
if (date('M', $datestr) !== $lastFrom1) {
$dategrouphtml .= date('M', $datestr).'<br>';
$dategrouphtml .= $getresellerinfo['order_id'].'<br>';
}
$lastFrom1 = date('M', $datestr);
}
$lastFrom = date('Y', $datestr);
}
echo $dategrouphtml;
?>
Zodra het jaar veranderd echo ik het jaar, zelfde voor de maanden en dan daarin voor nu om te testen toon ik een order id. Probleem is dat ik een maand mis.
Dit is voorbeeld output:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Array
(
[id] => 1358
[order_id] => 59.1358
[totaalprijs] => 27
[account_id] => 59
[datum] => 2021-05-14 11:05:47
[korting_id] => 32
[tmp] => 1
)
Array
(
[id] => 1359
[order_id] => 59.1359
[totaalprijs] => 27
[account_id] => 59
[datum] => 2021-03-01 12:05:47
[korting_id] => 32
[tmp] => 1
)
Array
(
[id] => 1360
[order_id] => 59.1360
[totaalprijs] => 27
[account_id] => 59
[datum] => 2020-08-06 15:02:47
[korting_id] => 32
[tmp] => 1
)
(
[id] => 1358
[order_id] => 59.1358
[totaalprijs] => 27
[account_id] => 59
[datum] => 2021-05-14 11:05:47
[korting_id] => 32
[tmp] => 1
)
Array
(
[id] => 1359
[order_id] => 59.1359
[totaalprijs] => 27
[account_id] => 59
[datum] => 2021-03-01 12:05:47
[korting_id] => 32
[tmp] => 1
)
Array
(
[id] => 1360
[order_id] => 59.1360
[totaalprijs] => 27
[account_id] => 59
[datum] => 2020-08-06 15:02:47
[korting_id] => 32
[tmp] => 1
)
En dit is wat er wordt geechoed:
2021
May
59.1358
2020
Aug
59.1361
Zoals je ziet mist er een maand in 2021, maart. Dus er gaat iets nog niet helemaal goed in m'n loop.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?PHP
$getresellerinfo = '
SELECT *
FROM orders
WHERE korting_id IN (SELECT korting_id FROM kortingcodes WHERE account_id = "'.$conn->real_escape_string($_GET['account_id']).'")
AND tmp = 1
ORDER BY datum DESC';
$getresellerinfocon = $conn->query($getresellerinfo);
while($getresellerinfo = $getresellerinfocon->fetch_assoc()){
// Zet datum om in secondes
$datestr = strtotime($getresellerinfo['datum']);
// Check of jaar gelijk is aan vorige jaar loop
if (date('Y', $datestr) !== $lastFrom) {
$dategrouphtml .= date('Y', $datestr).'<br>';
}
$lastFrom = date('Y', $datestr);
// Check of maand gelijk is aan vorige maand loop
if (date('M', $datestr) !== $lastFrom1) {
$dategrouphtml .= date('M', $datestr).'<br>';
}
$dategrouphtml .= $getresellerinfo['order_id'].'<br>';
$lastFrom1 = date('M', $datestr);
}
echo $dategrouphtml;
?>
$getresellerinfo = '
SELECT *
FROM orders
WHERE korting_id IN (SELECT korting_id FROM kortingcodes WHERE account_id = "'.$conn->real_escape_string($_GET['account_id']).'")
AND tmp = 1
ORDER BY datum DESC';
$getresellerinfocon = $conn->query($getresellerinfo);
while($getresellerinfo = $getresellerinfocon->fetch_assoc()){
// Zet datum om in secondes
$datestr = strtotime($getresellerinfo['datum']);
// Check of jaar gelijk is aan vorige jaar loop
if (date('Y', $datestr) !== $lastFrom) {
$dategrouphtml .= date('Y', $datestr).'<br>';
}
$lastFrom = date('Y', $datestr);
// Check of maand gelijk is aan vorige maand loop
if (date('M', $datestr) !== $lastFrom1) {
$dategrouphtml .= date('M', $datestr).'<br>';
}
$dategrouphtml .= $getresellerinfo['order_id'].'<br>';
$lastFrom1 = date('M', $datestr);
}
echo $dategrouphtml;
?>
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
while($getresellerinfo = $getresellerinfocon->fetch_assoc()){
// Zet datum om in secondes
$datestr = strtotime($getresellerinfo['datum']);
// Check of jaar gelijk is aan vorige jaar loop
if (date('Y', $datestr) !== $current_year) {
$dategrouphtml .= date('Y', $datestr).'<br>';
}
$current_year = date('Y', $datestr);
// Check of maand gelijk is aan vorige maand loop
if (date('M', $datestr) !== $current_month) {
$dategrouphtml .= date('M', $datestr).'<br>';
}
$dategrouphtml .= $getresellerinfo['order_id'].'<br>';
$current_month = date('M', $datestr);
}
?>
while($getresellerinfo = $getresellerinfocon->fetch_assoc()){
// Zet datum om in secondes
$datestr = strtotime($getresellerinfo['datum']);
// Check of jaar gelijk is aan vorige jaar loop
if (date('Y', $datestr) !== $current_year) {
$dategrouphtml .= date('Y', $datestr).'<br>';
}
$current_year = date('Y', $datestr);
// Check of maand gelijk is aan vorige maand loop
if (date('M', $datestr) !== $current_month) {
$dategrouphtml .= date('M', $datestr).'<br>';
}
$dategrouphtml .= $getresellerinfo['order_id'].'<br>';
$current_month = date('M', $datestr);
}
?>
Zie je het verschil?
Verder valt me nog op dat je $lastFrom en $lastFrom1 nergens initialiseert. De eerste keer dat je ze vergelijkt in het if-statement bestaan ze dus nog niet. Dat is niet netjes en zal hoogstwaarschijnlijk in je error-log een warning opleveren. Zorg daarom dat je beiden variabelen vóór de while-loop initialiseert.
Code (php)
1
2
3
4
5
6
2
3
4
5
6
<?
$lastFrom = '';
$lastFrom1 = '';
?>
$lastFrom = '';
$lastFrom1 = '';
?>
In dat geval is namelijk je $lastFrom1 nog steeds maart (van 2021)
Toevoeging op 19/05/2021 12:01:49:
regel 10 (van Ozzie) zou boven de accolade van regel 9 moeten staan (je hoeft alleen het huidige jaar op te slaan als die veranderd is)
en die kan dan gevolgd worden door $current_month = null;
Zodat je na een kopje voor het jaartal altijd een kopje voor de maand krijgt. (aangenomen de maand in kwestie is niet gelijk aan NULL.
Ik loop nog wel tegen 1 probleem aan met mijn oplossing. Hoe kan ik nu een element, bijvoorbeeld een div, openen en sluiten om bijvoorbeeld alleen een jaar, of een maand?