Incrementing a variable in a loop.

[praveena mani]

create procedure view_select
(coll int(11))
begin
declare x  int;
declare y int;
set x=0;
select count(*) into y from s_details where college_code=coll;
while(x<y) do
select day_number,title,first_name,middle_name,last_name,
date_of_birth,e_mail,ref_number,
date_of_entry_of_details,course_id,x+1 from s_details;
set x=x+1;
end while;
end//
 

How to increment the value of x alone for each record being displayed?
In this the select statement is repeated as many times as x?
How to solve this problem?

[pbmods]

Heya, Praveena.

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

Change:

date_of_entry_of_details,course_id,x+1 FROM s_details;

To:

date_of_entry_of_details,course_id, x := x + 1 FROM s_details;

[praveena mani]

Heya, Praveena.

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

Change:

date_of_entry_of_details,course_id,x+1 FROM s_details;

To:

date_of_entry_of_details,course_id, x := x + 1 FROM s_details;

it becomes infinite loop.
I want x to act like serial number for my table.
Everytime wen a record is displayed x shud be incremented by 1.
Can u pls help me to implement this?

[pbmods]

Heya, Praveena.

Are you trying to set a variable LIMIT for your SELECT query?

Try using a Prepared Statement.

[praveena mani]

Heya, Praveena.

Are you trying to set a variable LIMIT for your SELECT query?

Try using a Prepared Statement.

I jus want to view the records what i ve entered in my table.
But with one more field added while displaying that is the serial number x.
I am getting the count of total number of records in the table into variable y.
So until x<y the loop should execute but it is not happening why?

[pbmods]

Heya, Praveena.

This line:

SELECT day_number,title,first_name,middle_name,last_name, 
date_of_birth,e_mail,ref_number,
date_of_entry_of_details,course_id,x := x + 1 FROM s_details;
 

does select every record from the table. You don't need to use a loop here.

[praveena mani]

Heya, Praveena.

This line:

SELECT day_number,title,first_name,middle_name,last_name, 
date_of_birth,e_mail,ref_number,
date_of_entry_of_details,course_id,x := x + 1 FROM s_details;
 

does select every record from the table. You don't need to use a loop here.

i even tried without a loop but that x=x+1 doesnt get incremented.
each time it is displaying zero only for x. Why?

[pbmods]

Heya, Praveena.

The colon is important.

[praveena mani]

Heya, Praveena.

The colon is important.

CREATE PROCEDURE view_select
(coll INT(11))
BEGIN
DECLARE x INT;
set x=0
SELECT course_id,x:=x+1 FROM s_details;
end//
 

this is what u asked me to do right?
but this gives an error.
if i remove the colon it is working

[praveena mani]

CREATE PROCEDURE view_select
(coll INT(11))
BEGIN
DECLARE x INT;
set x=0
SELECT course_id,x:=x+1 FROM s_details;
end//
 

this is what u asked me to do right?
but this gives an error.
if i remove the colon it is working

it is working in the sense the query is ok..
but x is always displayed as 0.