On Wed, 16 Feb 2000, Steve Edberg wrote:
>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 |
>>
>
>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.
Solved using method one, this is the code (PHP and PHPLIB);
functions are in the extended database class.
function StatQuery($type, $bid, $format = 8, $daylimit = '0') {
$qry = "select
substring(access_date, 1, $format) as dan,
count(*) as c
from banner_access, banner
where banner.banner_id = '$bid'";
if($daylimit > 0)
$qry .= "and to_days(now()) - to_days(access_date) < $daylimit";
$qry .= " and access_type = '$type'
and banner.banner_id=banner_access.banner_id
group by dan
order by dan";
return $qry;
}
/* banneri */
function BannerStat($bid, $format = 8, $daylimit = '0') {
$qry = $this->StatQuery('viewed', $bid, $format, $daylimit);
$this->query($qry);
$v = array();
$c = array();
while($this->next_record()) {
$datum = $this->f("dan");
$viewed = $this->f("c");
$v[$datum] = $viewed;
}
$qry = $this->StatQuery('clicked', $bid, $format, $daylimit);
$this->query($qry);
while($this->next_record()) {
$datum = $this->f("dan");
$clicked = $this->f("c");
$c[$datum] = $clicked;
}
$vc = array();
do {
$datum = key($v);
$viewed = $v[$datum];
$clicked = $c[$datum];
$vc[$datum]["viewed"] = $viewed;
$vc[$datum]["clicked"] = $clicked;
//printf("count vc: %d<br>", count($vc));
} while(next($v));
return $vc;
}
function PrintBannerStat($bid, $format = 8, $daylimit = 0) {
$vc = $this->BannerStat($bid, $format, $daylimit);
if(!is_array($vc)) {
print "No exposures or clicks!<br>\n";
}
else {
print "<table width=85%>";
print " <tr bgcolor=silver>";
print " <th>Data</th>";
print " <th>Visualizaçőes</th>";
print "<th>Clicks</th>";
print "<th>Taxa V/C</th>";
print "</tr>";
$totviewed = 0;
$totclicked = 0;
do {
printf("<tr>");
printf("<td align=right>%s</td>\n", FormatTS($datum = key($vc),
$format));
($vc[$datum]["viewed"]) ? $viewed = $vc[$datum]["viewed"] : $viewed = 1;
printf("<td align=right>%s</td>\n", $viewed);
$totviewed += $viewed;
($vc[$datum]["clicked"]) ? $clicked = $vc[$datum]["clicked"] : $clicked = 1;
$totclicked += $clicked;
printf("<td align=right>%s</td>\n", $clicked);
@printf("<td align=right>%d:1 (%2.2f %%)</td>\n", round($viewed /
$clicked), 100 * $clicked / $viewed);
printf("</tr>");
} while(next($vc));
printf("<tr><td colspan=4 align=right>");
printf ("<hr size=1 width=\"100%%\" noshade><b>Total de
visualizaçőes: %d, Total de Clicks: %d, Taxa de V/C %d:1
(%2.2f%%)</b><br>\n", $totviewed, $totclicked, round($totviewed/$totclicked),
100 * $totclicked / $totviewed);
print "</td></tr></table><br>\n";
}
}
--
v
Davor Cengija
davor@stripped
===================================
"Please allow 30 days for delivery"
