Where clause have while loop

See more:

PHP

MySQL

I have upper given procedure and it display syntax error.
I have to use while loop in where clause as per my project requirement.

I have to compare two comma separated fields in where clause so i have set while loop in where clause which is used to get comma separated values one by one and pass it to find_in_set().

Before WHILE statement is closed with semicolon(;) but in query how it's possible...

This is the because it display syntax error... Is there any solution for this ??
If you have alternative for this so please guide me ...

Thank you in advance

What I have tried:

BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE ctr INT DEFAULT 0;
	DECLARE str_len INT;
	DECLARE total_row INT;
	DECLARE occurance INT;
	DECLARE split_comma text;
	DECLARE out_str TEXT DEFAULT '';
	DECLARE str TEXT DEFAULT '';
	DECLARE tmp_str TEXT DEFAULT '';
	DECLARE tmp_val VARCHAR(255) DEFAULT '';
	
	SELECT course AS str FROM student where s.id = studentid1;
	SET str_len = LENGTH(str);
	SET i = (LENGTH(str)-LENGTH(REPLACE(str, ',', '')))/LENGTH(',') + 1;  	
	
select (SELECT count(distinct t.id)
	FROM test As t, package AS p, packagetestassoc as pt, course AS c, student as s
	WHERE p.id = pt.packageid AND find_in_set(t.id, pt.testid) <> 0 AND 
	t.status = 1 AND find_in_set(c.id,p.courseid) AND 
	
	WHILE (ctr < i) DO
		SET ctr = ctr + 1;
		SET tmp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, ',',ctr), LENGTH(SUBSTRING_INDEX(str,',',ctr-1))+1),',','');
	 	SELECT find_in_set(tmp_str, p.courseid);
	END WHILE;
	
	AND s.id = studentid1 AND 
	(p.packagecost =0 or
 	p.id IN (SELECT ps.packageid FROM paymentstatus AS ps WHERE ps.studentid = studentid1)))
AS totaltest,

(select (SELECT count(distinct t.id)
	FROM test As t, package AS p, packagetestassoc as pt, course AS c, student as s
	WHERE p.id = pt.packageid AND find_in_set(t.id, pt.testid) <> 0 AND
	
	WHILE (ctr < i) DO
		SET ctr = ctr + 1;
		SET tmp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, ',',ctr), LENGTH(SUBSTRING_INDEX(str,',',ctr-1))+1),',','');
	 	SELECT find_in_set(tmp_str, p.courseid);
	END WHILE;
	
	AND s.id = studentid1 AND 
	t.status = 1 AND find_in_set(c.id,p.courseid) AND t.testtype = 2 AND CURDATE() > t.enddate AND
	 t.id NOT IN (SELECT testid FROM testresult WHERE studentid = studentid1) AND t.id NOT IN (SELECT testid FROM incompletedtest WHERE studentid = studentid1) AND (p.packagecost =0 or
 	p.id IN (SELECT ps.packageid FROM paymentstatus AS ps WHERE ps.studentid = studentid1))))
AS missedtest,

(SELECT count(*)
 FROM test AS t, incompletedtest AS i
 where i.studentid = studentid1 AND t.id = i.testid) AS
incompletetest,

(SELECT count(*)
 FROM test AS t, testresult AS i
 where i.studentid = studentid1 AND t.id = i.testid) AS
completetest;
END;