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.

    To include empty dates, what I'd do is create a 2-dimensional array. After the sql results are returned, populate the array like this in a results loop:

    $date_a['$date'][1] = $raw;
    $date_a['$date'][2] = $uniq;

    When that's done, use a for loop to go through your date range printing out the contents of the sub arrays. On blank dates, the sub arrays will be empty, so arrange to print zeros or whatever.

    I also do that when I want to display results from several (unjoinable-at least by me) queries in one table.

    On your second question, there doesn't seem to anything linking clicks to commisions directly, as with a foreign key. I would have done that differently.

    But you could run a query right after the first one, summing amounts and grouping by day and adding that to $date_a['$date'][3]. Then just display 3 columns for each date in the table.

      just what i had in mind.
      thank your very much.

      but is this the optimal design ?? or is there a better design ?

        It's definitely not optimal. Results are returned in arrays. Building more arrays takes more time and memory. Whether this will be a problem depends on how much of these you have to spare.

        I'm not aware of a better way to include empty results from a select though.

        In your database, can one click get more than one commission? If no, why not just add a commission field to the clicks table? If yes, it seems that instead of duplicating the affiliate id, ip, date and time for each commision, you'd do better to just include a click foreign key in the commission table. Either way, you'd make the second query unnecessary.

        Unless I'm not understanding the data.

          Write a Reply...