Incrementing a variable in a loop.

[praveena mani]

[code=mysql]
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=co ll;
while(x<y) do
select day_number,titl e,first_name,mi ddle_name,last_ name,
date_of_birth,e _mail,ref_numbe r,
date_of_entry_o f_details,cours e_id,x+1 from s_details;
set x=x+1;
end while;
end//
[/code]


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:
[code=mysql]date_of_entry_o f_details,cours e_id,x+1 FROM s_details;[/code]

To:
[code=mysql]date_of_entry_o f_details,cours e_id, x := x + 1 FROM s_details;[/code]

[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:
[code=mysql]date_of_entry_o f_details,cours e_id,x+1 FROM s_details;[/code]

To:
[code=mysql]date_of_entry_o f_details,cours e_id, x := x + 1 FROM s_details;[/code]

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:
[code=mysql]
SELECT day_number,titl e,first_name,mi ddle_name,last_ name,
date_of_birth,e _mail,ref_numbe r,
date_of_entry_o f_details,cours e_id,x := x + 1 FROM s_details;
[/code]
does select every record from the table. You don't need to use a loop here.

[praveena mani]

Heya, Praveena.

This line:
[code=mysql]
SELECT day_number,titl e,first_name,mi ddle_name,last_ name,
date_of_birth,e _mail,ref_numbe r,
date_of_entry_o f_details,cours e_id,x := x + 1 FROM s_details;
[/code]
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.

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

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

[praveena mani]

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

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.