Hello,
Trying to build a afffiliate program so i made two tables.
Clicks - with fields id, affiliate_id, ip, date, time
and another tables
Commisions with fields id, affiate_id, ip, date, time, amount
now iam trying to output stats from a given start date to end date( for ex a week).
so i made something like
select date,count(ip) as raw, count(distinct ip) as uniq from clicks where affilate_id = '3444' and date >= '2004-02-25' and date <= '2004-03-02' group by date;
it works fine for the clicks but i am faced with two problems
1.) if a partner did not send any hits for a particular date that date is totally skipped in the results. so how would i fix it? using a programming language (PHP) or is there a another way?
2.) i want to combine the total sales amounts for that date too from the commisions table. how would i join it ?
in genereal i want a output like this
|Date | Raws | Uniques | Amount |
| 2004-02-25 | 50 | 15 | 20.00 |
| 2004-02-26 | 0 | 0 | 0 |
| 2004-02-27 | 100 | 75 | 0 |
..
...
...
Thanx
Any help would be appreciated.