Need help in Procedure

[balaraju mandala]

Hi All,

This is my procedure, i am using cursor to access data one row by one. The
cursor is looping twice, though i have only one row data in the DB
table(playersloginfo). Can any body tell me why cursor is looping twice and
how can i control it, And please explain me what is this  declare continue
handler for sqlstate '02000' . I will be glad if anybody answer to me.

create procedure activity1()
begin
declare done int default 0;
declare pack text;
declare name, logout, login varchar(50);
set pack='';
declare cur1 cursor for select user_name, concat(logindate, ' ', logintime),
concat(logoutdate, ' ',logouttime) from playersloginfo;
declare continue handler for sqlstate '02000' set done = 1;
open cur1;
repeat
fetch cur1 into name, login, logout;
set pack = concat(pack, name, ',', login, ',', logout,';');
until done end repeat;
select pack;
close cur1;
end;|

Need help with procedure

[Barry]

Hello everyone!

The mysql documentation doesn't show any good infos er examples about 
writing procedures.


To be more specific:

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

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
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help with procedure

[Peter Brawley]

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]