Visa fullständig version : Problematik datumsökning MySQL
Cognatus
2013-03-12, 22:34
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:
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:
$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
tommypeters
2013-03-13, 00:07
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.
Enligt manualen till MySQL 5.6 och DATE ... "The supported range is '1000-01-01' to '9999-12-31'. "
tommypeters
2013-03-13, 11:53
Det var ju lite ynkligt för vikingsläktforskning... ;-)
Är inte säker på vad du vill åstadkomma, men du kanske kan ha nytta av IF* i din SELECT-sats (http://stackoverflow.com/questions/5951157/mysql-if-in-select-statement) om du vill sortera på FDatum eller DDatum.
* eller CASE
Cognatus
2013-03-13, 15:28
Ett försök att visualisera det jag vill åstadkomma.
Jag har flera tabeller:
Tabell 1
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
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
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:
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
Undrar om nåt sånt här kan fungera ... Orkar inte testa :p
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.
tommypeters
2013-03-13, 17:22
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
Cognatus
2013-03-13, 17:30
Undrar om nåt sånt här kan fungera ... Orkar inte testa :p
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.
Cognatus
2013-03-13, 17:40
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.
"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.
Cognatus
2013-03-13, 19:41
"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?
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.
Cognatus
2013-03-13, 20:15
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! :confused:
Men skitsamma, nu funkar det ju...
Kanske så här ...
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
Cognatus
2013-03-13, 20:32
Nope :(
Är det fortfarande varbinary?
Otillåtna värden tycks generera '0000-00-00' ...
Cognatus
2013-03-13, 20:41
Ä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.
Cognatus
2013-03-13, 20:45
Här beskrivs något liknande och han använder CAST.
http://phe1129.wordpress.com/2011/01/17/inconsistency-in-view-column-data-type/
Men tyvärr inget exempel med datatypen DATE.
Cognatus
2013-03-13, 21:29
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:
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! :rolleyes:
Kommer någon på hur man löser problemet är jag givetvis tacksam!
Fick inget syntax-fel när jag körde det på MySQL (hos one.com). Vet inte vilken version de använder.
Cognatus
2013-03-13, 22:19
Gjorde du det i en CREATE VIEW?
Först bara en select-sats, men testade även att skapa vyer och det gick utan problem. Data-typen blev dessutom rätt.
Cognatus
2013-03-14, 08:09
Vill du får du gärna posta koden för viewn så kanske jag kan förstå vad jag gör fel? :o
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).
Lånade din CREATE-kod och den fungerar ...
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
Cognatus
2013-03-14, 10:17
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!
Det är alltid intressant att se vad som händer i släktforskarvärlden så du kan gärna skicka adressen ...
Cognatus
2013-03-14, 20:48
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:
$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
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?
Cognatus
2013-03-15, 10:59
Anledningen till att jag använder LIKE i datumsökningarna är att det skall fungera att bara söka på "1685" i sökfältet. Använder jag "=" så behöver jag ange exakt datum med 10 siffror. Wildcards fungerar inte.
Jag har letat runt lite och vad jag förstår bör man kunna använda något i stil med :
SELECT * FROM fvy WHERE (Datum >= '$SDatummin' AND Datum <= '$SDatummax')
Men jag vill ju bara använda den frågan när båda eller något min-max variablerna är satta och när inte den enkla variabeln $SDdatum används. Om jag använder exempelkoden i ovanstående fall får jag väl försöka göra det tydligt på sidan att dessa två datum måste anges med 10 siffror.
Man validerar oftast det som kommer in i ett formulär. Du kan styra ganska hårt vad användaren får knappa in. Många använder Javascript för inmatningskontroll på klientsidan (innan det skickas till servern). På serversidan kan du också kontrollera det som kommer in i formuläret och trimma/manipulera variablerna innan du skickar dem vidare i en fråga till databasen, eller ställa olika frågor beroende på vad som skrivs i formuläret.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions, Inc. All rights reserved.