Tijden veranderen
db datum probleem
Ik ben al een tijdje bezig met het zichtbaar krijgen van de items van de maand. Het is dus de bedoeling dat ik een lijstje krijg met de best bekeken items van de maand, mocht het de eerste van de maand zijn en nog niet genoeg items in de best bekeken hebben dan zou hij naar de vorige maand moeten kijken.
Hieronder heb ik de code staan die ik tot nu toe heb. Sorry dat het er niet meer netjes uitziet, ik heb er al zoveel mee geprobeert dat ik er zelf niet meer uitkom.
De eerste query is om te kijken of er voldoende items al bekeken zijn, mocht dit niet het geval zijn moet hij $maand - 1 doen, dus naar de vorige maand.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?
$timestamp = strtotime("now");
//$maand = date("m", $timestamp);
$maand = 7;
if ($maand < 10) { $maand = "0$maand"; }
$sqlm = "SELECT distinct id, count(*) as cnt FROM itemcount WHERE date LIKE '%-".$maand."-%' GROUP BY id ORDER BY cnt DESC, id LIMIT 0,$limit";
$resm = mysql_query($sqlm) or die("SELECT Error: ".mysql_error());
$items = mysql_num_rows($resm);
echo $items;
echo " ";
if ($items < 10) { $maan = --$maand; } else { $maan = $maand; }
if ($maan < 10) { $maan = "0$maan"; }
$sqlc = "SELECT distinct id, item, timestamp, count(*) as cnt FROM itemcount WHERE date LIKE '%-".$maan."-%' GROUP BY id ORDER BY cnt DESC, id LIMIT 0,$limit";
$resc = mysql_query($sqlc) or die("SELECT Error: ".mysql_error());
$item = mysql_num_rows($resc);
echo $item;
echo " ";
?>
$timestamp = strtotime("now");
//$maand = date("m", $timestamp);
$maand = 7;
if ($maand < 10) { $maand = "0$maand"; }
$sqlm = "SELECT distinct id, count(*) as cnt FROM itemcount WHERE date LIKE '%-".$maand."-%' GROUP BY id ORDER BY cnt DESC, id LIMIT 0,$limit";
$resm = mysql_query($sqlm) or die("SELECT Error: ".mysql_error());
$items = mysql_num_rows($resm);
echo $items;
echo " ";
if ($items < 10) { $maan = --$maand; } else { $maan = $maand; }
if ($maan < 10) { $maan = "0$maan"; }
$sqlc = "SELECT distinct id, item, timestamp, count(*) as cnt FROM itemcount WHERE date LIKE '%-".$maan."-%' GROUP BY id ORDER BY cnt DESC, id LIMIT 0,$limit";
$resc = mysql_query($sqlc) or die("SELECT Error: ".mysql_error());
$item = mysql_num_rows($resc);
echo $item;
echo " ";
?>
Ik hoop dat ik het een beetje begrijpend heb kunnen uitleggen.
Alvast bedankt voor jullie tijd en moeite..
Gewijzigd op 01/01/1970 01:00:00 door Rico
Er valt nog wel wat op te merken aan je code. Bijvoorbeeld het toepassen van voorloopnullen, daar zou je heel mooi de functie sprintf() voor kunnen gebruiken:
En gebruik in je query geen LIKE als het om data gaat. Aangezien een datum in een DATE of DATETIME veld staat en je precies weet waarop je wilt vergelijken, kun je dat met de datum/tijd functies van mysql voor elkaar krijgen.
Code (php)
1
2
3
2
3
SELECT iets
FROM tabel
WHERE MONTH(datum) = 10
FROM tabel
WHERE MONTH(datum) = 10
Die 10 is natuurlijk een voorbeeldje, daar moet je zelf nog iets voor invullen, direct dan wel met een variabele.
Ik zie dat je een timestamp gebruikt. Waarvoor als ik vragen mag?
Wat ik niet snap is dat de 2 queries eigelijk hetzelfde zijn, maar de eerste geeft wel resultaat en de 2de niet, als de 2de ook gewoon resultaat gaf dan was het probleem opgelost. :)
En het gebruik van de timestamp en datum is omdat ik voor sommige dingen wel met timestamp kan/moet werken en met andere dingen zoals dit kan ik het dus niet. :( ik kan redelijk uit de voeten met php en sql, maar zo goed ben ik er nog niet in. :) Ik hoop dat ik nog ergens een keer een goed boek tegenkom waar alles netjes instaat. :)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
$sqlm = "
SELECT
id,
count(id) as cnt
FROM
itemcount
WHERE
CASE
WHEN DAYOFMONTH(CURDATE()) = 1 THEN MONTH(date) = MONTH(DATE_ADD(CURDATE() INTERVAL -1 MONTH)
ELSE MONTH(date) = MONTH(CURDATE())
END
GROUP BY id
ORDER BY
cnt DESC,
id ASC
LIMIT 0, $limit";
?>
$sqlm = "
SELECT
id,
count(id) as cnt
FROM
itemcount
WHERE
CASE
WHEN DAYOFMONTH(CURDATE()) = 1 THEN MONTH(date) = MONTH(DATE_ADD(CURDATE() INTERVAL -1 MONTH)
ELSE MONTH(date) = MONTH(CURDATE())
END
GROUP BY id
ORDER BY
cnt DESC,
id ASC
LIMIT 0, $limit";
?>
Nu wordt gekeken of het dagnummer 1 is, dit kun je natuurlijk nog veranderen in bv. < 3 wanneer je pas vanaf dag 3 de huidige maand wilt gaan tonen. Een timestamp heb je echt nooit nodig in de database, gebruik deze dan ook vooral niet. Het kan alleen maar problemen opleveren.
(niet getest, kan nog een foutje in zitten, maar in grote lijnen klopt het wel)
Bedankt voor je reactie. Ik wist nog niet dat je dit ook volledig in de query kon verwerken.
Ik ga er een mee worstelen.
Bedankt!!!!
Ik heb er nu een tijdje mee lopen stoeien, maar kom er niet uit :( Hij geeft als error " SELECT Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') INTERVAL -1 MONTH) ELSE MONTH(date) = MONTH(CURDATE()) END C " dit aan. Ik heb al verschillende dingen gedaan maar blijf deze error houden. :( Ik heb nog nooit met case gewerkt dus het is een beetje vreemd voor mij, ik heb al wel op de mysql.com site gekeken maar ook niks wijzer geworden.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
$sqlm = "
SELECT
id,
count(id) as cnt
FROM
itemcount
WHERE
CASE
WHEN DAYOFMONTH(CURDATE()) = 1 THEN MONTH(date) = MONTH(DATE_ADD(CURDATE()) INTERVAL -1 MONTH)
ELSE MONTH(date) = MONTH(CURDATE())
END CASE
GROUP BY id
ORDER BY
cnt DESC,
id ASC
LIMIT 0, $limit";
?>
$sqlm = "
SELECT
id,
count(id) as cnt
FROM
itemcount
WHERE
CASE
WHEN DAYOFMONTH(CURDATE()) = 1 THEN MONTH(date) = MONTH(DATE_ADD(CURDATE()) INTERVAL -1 MONTH)
ELSE MONTH(date) = MONTH(CURDATE())
END CASE
GROUP BY id
ORDER BY
cnt DESC,
id ASC
LIMIT 0, $limit";
?>
Quote:
MONTH(DATE_ADD(CURDATE()) INTERVAL -1 MONTH)
moet zijn:
MONTH(DATE_ADD(CURDATE(), INTERVAL -1 MONTH))
Gewijzigd op 01/01/1970 01:00:00 door Jan Koehoorn
Heb ik ookal geprobeert, maar krijg dan dezelfde error :(
Sorry had de komma niet gezien. :S maar nu werkt hij zover ik lokaal kan zien, nu nog even online testen met de up to date db...
Bedankt Frank en Jan.
En sorry dat ik het niet in de handleiding kon vinden. :/
Gewijzigd op 01/01/1970 01:00:00 door Rico
Rico, let voortaan heel goed op de foutmelding die MySQL geeft. Deze geeft aan dat het bij INTERVAL fout gaat. Dan weet je dat de fout daar vlak voor moet zitten, MySQL komt vanwege een fout namelijk niet meer toe aan INTERVAL en de rest van de query. In dit geval omdat MySQL een komma verwacht.
De fout had dus helemaal niets met de CASE te maken, maar met de functie DATE_ADD() die niet goed wordt gebruikt. Dit had je vrij eenvoudig in de MySQL-handleiding kunnen vinden, zie hoofdstuk 12.5.
Succes!
De datum word opgeslagen in de table date in formaat DD-MM-YYYY. Ik heb het al geprobeert met voor de case " date = " te zetten maar dat werkt niet, krijg ik nog steeds niks, ondanks er ruim 2000 entries staan.
Gewijzigd op 01/01/1970 01:00:00 door Rico
1) de tabelnaam 'date' is een slecht idee i.v.m. reserved words.
2) het formaat DD-MM-YYYY, daar kan MySQL niets mee. Een veld type DATE hoort YYYY-MM-DD te zijn.
Rico schreef op 10.11.2006 13:59:
Helemaal correct! Je hebt namelijk onzin in je database gezet!Dus met andere woorden moet ik alle 20.000 entries veranderen voor het kan werken??
Oplossing: Maak even een nieuwe kolom aan, bv. met de naam 'iso_date' (het is tenslotte een iso-formaat). En ga dan de volgende query op jouw tabel loslaten:
Code (php)
1
Edit: deze query mag je snel vergeten!
Deze werkt uitstekend wanneer de boel in de kolom 'date' is opgeslagen in het formaat dd-mm-yyyy
Edit:
Nadat je de kolom 'iso_date' keurig hebt gevuld (en even gecontroleerd of alles goed is) kun je de kolom 'date' verwijderen. Alle queries de gebruik maakten van de kolom 'date' moet je natuurlijk gaan aanpassen, die moeten nu gebruik maken van iso_date waar nu een échte datum in staat.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
van 04-04-2006 maakt hij dan 6002-40-40
:)
Er zijn overigens string-functies die wél doen wat jij wilt, zoek daar maar even naar: http://dev.mysql.com/doc/refman/4.1/en/string-functions.html
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
UPDATE
tabelnaam
SET
iso_date = CONCAT(
SUBSTRING(date, 7, 4),
'-',
SUBSTRING(date, 4, 2),
'-',
SUBSTRING(date, 1, 2)
)
tabelnaam
SET
iso_date = CONCAT(
SUBSTRING(date, 7, 4),
'-',
SUBSTRING(date, 4, 2),
'-',
SUBSTRING(date, 1, 2)
)
Wederom niet getest...
Nu heb ik alles omgezet naar iso_date en date verwijderd, en nu doet hij het dus wel als ik het goed zie. Alleen is er een mogelijkheid dat ik kan kijken naar de vorige maand om te testen dat hij alleen deze maand pakt. ??
In ieder geval heel erg bedankt. :)
Gewijzigd op 01/01/1970 01:00:00 door Rico
WHEN DAYOFMONTH(CURDATE()) = 1
Maak van die 1 even een 10 (het is vandaag de 10e!) en je hoort de gegevens van de vorige maand te krijgen.
En als ik het per week wil laten zien, kan ik dan MONTH vervangen door WEEK of is het niet zo simpel ?? :)