Door
Crispijn -
op 16-09-2013 20:29
gewijzigd op 16-09-2013 20:32
2.317 views
Beste php-ers,
Voor een online boekingssysteem ben ik een grafiek aan het maken met het aantal boekingen per jaar. Hierdoor kan inzicht verkregen worden of de boekingen van het nieuwe jaar op schema liggen.
Nu vraagt de grafiek om een xml formaat waar ik per week het aantal boekingen wil 'invullen'. ue.datum is opgeslagen als DATETIME formaat.
Per week draai ik de volgende query:
<?php
$weken = range(1,53);
$jaar = 2013;
$aantal = 0;
foreach($weken as $week):
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(DATE_FORMAT(e.start, \'%Y\') = '.$jaar.')
AND
(DATE_FORMAT(ue.datum, \'%V\') = '.$week.')';
//query uitvoeren
//row ophalen en bij aantal optellen dus bijvoorbeeld: $aantal = $aantal + $row;
endforeach;
?>
Als ik ter controle het volgende doe krijg ik net iets meer resultaten.
<?php
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(DATE_FORMAT(e.start, \'%Y\') = '.$jaar.')';
?>
En dat kan niet de bedoeling zijn, op beide methoden moet ik evenveel boekingen kunnen behalen in dat bepaalde jaar. Kan iemand misschien een tip geven waarom ik cumulatief per week minder resultaten heb dan wanneer ik het allemaal in één keer selecteer? Ik ben aardig door het ISO date format gestruind en dan zie je dat ze een week 53 gebruiken. Verder zie ik geen bijzonderheden. Iemand een tip voor een andere aanpak of een effectieve methode?
Crispijn
edit
Ik bouw het hele project in Zend Framework en het totaal beslaat aardig wat code dat hier niet relevant is volgens mij, dit is de basis opzet die volgens mij ergens fout gaat. Mocht er behoefte zijn aan meer broncode dan hoor ik het graag!
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(YEAR(e.start) = '.$jaar.')
AND
(WEEK(ue.datum) = '.$week.')
AND
(YEAR(ue.datum) = '. $boekjaar.')';
//query uitvoeren
//row ophalen en bij aantal optellen dus bijvoorbeeld: $aantal = $aantal + $row;
endforeach;
?>
Mijn controle query is dan deze:
<?php
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(YEAR(e.start) = '.$jaar.')';
?>
Persoonlijk denk ik wel dat je het niet met een foreach moet gaan oplossen waarin je dan 53+ queries gaat afvuren.
Als ik jouw vraag goed begrepen heb wil je een rijtje van 53 weken van één kalenderjaar waarin per week staat hoeveel accommodaties? er verhuurd zijn.
Als dat klopt dan mis ik eigenlijk nog een einddatum, iets als e.end. want als je dat er bij betrekt kun je zoiets maken als Eddy aangeeft en kun je ook de de accommodaties meetellen die nog in het vorige kalenderjaar gestart zijn met de verhuur. (immers je hebt dan ook een einddatum).
Gewoon met een query:[code lamg="SQL"]
SELECT
w.weeknr,
COUNT(e.id) AS aantal
FROM
(SELECT
@weeknr := @weeknr + 1 weeknr
FROM
table_with_more_than_53_rows, (SELECT @weeknr:= 0) uservar
LIMIT 53
) w
LEFT JOIN
events e
ON WEEK(e.datum, 3) = w.weeknr
WHERE
e.datum BETWEEN '2013-01-01' AND '2013-12-31'
AND w.weeknr <= WEEK('2013-12-31', 3)
GROUP BY w.weeknr
[/code]
Ik ben een klein stukje verder met de opzet van jou die wel erg charmant is. Momenteel zie ik het nog niet helemaal maar heb ik dit:
SELECT YEAR( ue.datum ) AS jaarnummer, WEEK( ue.datum ) AS weeknummer, COUNT( ue.id ) AS aantal
FROM user_event AS ue
WHERE YEAR( ue.datum )
IN ( 2012, 2013 )
GROUP BY YEAR( ue.datum ) , WEEK( ue.datum )
ORDER BY YEAR( ue.datum ) , WEEK( ue.datum ) ASC
In phpmyadmin zie ik dat ik dan ook een nulde week heb in 2013, wel vreemd niet?
Morgen ga ik er mee verder en ga ik kijken of ik dan wel goed uit kom. Bedankt voor jullie hulp tot nu toe, ik ga verder met stoeien!
Ik zie dat dit klopt! Super, nu alleen even fixen dat de weken die 'ontbreken' 0 geven en dan kan ik een csv file genereren en de grafiek maken.
Bedankt voor jullie expertise heren!
Crispijn
[size=xsmall]Toevoeging op 16/09/2013 21:41:48:[/size]
@Ger,
De query die je schrijft snap ik niet helemaal. Maak je nu een virtuele tabel aan met meer dan 53 rijen? Kan je een kleine toelichting geven?
Je hebt een tabel nodig die meer dan 53 rijen bevat (welke tabel dat is maakt niets uit), uit die tabel genereer je dan de weeknr's 1 t/m 53. Dit omdat de mogelijkheid bestaat dat er in een week geen event bestaat.
Dat is dan een (derived) tabel met 1 kolom.
Daarna ga je die weeknrs op je events joinen op het weeknr.
Vanwege de left join krijgen de weken waarin geen event plaats vindt 0 als aantal, want ik tel op e.id en lege kolommen worden niet mee geteld.
@naam is in SQL een user variabele (uservar), net zoals je in PHP variabels hebt (alleen is het dan $naam).
Deze uservars bestaan in het geheugen van de database server zolang de connectie actief is, en zijn per connectie.
In mijn eerdere voorbeeld wordt die geset via de [code lang="SQL"]
.. FROM ....(SELECT @weeknr:= 0) uservar
[/code]
Daarna wordt met: [code lang="SQL"]
SELECT
@weeknr := @weeknr + 1 weeknr
FROM ...
[/code]
de waarde elke keer met 1 opgehoogd zolang de tabel in de FROM rijen heeft, en daarom moeten er minimaal 53 in die tabel zijn. Dat kan dus elke tabel zijn als ie maar min. 53 rijen heeft.
Gek genoeg als ik er deze query van maak zou ik resultaten met aantal = 0 terug moeten krijgen. Dat is echter niet het geval...
SELECT YEAR( ue.datum ) AS jaarnr, w.weeknr, COUNT( ue.id ) AS aantal
FROM (
SELECT @weeknr := @weeknr +1 weeknr
FROM user, (
SELECT @weeknr :=0
)uservar
LIMIT 53
)w
LEFT JOIN user_event AS ue ON WEEK( ue.datum, 3 ) = w.weeknr
LEFT JOIN event AS e ON ue.event = e.id
WHERE YEAR( ue.datum )
IN ( 2012, 2013 )
AND YEAR( e.start ) =2013
GROUP BY w.weeknr
ORDER BY w.weeknr
Enig idee wat er fout gaat? Ook als ik de query helemaal uitkleed geeft hij dit resultaat (zonder de join event)?
