Loading...
 
Skip to main content

Features / Usability

Forum List Topic List

Forums » Features / Usability » Need help making MySQL code work (trying to access tracker data)
 
Thread actions
Print this page
  • Print all pages

    • Need help making MySQL code work (trying to access tracker data)

    Posted by François Bachmann posts: 35 Switzerland

    Hi all

    I've come up with a tracker that contains, among other fields, two dates (from-to for vacations, if you need to know😀 )

    Now I'm finding it hard to include the filter in the query, in order to display only currently "active" vacations:

    from_date < current_date < to_date

    Any takers? Help much appreciated, so far I've spent two evenings on various flavours of FROM_UNIX, UNIX_TIMESTAMP, NOW(), CURRENT_TIMESTAMP etc., to no avail... 😑

    Francois

    Here's my code:

    Copy to clipboard
    {SQL(db=>Tiki)}
SELECT  
nameEmployee.value as name,
datestart.value as ux_start,
dateend.value as ux_end,

FROM 
tiki_tracker_item_fields AS datestart,
tiki_tracker_item_fields AS dateend,
tiki_tracker_item_fields AS nameEmployee,
tiki_tracker_items as vac_trk

WHERE datestart.fieldId=38 
AND nameEmployee.fieldId=37
AND dateend.fieldId=39
AND vac_trk.trackerId=9
AND datestart.itemId=dateend.itemId
AND datestart.itemId=vac_trk.itemId
AND vac_trk.itemId=nameEmployee.itemId

{SQL}
    Reads: 1145
    Posted by sylvie greverend posts: 1092

    Not sure to understand the problem but
    SELECT * FROM `a_test`
    WHERE now( ) > deb AND now( ) < END

    give me the rows only in the date


    Posted by François Bachmann posts: 35 Switzerland
    Found the culprit - turning "Allow HTML" on for the page does the trick (plus there's another workaround: using the BETWEEN statement in SQL, avoiding ">" and "