Below are the 2 tables:
classes
academic_year_id student_id standard
2 1 10
2 2 10
2 3 10
2 4 10
2 5 10
2 6 10
2 7 11 Science
2 8 11 Science
Here standard is not INT
student
student_id roll_no name teacher_approval
1 0 S Sumeet G 1
2 0 Nair Nikhil R 1
3 0 Nayak Ankita R 0
4 0 Rathod Ketan P 0
5 0 Patel Vishal D 1
6 0 Patel Jignesh R 0
7 0 Prajapati Bhavesh A 1
8 0 Shah Harsh N 1
What i want to do:
when teacher selects standard -> 10 and press a button "Assign Roll No"
i want all student of 10th standard to be given roll no sequentially order by name and which are approved by teacher that is teacher_approval = '1'
So my student table becomes as below:
student
student_id roll_no name teacher_approval
1 3 S Sumeet G 1
2 1 Nair Nikhil R 1
3 0 Nayak Ankita R 0
4 0 Rathod Ketan P 0
5 2 Patel Vishal D 1
6 0 Patel Jignesh R 0
7 0 Prajapati Bhavesh A 1
8 0 Shah Harsh N 1
I have so far tried below code:
$standard = $_POST['standard']
SET @incr = 0
UPDATE
student AS st
JOIN
( SELECT * FROM
classes AS classes
WHERE standard = '".$standard."'
) AS tmp
ON tmp.student_id = st.id
SET
st.roll_no = @incr:=@incr+1
WHERE st.teacher_approval = '1'
ORDER BY st.name ASC
But it gives me an error: incorrect usage of update and order by
Anyone with a similar experience? Any help would be appreciated. Thanks.