how to use a variable as a part of a field name in mysql trigger declaration Here is what I want to do (a part of my trigger):

BEGIN
DECLARE updatecount INT default 0;
declare number int;
declare column varchar(10);
declare i int default 1;
declare j int default 1;
while i&lt;=6 do
    set column = "num"+i; /*will be num1 num2, so on*/
    set number = NEW.@column; /* will represent NEW.num1, num2 so on*/


I know that the last 2 lines are not correct. But I wanted you guys to understand what I want to do. Can anyone tell me the correct syntax? thanks.

EDIT: 

what I want to do with this trigger is when a new row added into the resultsTBL, I want to check all the rows in the guessesTBL to find how many of them are same. 

For example we have 2 rows in guessesTBL (always in ascending order):

num1 num2 num3 num4 num5 num6
 1    5    10   15   20   26
 4    8    12   16   25   40


then we add a new row into the resultsTBL (always in ascending order):

num1 num2 num3 num4 num5 num6
 1    8    17   20   25   40


after that trigger will compare the new added row with all the rows in the guessesTBL:

for the 1st row the result is: 2
for the 2nd row the result is: 3


pseudocode:

CREATE DEFINER = CURRENT_USER TRIGGER `myDB`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
for each field in resultsTBL (num1, num2, ... num6)
  number= num1...num6
  for each field in guessesTBL (num1, num2, ... num6)
    if number == guessesTBL.num1,num2,...num6
      counter++
Your syntax is wrong. I don't think putting a loop inside a TRIGGER is a good idea, but I made you a Stored Procedure as example:

Procedure:

USE `test`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN

SET @counter=0;
SET @column='';

WHILE (@counter &lt; 6) DO
    SET @counter=@counter+1;
    set @column = CONCAT("num",@counter); /*will be num1 num2, so on*/
    SELECT CONCAT(" SET @number = NEW.",@column,"; ");

END WHILE;
END$$

DELIMITER ;


Output Test: 

mysql&gt; call test.new_procedure();
+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num1;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num2;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num3;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num4;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num5;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num6;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql&gt;