From: Peter Brawley
Date: May 15 2006 5:50pm
Subject: Re: Need help with procedure
List-Archive: http://lists.mysql.com/mysql/198001
Message-Id: <4468BF63.1080901@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Barry wrote:
> Hello everyone!
> <snip>
> I have a table with saved clicks by users.
> Now i want to draw a graph with a php extension.
> Problem is: if i let me show the clicks, one day is missing.
> Because on that day noone clicked on the link.
> I use this query:
> SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM 
> clicks WHERE c_cl_id = 3 AND DATE(c_clicktime)
> BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime
To get data that isn't there from SQL, you pretty much need a join to 
data which _is_ there. So create a calendar table, for example 
calendar(d DATE), and populate it with one row per day of interest to 
you. (There is an example under 'Make a calendar table' at 
http://www.artfulsoftware.com/queries.php). Then write your query as 
something like ...

SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks
FROM calendar AS cal
LEFT JOIN clicks ON cal.d = clicks.DATE(c_clicktime)
WHERE c_cl_id = 3
  AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20'
GROUP BY clicktime;

PB

>
> The output gives me 19 entries because on the 20th January noone 
> clicked that link.
>
> I think this have to be done with a procedure.
>
> So that my query would give a 0 (zero) for the 20th January as clicks.
>
> How would i do something like that?
>
> Thanks for your time :)
>
> Mysql version is:  3.23.54
>
> Barry


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006