At 3:13 PM +0100 2/16/00, Davor Cengija wrote:
> I'm writing the banner stats system and now I have some
> problems with a query which should show me the complete viewed
> and clicked sums for each day.
>
> Here's the table:
>
>mysql> explain banner_access;
>+-------------+--------------------------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------------+--------------------------+------+-----+---------+-------+
>| banner_id | int(11) | YES | | NULL | |
>| category_id | int(11) | YES | | NULL | |
>| access_date | timestamp(14) | YES | | NULL | |
>| access_type | enum('viewed','clicked') | YES | | viewed | |
>+-------------+--------------------------+------+-----+---------+-------+
>
> and
>
<SNIP>
>+----------+-------------+----+-------------+
>| dan | banner_name | c | access_type |
>+----------+-------------+----+-------------+
>| 20000124 | 43 | 19 | viewed |
>| 20000124 | 43 | 1 | clicked |
>| 20000125 | 43 | 11 | viewed |
>| 20000125 | 43 | 1 | clicked |
>| 20000128 | 43 | 18 | viewed |
>| 20000202 | 43 | 2 | viewed |
>| 20000202 | 43 | 1 | clicked |
>| 20000207 | 43 | 8 | viewed |
>| 20000209 | 43 | 13 | viewed |
>| 20000210 | 43 | 36 | viewed |
>| 20000211 | 43 | 16 | viewed |
>+----------+-------------+----+-------------+
>11 rows in set (0.00 sec)
>
> It's obvious that there was no clicks on Jan.28th, Feb.07 etc.
>
> What I'd like to have is the query which will inform me that c
> column (count) is zero, like
>
>| 20000128 | 43 | 18 | viewed |
>| 20000128 | 43 | 0 | clicked |
>
>
> After trying few different queries, I decided to post the
> question here.
>
> Any help?
>
> Thanks.
>--
I don't think you can do that in a query the way your database is set
up. That would require MySQL to know that your table 'should' contain
one record for every day. I see two options:
(1) As you iterate through the result's rows in whatever programming
language you use, check for missing dates; or,
(2) Preload the table with ALL dates you want, with their values
initialized to 0. The, instead of using an INSERT query in your
banner stats system, use an UPDATE or REPLACE at all times. You could
probably write a little program to go back in to your current tables
an add the missing dates.
If I were me, I'd do (2)
- steve
+---------------- Splurk! Glort! Klikrunk! Ploip! Katoong!---------------+
| Steve Edberg University of California, Davis |
| sbedberg@stripped (530)754-9127 |
| Computer Consultant http://aesric.ucdavis.edu/ |
+------------------ Don Martin 18 May 1931 - 7 Jan 2000 -----------------+
