Entry uit tabel weergeven als de entry op of binnen een bepaalde datum valt

    Hallo ICT'ers,


    Ik ben bezig met een project voor een zwembad die een rooster website wil hebben. Het moet hier mogelijk zijn om een rooster te maken en daarbij moeten medewerkers voorkeuren voor het rooster kunnen opgeven. Dit alles werkt, maar met het weergaven van de voorkeuren bij de juiste datum stuit ik op een probleem: hij geeft momenteel de voorkeur ook weer op 1 en 2 dagen eerder, terwijl dat niet zou moeten. De SQL van de voorkeuren ziet er zo uit:


    id - int(250)
    user - int(250)
    period - int(250)
    date_from - datetime
    date_to - datetime
    reason - int(1)
    reason_desc - varchar(50)


    Stel je voor een medewerker geeft een voorkeur op dat hij een hele dag vrij wenst te zijn, in de database komt er dan een record bij:


    id - 1
    user - 5
    period - 6 (verwijst naar de periode waar het rooster in loopt)
    date_from - 2016-08-10 00:00:00
    date_to - 2016-08-10 23:59:59
    reason - 1
    reason_desc - vrij


    Nu moet dit record dus komen bij de juiste te roosteren dag (10 augustus 2016). Het kan echter ook zijn dat er een voorkeur voor een gehele periode opgegeven wordt (meerdere dagen). In elk geval kan elke medewerker per dag (ook al is het maar een voorkeur voor een paar uur op die dag) maximaal één voorkeur opgeven. Het is belangrijk dat als er een voorkeur van 9 augustus 00:00:00 t/m 11 augustus 23:59:59 opgegeven wordt, dat deze dus op zowel 9, als 10, als 11 augustus zichtbaar is en dus uit de tabel gehaald wordt.


    De query die ik nu gebruik is deze:

    SQL
    SELECT UNIX_TIMESTAMP(date_from) as van, UNIX_TIMESTAMP(date_to) as tot, reason, reason_desc FROM voorkeuren WHERE user='".$emp_sel['id']."' AND period='".$periode['id']."' AND ((UNIX_TIMESTAMP(date_from) <= '".$date_from."' && UNIX_TIMESTAMP(date_to) >= '".$date_to."') OR (UNIX_TIMESTAMP(date_from) <= '".$date_from."' AND UNIX_TIMESTAMP(date_to) <= '".$date_to."' AND UNIX_TIMESTAMP(date_to) >= '".$date_from."') || (UNIX_TIMESTAMP(date_from) >= '".$date_from."' && (UNIX_TIMESTAMP(date_to) <= '".$date_to."' || UNIX_TIMESTAMP(date_to) >= '".$date_from."'))) ORDER BY date_from ASC LIMIT 1


    $date_from verwijst naar een variabele die de UNIX TIMESTAMP van het begin van de dag bevat, bijvoorbeeld die van 10 augustus 00:00:00. $date_to verwijst naar een variabele die de UNIX TIMESTAMP van het einde van de dag bevat, bijvoorbeeld die van 10 augustus 23:59:59.


    De entry zoals bovenstaande zorgt er in de bovenstaande query echter voor dat die voorkeur zowel op 8, 9 als 10 augustus zichtbaar is. Hoe zorg ik ervoor dat deze alleen op de 10e zichtbaar is, waarvoor de entry ook bedoeld is?


    Ik heb al uren naar de query gekeken maar ik kom er gewoon niet uit.. Wie zou me misschien een handje willen helpen met het oplossen van deze query?


    Alvast heel erg bedankt!


    Met vriendelijke groet,


    Lars Groot

    Allereerst: je hoeft niet alles om te zetten naar UNIX_TIMESTAMPs. Een DATETIME (YYYY-MM-DD HH:MM:SS) is namelijk zo opgebouwd dat deze alfabetisch dezelfde ordening / sortering volgt als een numerieke variant. Je kunt met DATETIMEs dus op precies dezelfde wijze rekenen (en dezelfde operatoren gebruiken) als dat je met getallen/floats zou doen.


    Ten tweede: dit is wel een hele bijzondere mix van AND, OR en (vervolgens) && en ||. Het is voor mij lastig om direct te zeggen wat deze code / query nu concreet zou doen :p (iha: de query leest allesbehalve prettig). Het is denk ik beter wanneer je één schrijfwijze (bij voorkeur AND en OR) aanhoudt. Te meer omdat in MySQL ook de operatoren & en | bestaan die iets heel anders betekenen.


    Ook heeft MySQL functies als DATE() waarmee je het datum-deel van een DATETIME kunt opvragen.


    Tot slot: het maken van een simpel tekeningetje kan enorm helpen. Teken een of meer tijdslijnen en het tijdsinterval waarin je geinteresseerd bent en kijk dan naar de verschillende situaties die kunnen optreden om het gevalsonderscheid (in de gevallen waarin je geinteresseerd bent) in je query te kunnen maken.


    Er zijn dus al een heleboel dingen die je kunt doen om de query flink in te korten en dingen voor jezelf overzichtelijk te maken.


    Echter, misschien wil je je strategie herzien of op zijn minst herbestuderen. Zo zou je kunnen overwegen om dingen in tijdsblokken (kwartier? half uur? uur? dagdeel? dag?) op te delen. Het grote voordeel hiervan is dat je expliciet vastlegt wie wat op welk moment aan het doen is. Want op dit moment moet je dit elke keer indirect herberekenen / afleiden uit intervallen, wat mogelijk niet altijd handig is. Wanneer je je databasevragen dan bijvoorbeeld uit een ander perspectief gaat stellen ("Wie is er op tijdstip/tijdsinterval X beschikbaar") dan wordt het verdomd lastig zoniet onmogelijk om hier direct/makkelijk een antwoord op te geven. Vraag dus jezelf (mede) af: welke vragen wil ik kunnen beantwoorden bij dit roostersysteem (of wat het ook is). Het (technische) databaseontwerp zou in dienst moeten staan van wat je (functioneel) met dit systeem wilt kunnen doen.

Participate now!

Heb je nog geen account? Registreer je nu en word deel van onze community!

Maak een account aan Login