Sidan 1 av 2 1 2 SistaSista
Resultat 1 till 30 av 32

Ämne: Problematik datumsökning MySQL

  1. #1
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27

    Problematik datumsökning MySQL

    Jag håller på med ett databasbygge för webben och jobbar i PHP och MySQL.

    Jag har ställts inför ett litet problem som jag har svårt att komma runt med mina kraftigt begränsade programmeringskunskaper.

    Databasen är uppbyggd av ett stort antal tabeller som motsvarare en avskriven volym och typ av längd. Av dessa skapar jag sedan anpassade vyer (views) för att applicera mina queries på.

    Nu håller jag på och gör sökfunktion för data hämtade ur födelseböcker. Problemet jag har hänger ihop med att dessa födslar kan innehålla två olika datum, födelsedatum och dopdatum. Ibland är det båda, ibland endera och ibland inget.

    Jag försöker nu skapa en datumsortering av dessa poster, vilket inte är lätt.

    Skulle jag välja att sortera på endera av datumkolumnerna kommer alla som inte har ett angivet datum i den kolumnen att sorteras först eftersom de får datumet 0000-00-00. Därför har jag försökt skapa en ny, tredje datumkolumn, enbart som sorteringshjälp. Tanken är att först och främst skall sorteringskolumnen i viewn innehålla födelsedatum om det finns, sen dopdatum och slutligen 0000-00-00 om inget av dem finns.

    Till detta har jag försökt använda COALESCE vilket returnerar det första värdet som inte är NULL:

    Kod:
    COALESCE(FDatum, DDatum, "0000-00-00") AS Date
    Men det fungerar inte eftersom "0000-00-00" inte tolkas som ett datum och därför ändras typen på fältet från DATE till någon variant av CHAR. Då tappar jag funktionaliteten av datumtypen. Detta skulle man väl kunna komma runt genom att skapa en dummy-kolumn i varje tabell där alla poster får datumet 0000-00-00 och byta ut det i koden ovan, men det känns som en konstig omväg!?

    Ett annat problem är att för att kunna använda COALESCE enligt ovan måste jag ersätta alla tomma rutor i databasen med NULL, vilket får till följd att när jag försöker skapa en sql-fråga i PHP så hittar sökningen inte någon post som har ett NULL i endera datumkolumnen.

    Jag tycker att sqlfrågan borde vara utformad typ så här:

    Kod:
    $sql = mysql_query("SELECT * FROM vy WHERE 
         FDatum LIKE '$FDatum%'
         AND DDatum LIKE '$DDatum%'
         AND X LIKE '$X%'
         AND Y LIKE '$Y%'
         AND Z LIKE '$Z%'		
         o. s. v.
    Men det är något som inte fungerar med datumen?

    Hur löser jag den här problematiken?

    Ska man använda CASE i s f COALESCE och hur går det till?

    All hjälp mottages tacksamt!

    MVH

    Jonas
    Jonas Magnusson
    www.cognatus.se


  2. #2

    Tommy Petersson
    Medlemsnr
    49794
    Inlägg
    245
    Bara några funderingar:

    Kanske det inte går att använda år 0, pröva 1 jan år 1 istället.

    Istället för att i Coalesce ha tredje alternativet som en sträng, använd den via en CAST/CONVERT till Date.

  3. #3
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Enligt manualen till MySQL 5.6 och DATE ... "The supported range is '1000-01-01' to '9999-12-31'. "

  4. #4

    Tommy Petersson
    Medlemsnr
    49794
    Inlägg
    245
    Det var ju lite ynkligt för vikingsläktforskning... ;-)

  5. #5
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Är inte säker på vad du vill åstadkomma, men du kanske kan ha nytta av IF* i din SELECT-sats om du vill sortera på FDatum eller DDatum.

    * eller CASE

  6. #6
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Ett försök att visualisera det jag vill åstadkomma.

    Jag har flera tabeller:

    Tabell 1

    Kod:
    FDATUM---------DDATUM---------NAMN
    1650-02-02-----1650-02-05-----Jon
    ---------------1650-03-01-----Brita
    1650-04-12-----1650-04-14-----Olof
    1650-05-11--------------------Erik
    Tabell 2

    Kod:
    FDATUM---------DDATUM---------NAMN
    ------------------------------Lisken
    1650-02-01-----1650-02-04-----Hans
    ---------------1650-04-02-----Lars
    1650-05-12-----1650-05-14-----Karl
    Det jag vill åstadkomma är en vy som har ytterligare en datumkolumn som jag kan sortera på:

    Vy
    Kod:
    SDATUM---------FDATUM---------DDATUM---------NAMN
    1000-01-01-----------------------------------Lisken
    1650-02-01-----1650-02-01-----1650-02-04-----Hans
    1650-02-02-----1650-02-02-----1650-02-05-----Jon
    1650-03-01--------------------1650-03-01-----Brita
    1650-04-02--------------------1650-04-02-----Lars
    1650-04-12-----1650-04-12-----1650-04-14-----Olof
    1650-05-11-----1650-05-11--------------------Erik
    1650-05-12-----1650-05-12-----1650-05-14-----Karl

    Jag har som jag nämnde här ovan testat detta, men det blir inte bra:
    Kod:
    CREATE 
    SQL SECURITY INVOKER VIEW vy
    AS
    SELECT COALESCE(FDatum, DDatum,"1000-01-01") AS SDatum, FDatum, DDatum
    FROM Tabell1
    UNION
    SELECT COALESCE(FDatum, DDatum,"1000-01-01") AS SDatum, FDatum, DDatum
    FROM Tabell2

    Jag har testat lite nu med CAST/CONVERT och även STR_TO_DATE, men jag lyckas inte skapa någon vy. Jag har säkert fel i syntaxen så ett konkret exempel vore guld värt!

    Mvh Jonas
    Jonas Magnusson
    www.cognatus.se


  7. #7
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Undrar om nåt sånt här kan fungera ... Orkar inte testa

    Kod:
    SELECT IF(FDatum<>NULL,FDatum,IF(DDatum<>NULL,DDatum,'1000-01-01')) as sdatum, fdatum, ddatum, namn
    FROM tabell
    ORDER BY sdatum, namn
    Om tomma datumfält är NULL, annars får du jämföra med default-värdet.
    Senast redigerat av Phryxe den 2013-03-13 klockan 17:58.

  8. #8

    Tommy Petersson
    Medlemsnr
    49794
    Inlägg
    245
    För att få fram SDatum blir det ju något typ:

    SELECT COALESCE(FDatum, DDatum,Convert(date,"1000-01-01")) AS SDatum
    FROM Tabell1

    Vill du också ha DDatum i samma SELECT lär det väl bli:
    SELECT COALESCE(FDatum, DDatum,Convert(date,"1000-01-01")) AS SDatum, COALESCE(DDatum,Convert(date,"1000-01-01")) AS DDatum
    FROM Tabell1

  9. #9
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Citat Ursprungligen postat av Phryxe Visa inlägg
    Undrar om nåt sånt här kan fungera ... Orkar inte testa

    Kod:
    SELECT IF(FDatum<>NULL,FDatum,IF(DDatum<>NULL,DDatum,'1000-01-01')) as sdatum, fdatum, ddatum, namn
    FROM tabell
    ORDER BY sdatum, namn
    Om tomma datumfält är NULL, annars får du jämföra med default-värdet.

    Tack för försöket men resultatet blir detsamma som när jag använder COALESCE.

    Poster där endera kolumnen innehåller NULL bli sorteringsdatumet 1000-01-01 oavsett om den andra kolumnen innehåller ett datum. Samt att den nya kolumnen SDatum blir av typen VARBINARY, vilket jag antar beror att "1000-01-01" är en textsträng.
    Jonas Magnusson
    www.cognatus.se


  10. #10
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Citat Ursprungligen postat av tommypeters Visa inlägg
    För att få fram SDatum blir det ju något typ:

    SELECT COALESCE(FDatum, DDatum,Convert(date,"1000-01-01")) AS SDatum
    FROM Tabell1
    MySQL köper inte den konverteringen, fel syntax #1064.
    Jonas Magnusson
    www.cognatus.se


  11. #11
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    "Om tomma datumfält är NULL, annars får du jämföra med default-värdet.".

    Defaultvärdet tycks vara '0000-00-00', inte NULL. Om du byter ut NULL mot '0000-00-00' i IF-satsen bör det funka.

  12. #12
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Citat Ursprungligen postat av Phryxe Visa inlägg
    "Om tomma datumfält är NULL, annars får du jämföra med default-värdet.".

    Defaultvärdet tycks vara '0000-00-00', inte NULL. Om du byter ut NULL mot '0000-00-00' i IF-satsen bör det funka.
    Tack, det funkar, men det måste innebära att om ett datumvärde i originaltabellen är satt till NULL, vilket alla som saknar värde är i mitt fall, så hanteras det som '0000-00-00'?

    Då är det bara problemet att den skapade sorteringskolumnen blir varbinary kvar?
    Jonas Magnusson
    www.cognatus.se


  13. #13
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Jag testade att skapa en minimal tabell och om jag inte definierade ett standard-värde i ett DATE-fält så blev standardvärdet '0000-00-00'. Man kan definiera andra standardvärden, ex. NULL eller '1000-01-01' om man vill.

  14. #14
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Jag hade definierat standardvärdet till NULL i mina tabeller, men det hade uppenbarligen ingen funktion, för då borde ju din första variant ha funkat kan jag tycka!

    Men skitsamma, nu funkar det ju...
    Jonas Magnusson
    www.cognatus.se


  15. #15
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Kanske så här ...

    Kod:
    SELECT CONVERT(IF(FDatum<>'0000-00-00',FDatum,IF(DDatum<>'0000-00-00',DDatum,'1000-01-01')),DATE) as sdatum, fdatum, ddatum, namn
    FROM tabell
    ORDER BY sdatum, namn

  16. #16
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Nope
    Jonas Magnusson
    www.cognatus.se


  17. #17
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Är det fortfarande varbinary?

    Otillåtna värden tycks generera '0000-00-00' ...

  18. #18
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Citat Ursprungligen postat av Phryxe Visa inlägg
    Är det fortfarande varbinary?

    Otillåtna värden tycks generera '0000-00-00' ...
    Det senaste försöket blir en #1064 fel syntax.

    Utan någon CONVERT blir sökkolumnen varbinary.
    Jonas Magnusson
    www.cognatus.se


  19. #19
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Här beskrivs något liknande och han använder CAST.

    http://phe1129.wordpress.com/2011/01...umn-data-type/

    Men tyvärr inget exempel med datatypen DATE.
    Jonas Magnusson
    www.cognatus.se


  20. #20
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Jag har gjort en temporär lösning genom att lägga till en dummykolumn i originaltabellerna så att jag kan använda den i s f för textsträngen som krånglar till det:

    Kod:
    SELECT IF(FDatum<>'0000-00-00',FDatum,IF(DDatum<>'0000-00-00',DDatum,Dummy)) as sdatum, fdatum, ddatum, namn
    Det känns ju inte riktigt rätt, men det funkar!

    Kommer någon på hur man löser problemet är jag givetvis tacksam!
    Jonas Magnusson
    www.cognatus.se


  21. #21
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Fick inget syntax-fel när jag körde det på MySQL (hos one.com). Vet inte vilken version de använder.

  22. #22
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Gjorde du det i en CREATE VIEW?
    Jonas Magnusson
    www.cognatus.se


  23. #23
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Först bara en select-sats, men testade även att skapa vyer och det gick utan problem. Data-typen blev dessutom rätt.

  24. #24
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Vill du får du gärna posta koden för viewn så kanske jag kan förstå vad jag gör fel?
    Jonas Magnusson
    www.cognatus.se


  25. #25
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Använde phpMyAdmin. Minns nu inte vad jag skrev, men eftersom jag även testade med den inbyggda versionen kan du ju testa den (om du har phpMyAdmin). När man skrivit en SELECT-fråga kan man göra om den till en vy (med en knapp nere till höger, skapa vy).

  26. #26
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Lånade din CREATE-kod och den fungerar ...
    Kod:
    CREATE SQL SECURITY INVOKER VIEW xxx__vy0
    AS
    SELECT CONVERT(IF(FDatum<>'0000-00-00',FDatum,IF(DDatum<>'0000-00-00',DDatum,'1000-01-01')),DATE) as sdatum, fdatum, ddatum, namn
    FROM xxx__tab
    ORDER BY sdatum, namn

  27. #27
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Ja, jag får det att fungera nu också, måste ha klämt in en parentes för mycket??? Stort tack C-G!

    Är du intresserad kan jag PM'a adressen till det pågående projektet. Alltid intressant med kunniga människors synpunkter!
    Jonas Magnusson
    www.cognatus.se


  28. #28
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Det är alltid intressant att se vad som händer i släktforskarvärlden så du kan gärna skicka adressen ...

  29. #29
    Cognatuss avatar
    Jonas Magnusson
    Medlemsnr
    40573
    Ort
    Vänge, Uppsala
    Inlägg
    27
    Nästa sak, och förhoppningsvis den sista, att ta sig förbi är hur jag skall formulera en SELECT-fråga som omfattar att söka på antingen enbart ett datum, alternativt ett intervall.

    Mitt sökformulär för födda kan ni se i bifogad fil.

    SQl-frågan som den ser ut nu, med enbart sökning på ett datum tillsammans med andra söktermer i textformat:


    Kod:
    $sql = mysql_query("SELECT * FROM fvy WHERE 
               (Datum LIKE '$SDatum%' OR FDatum LIKE '$SDatum%') 
               AND BFnamn LIKE '$BFnamn%'											
    	   AND FFnamn LIKE '$FFnamn%'
    	   AND FEnamn LIKE '$FEnamn%'		
    	   AND FYrke LIKE '$FYrke%'											
               AND MOFnamn LIKE '$MOFnamn%'	
               AND MOEnamn LIKE '$MOEnamn%'	
               AND MOYrke LIKE '$MOYrke%'	
               AND OrtN LIKE '$OrtN%'	
               AND Anm LIKE '$Anm%'
    	   ORDER BY SDatum, Kalla, Id");
    Hur utökar jag denna query att omfatta intervallet "Mellan åren" (se bild), som har fått variabelnamnen $SDatummin och $SDatummax.

    Jag vill alltså att queryn skall gälla antingen $SDatum eller intervallet $SDatummin till $SDatummax, där båda alternativen kombineras med övriga söktermer.

    Hoppas det gick att förstå den frågan?

    Mvh
    Bifogade bilder Bifogade bilder
    Jonas Magnusson
    www.cognatus.se


  30. #30
    Phryxes avatar
    C-G Magnusson
    Medlemsnr
    13088
    Inlägg
    1 039
    Citat Ursprungligen postat av Cognatus Visa inlägg
    Jag vill alltså att queryn skall gälla antingen $SDatum eller intervallet $SDatummin till $SDatummax, där båda alternativen kombineras med övriga söktermer.
    Jag undrar om alla kan innehålla ett riktigt datum eller '0000-00-00'!? Hur ska man då tolka öppna intervall, dvs de utan min- eller max-värde? När det gällde födelse- och dop-datum prioriterade du födelse. Hur blir det i detta fall? Undrar hur det går att testa om ett datum är i ett intervall när du använder LIKE ... Varför använder du förresten LIKE på datumfältet?

Sidan 1 av 2 1 2 SistaSista

Behörigheter för att posta

  • Du får inte posta nya ämnen
  • Du får inte posta svar
  • Du får inte posta bifogade filer
  • Du får inte redigera dina inlägg
  •