[PHP/SQL] Berichten Laatste 7 dagen

[Crazy-]

heb een db waar heel wat berichten instaan met Unix time

nu wil ik het aantal berichten per dag van de afgleopen 7 dagen zien

nu heb ik het volgende:

code:

<?php
$query = "SELECT 
     count(*) as aantal, 
            ((TO_DAYS(datum)-TO_DAYS(NOW()))*-1) as dagen_geleden
        FROM 
            perso
        WHERE 
            TO_DAYS(NOW()) - TO_DAYS(datum) <= 7 
        GROUP BY
            TO_DAYS(datum)
        ORDER BY 
            dagen_geleden ASC
        "; 
    if ($result = mysql_query($query)) { 
        if (mysql_num_rows($result) > 0) {
            while ($row = mysql_fetch_assoc($result)) { 
                echo $row['dagen_geleden']." dagen geleden was het aantal ".$row['aantal']."<br>\n"; 
            }
        } else {
            echo 'geen resultaten';
        }
    } else {
        echo $query ."<br>\nblijkt toch niet goed te zijn:<br>\n".mysql_error();
    }
?>

maar aangezien het unix time in de DB staat, werkt het niet
hoe kan ik dit oplossen/

ok, dmv FROM_UNIXTIME & UNIX_TIMESTAMP maar ik kom er niet echt uit
iemand een idee?

[RedRose]

Een week geleden is 604800 seconden terug vanaf nu (UNIX_TIMESTAMP()
) gerekend in Unix tijd (het aantal seconden sinds 1-1-1970)....

Maar je weet zeker dat het een Unix-timestamp is, en geen MySQL TIMESTAMP(14) of zo?

[Crazy-]

heb alles zelf gemaakt weet zelf wel wat ik maak he? hehe

maar dus, is 100% zeker unix time date("U"); dus...
heb al van alles geprobeerd, maar het lukt me gewoon bet

[RedRose]

CIOMrCrazy schreef op 04 July 2003 @ 03:38:
heb alles zelf gemaakt weet zelf wel wat ik maak he? hehe

maar dus, is 100% zeker unix time date("U"); dus...
heb al van alles geprobeerd, maar het lukt me gewoon bet

Ok, je weet nooit.

Wat heb je al geprobeerd dan? Ook nog even gekeken naar mijn hint, die dan wel zou moeten werken?

Waarom gebruik je trouwens niet gewoon de date/time column types van MySQL om dit soort dingen te doen, als je toch alles zelf maakt? Juist voor dit soort dingen zijn die column types gemaakt.

[Crazy-]

ja k dat is waar, maar ik prog al heel lang en geburik eigelijk altijd unix time, nu merk ik dus wel dat het niet de beste manier is.....

maar aangezien het iets vna 5000 records zijn........ die ik dan zou moeten wijzigen

ik zal ff proberen

[Crazy-]

heb beetje zitten proberen

$query = "SELECT
count(*) as aantal,
((TO_DAYS(datum)-TO_DAYS(UNIX_TIMESTAMP()))*-1) as dagen_geleden
FROM
perso
WHERE
TO_DAYS(UNIX_TIMESTAMP()) - TO_DAYS(datum) <= 7
GROUP BY
TO_DAYS(datum)
ORDER BY
dagen_geleden ASC
";

wil ook niet werken en als ik ipv UNIX_TIMESTAMP() UNIX_TIMESTAMP(now()) doe werkt het ook niet

[RedRose]

CIOMrCrazy schreef op 04 July 2003 @ 03:54:

code:

WHERE 
TO_DAYS(UNIX_TIMESTAMP()) - TO_DAYS(datum) <= 7

Ok, je hebt Unix timestamps in je db staan. Dan weet je dat TO_DAYS() niet zomaar gaat werken.

Een voorbeeld en dan zou je er uit moeten komen:

Nu in Unix time stamp is: 1057284086 en daar wil je wat vanaf trekken en dan checken of het verschil meer of minder dan 7 dagen is.
Eerdere datum (5 dagen geleden): 1056852086 en 7 dagen geleden is: 604800, dus onderstaande vergelijking is waar:

1057284086-1056852086 <= 604800

_{.edit: dus verder hoef je helemaal niks te doen met TO_DAYS oid}

[ Voor 7% gewijzigd door RedRose op 04-07-2003 04:10 ]

[Crazy-]

ik heb een begin

code:

$qry = "SELECT 
        count(*) as aantal,
             ((datum)-FROM_UNIXTIME(datum, '%W %e %M %Y %k:%i')*-1) as dagen_geleden
        FROM 
            perso 
        WHERE 
            FROM_UNIXTIME(datum, '%W %e %M %Y %k:%i') - FROM_UNIXTIME(UNIX_TIMESTAMP()) <= 604800
        GROUP BY
            datum
        ORDER BY
            dagen_geleden ASC
        ";

alleen nu krijg ik wel netjes de datum van tot 7 dagen terug, maar ik krijg bij vandaag 2 (wat al niet klopt) en bij de rest van de tijden allemaal 1...

wat doe ik fout ?

//edit: output:

1056684390 dagen geleden was het aantal 2
1056727590 dagen geleden was het aantal 1
1056727711 dagen geleden was het aantal 1
1056729740 dagen geleden was het aantal 1
1056729965 dagen geleden was het aantal 1
1056730363 dagen geleden was het aantal 1
1056731741 dagen geleden was het aantal 1
1056731998 dagen geleden was het aantal 1
1056732888 dagen geleden was het aantal 1
1056733381 dagen geleden was het aantal 1
1056739580 dagen geleden was het aantal 1
1056739881 dagen geleden was het aantal 1
1056740129 dagen geleden was het aantal 1
1056742023 dagen geleden was het aantal 1
1056743446 dagen geleden was het aantal 1
1056745722 dagen geleden was het aantal 1
1056794749 dagen geleden was het aantal 1
1056799732 dagen geleden was het aantal 1
1056800445 dagen geleden was het aantal 1
1056825850 dagen geleden was het aantal 1
1056845233 dagen geleden was het aantal 1
t/m
1057279589 dagen geleden was het aantal 1 (laatste van vandaag dus)

[ Voor 51% gewijzigd door Crazy- op 04-07-2003 04:27 ]

[RedRose]

CIOMrCrazy schreef op 04 juli 2003 @ 04:24:
ik heb een begin
<knip>
wat doe ik fout ?
<knip>

Je kijkt niet goed naar wat je aan het doen bent.

• Waarom trek je bij beide datumberekeningen de eerdere datum af van de huidige datum? En niet andersom?
• Je bent in seconden aan het rekenen nu. Daar moet je rekening mee houden. (60*60*24 = 1 dag)
• En:

code:

FROM_UNIXTIME(datum, '%W %e %M %Y %k:%i')

is ook niet nodig.

[Crazy-]

hm, ik vat het niet helemaal....

code:

$qry = "SELECT 
        count(*) as aantal,
             (FROM_UNIXTIME(datum)-(datum)*-1) as dagen_geleden
        FROM 
            perso 
        WHERE 
            FROM_UNIXTIME(datum) - FROM_UNIXTIME(UNIX_TIMESTAMP()) <= 604800
        GROUP BY
            datum
        ORDER BY
            dagen_geleden DESC
        ";

dat heb ik nu, nu is de output:

20031761304218 dagen geleden was het aantal 1
20031761303542 dagen geleden was het aantal 1
etc

ik vat het neit 100% meer....

[RedRose]

Okay, laatste hint: kijk nog even wat FROM_UNIXTIME(unix_timestamp [,format]) nou eigenlijk doet. Staat ergens onderaan. En dan zie je het vanzelf

_{.edit: dus of je converteert naar MySQL timestamps, of niet, maar nu haal je ze steeds door elkaar}

[ Voor 21% gewijzigd door RedRose op 04-07-2003 04:45 ]

[Crazy-]

hm, k, heb al wat iets maar volgens mij werkt de group by niet helemaal:

code:

$qry = "SELECT 
        count(datum) as aantal,
             (FROM_UNIXTIME(UNIX_TIMESTAMP()) - FROM_UNIXTIME(datum)) as dagen_geleden
        FROM 
            perso 
        WHERE 
            FROM_UNIXTIME(datum) - FROM_UNIXTIME(UNIX_TIMESTAMP()) <= 604800
        GROUP BY
            dagen_geleden
        ORDER BY
            dagen_geleden DESC
        ";

stukje output met een unix time -> datum functie:

Zaterdag 10 Juni 1972 06:29 891 dagen geleden was het aantal 2
Donderdag 8 Juni 1972 21:09 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 21:06 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 19:03 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 18:56 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 18:45 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 18:07 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 18:00 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 17:35 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 16:15 890 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 12:16 889 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 12:08 889 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 12:01 889 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 10:01 889 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 09:22 889 dagen geleden was het aantal 1
Donderdag 8 Juni 1972 07:12 889 dagen geleden was het aantal 1

maw, hij grouped de dagen niet...
al die 8 juni zou bij elkaar gedaan moeten worden, maar gebeurt niet.

[drm]

Waarom doe je eigenlijk zo moeilijk met die unix timestamps? Waarom gebruik je niet gewoon de MySQL functies voor het rekenen met data?

zie hier

[Bosmonster]

Of waarom bereken je de 7 dagen geleden niet (eenmalig) met PHP (of welke taal je ook gebruikt) en gebruik die in de QUERY. Das een stuk sneller in MySQL dan dingen gaan WHERE'en op dit soort dingen. Dan moet ie namelijk voor iedere record die berekening gaan uitvoeren, wat vervelend lang kan gaan duren als er veel records in je tabel zitten Bovendien kan MySQL geen indices gebruiken op dit soort WHERE-clausules.

Aangezien je toch al UNIX TIMESTAMPS gebruikt is dat imho de makkelijkste EN snelste oplossing.

PHP:

$lastdate = mktime (0,0,1,date('n'),date('j')-7,date('Y'));

//of
$lastdate = time() - (7*24*60*60);

$query = "SELECT 
     count(ID) as aantal
        FROM 
            perso
        WHERE 
            datum > ".$lastdate." 
        ORDER BY 
            datum DESC
        ";

oid.. COUNT(ID) oid is overigens ook sneller dan een COUNT(*).

[ Voor 76% gewijzigd door Bosmonster op 04-07-2003 09:31 ]

[Crazy-]

drm schreef op 04 July 2003 @ 09:11:
Waarom doe je eigenlijk zo moeilijk met die unix timestamps? Waarom gebruik je niet gewoon de MySQL functies voor het rekenen met data?

zie hier

dit ivm ik dan +/-5000 records moet aanpassen waar 2x een X datum in staat... in unix timestamp... dat is beetje de reden

[LeonT]

CIOMrCrazy schreef op 04 juli 2003 @ 10:17:
[...]
dit ivm ik dan +/-5000 records moet aanpassen waar 2x een X datum in staat... in unix timestamp... dat is beetje de reden

Daar zijn natuurlijk scriptjes voor...
En als je geen zin hebt om die zelf te maken staat hier ook wel wat tussen heb ik gezien.

[ripexx]

CIOMrCrazy schreef op 04 juli 2003 @ 10:17:
[...]
dit ivm ik dan +/-5000 records moet aanpassen waar 2x een X datum in staat... in unix timestamp... dat is beetje de reden

Met een simpel update scriptje hoef je maar een keer de query te bedenken en dan ben je klaar. Het zou wat anders zijn als je 5000 tabellen moest aanpassen

[Crazy-]

hmz true..... ff niet aangedacht *sorry, hele nacht door gewerkt....beetje duf dus*

ik ga maar is query bouwen en wijzigen en dan de velden omzetten, thnx rest zal wel lukken

//close

[Bosmonster]

Wat ik zei wordt genegeerd? Want daar komen je timestamps ook mooi van pas

[Crazy-]

tis finally opgelost....

code:

$qry = "SELECT 
    count(datum) as aantal,
     (TO_DAYS(FROM_UNIXTIME(UNIX_TIMESTAMP())) - TO_DAYS(FROM_UNIXTIME(datum))) as dagen_geleden
    FROM 
    perso 
                WHERE 
    FROM_UNIXTIME(datum) - FROM_UNIXTIME(UNIX_TIMESTAMP()) <= 604800
    GROUP BY
    dagen_geleden
    ORDER BY
    dagen_geleden ASC
";

$datum = date("U") - (60*60*24*$row['dagen_geleden']);

output:

Op 04 Juli 2003 zijn er - 3 - berichten geplaatst!

[ Voor 9% gewijzigd door Crazy- op 04-07-2003 11:04 ]

[drm]

Doe jezelf nou een plezier en verander het columntype van `datum` even in een TIMESTAMP (mysql) of DATETIME type

die queries met al die FROM_UNIXTIME / UNIX_TIMESTAMP - conversie-zut erin worden er echt niet overzichtelijker op.

Verder ben je vergeten dat er nog een DATE_SUB functie bestaat.

edit:
idd Bosmonster, ik negeer je gewoon straal

[ Voor 9% gewijzigd door drm op 04-07-2003 11:12 ]

[Crazy-]

ja zal ik zeker doen, maar ik kon er niet tegen dat het niet werkte.....:) en om alles om te zetten -> doe ik zeker wel, maar niet meteen *gaat proberen zo snel mogelijk slaap te vinden* *Gaap*