last 7 days stats

[Leverkusen]

a part of a code where is showind me the yesterday's results, how i can change that to last 7 days results?

if($_GET['id']==""){
	$result = mysql_query("select *,
		(select count(*) from clanovi_njihovi_parovi where status = 1 and racun=r.id and datum=curdate()-1) pog_pre,
		(select count(*) from clanovi_njihovi_parovi where status = 0 and racun=r.id and datum=curdate()-1) 
promaseno_pre,
		(select avg(kvota) from clanovi_njihovi_parovi where status =0 and racun=r.id) prosjek_l
		from clanovi_njihovi_racuni r order by profit desc,id");

[trq]

Take a look at this part "curdate()-1", and then have a guess.

[Leverkusen]

if i put -7 it will not be from all 7 days, only from that 7th day

[maxxd]

You could use the php DateTime object and specify a BETWEEN clause with a range of today and seven days ago on the datum column.

[Leverkusen]

how would it look like?

[maxxd]

$dt = new DateTime('now');
$qry = "select count(*) from clanovi_njihovi_parovi where status = 1 and racun=r.id
and datum BETWEEN '{$dt->format('Y-m-d')}' AND '{$dt->sub(DateInterval::createFromDateString('7 days'))->format('Y-m-d')}'";

That's off the top of my head, but should get you started - the php DateTime() docs are comprehensive. Also, I know the statement will parse in SQL Server, but unfortunately I don't use MySQL at work so the syntax might be slightly different.

[Barand]

... WHERE datum BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY ...

[Leverkusen]

thats the thing i needed thanx alot