MYSQL variables

adding an auto numbering column to SELECT result

[hafnius]

Hi all

I have been trying to retrive a result set with an autonumbering column so that the results can be listed with 1, 2, 3 etc. Tho code i am using is this:

SELECT @id:=@id+1, column_name FROM table_name;

Instead of an autonumbered row it just returns NULL in the MYSQL/PHPMYADMIN where i tested the querie.

I have searched on the net and on this site for a solution but with no result.

-just to clarify it is not the primary key wich is also autonumbered/incremented i am refering to.

Hope someone can help

Kind regards
/Hafnius

[coopster]

Did you SET [dev.mysql.com] the variable first?

SET @id = 0;

[hafnius]

Hi Coopster

No i didnt think i had too - but i SET the variable and now it works!

Thanx for the tip

Kind regards
/Hafnius

[coopster]

You're welcome, hafnius.

You don't have to SET a variable first, but, as the manual states, if you refer to a variable that has not been initialized, its value is

NULL

, and

NULL + 1

is still going to give you

NULL

, so it only makes sense in this case to initialize the user variable.

[hafnius]

I have a problem getting it to work in the real world out of PHPMYADMIN (yes im a beginner :)

my query:

$sql = 'SET @id =0;'
 . ' SELECT @id := @id +1, lang'
 . ' FROM test';

and then i try with the usual:

 $result = mysql_query($sql);
 while($r = mysql_fetch_array($result)) {
 @id := @id +1 = $r["@id := @id +1"];
 $lang  = $r["lang"];
echo "@id := @id +1 ,$lang<br />\n";
}
mysql_free_result($result);

-which doesnt produce anything and looks wrong too. Do i need to loop trough the result do i reference them like an array? The solution is probably real simply but i cant see it.

What i want is just:

1. <item>
2. <item>
etc...

Thoughts welcome
/Hafnius

[coopster]

You'll have to execute your queries separately, and you may want to give that "counter" an ALIAS [dev.mysql.com]:

mysql_query('SET @id =0;'); 
$sql = 'SELECT @id := @id +1 AS nbr, lang FROM test'; 
$result = mysql_query($sql); 
while($r = mysql_fetch_array($result)) { 
 $nbr = $r['nbr']; 
 $lang = $r['lang']; 
 echo "$nbr. $lang<br />\n"; 
}