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 ?