my aim is to be able to incriment student form each year i.e form 1 next year to be form 2.i tried using **mysql events **but seem to be complicated for my situation. i have the following tables
id student_id form year
----------
1 2013-04-04888 1 2013
2 2013-04-01920 2 2013
after one year i want to have something like this
id student_id form year
----------
1 2013-04-04888 1 2013
2 2013-04-01920 2 2013
3 2013-04-04888 2 2014
4 2013-04-01920 3 2014
any one with the idea or suggestion on how i can do in this the proffesional way
Since this is a process that runs once a year, I'd create an external process that runs that query (one more page in your admin module, if this is a web app, or something similar depending on what you're doing); that way, users can control exactly when it happens, and also it is very simple to implement.
A cron job or some other agent maybe?
The insert would be something like this:
INSERT INTO people(student_id,form,year)
SELECT student_id,max(form)+1,max(year)+1
FROM people
WHERE form < 10 -- or something
GROUP BY student_id
It sounds like you have a poor database design here. You have a student, and for each student you want to know what form they are in. You seem to have access to the year they joined, and so you could store a "form 1" year.
For sake of precision lets say that the form year starts on the 1st of April.
The database table would look like
students (
-- added an int primary key for use later
id INT PRIMARY,
-- update this as needed
student_id VARCHAR(20) UNIQUE ,
yearStartedForm1 INT UNSIGNED
)
Using your example you might have the following records
id student_id yearStartedForm1
1 2013-04-04888 2013
2 2013-04-01920 2012
From this "form 1" year you could then calculate their current year by using something like the following two. Firstly if you wanted to replace form>4 with 'completed'
SELECT
d.id,
d.student_id,
IF(d.form > 4,'completed',d.form) AS form,
d.yearStartedForm1
FROM (
SELECT
s.id,
s.student_id,
v.currentFormYear - s.yearStartedForm1 + 1 AS form,
s.yearStartedForm1
FROM students s,(
SELECT IF(MONTH(CURRENT_DATE)<4,YEAR(CURRENT_DATE)-1,YEAR(CURRENT_DATE)) AS currentFormYear
) v
) d
Secondly if you wanted to limit form to a max of 5
SELECT
s.id,
s.student_id,
LEAST(v.currentFormYear - s.yearStartedForm1 + 1,5) AS form,
s.yearStartedForm1
FROM students s,(
SELECT IF(MONTH(CURRENT_DATE)<4,YEAR(CURRENT_DATE)-1,YEAR(CURRENT_DATE)) AS currentFormYear
) v
This would give you the current form number dynamically without the need for duplicate rows. Of course you could change yearStartedForm1 into a DATE field easily enough, and work off DATEDIFF or similar functions. The idea is still the same, as at present you are duplicating each student row when in fact all you are trying to do is derive a form number based on time having elapsed.