Location: PHPKode > projects > els PHP web quiz > elsphpwebquiz_and_sources/procedures_txt/move_question.txt
CREATE  PROCEDURE move_question(
v_direction varchar(4),
v_question_id int
)
BEGIN
declare id_up int;
declare priority_up int;
declare id_down int     ;
declare priority_down int;
declare priority_current int;
declare v_quiz_id int;
select priority,quiz_id into priority_current,v_quiz_id from questions where id=v_question_id and parent_id=0;
select id,priority into id_up,priority_up from questions where quiz_id=v_quiz_id and priority<priority_current and parent_id=0 order by priority desc limit 0,1;
select id,priority into id_down,priority_down from questions where quiz_id=v_quiz_id and priority>priority_current and parent_id=0 order by priority limit 0,1;
if v_direction = 'up' then
    if priority_up is not null then
update questions set priority=priority_up where id=v_question_id and parent_id=0;
update questions set priority=priority_current where id=id_up and parent_id=0;
    end if;
ELSEIF  v_direction = 'down' then
    if priority_down is not null then
update questions set priority=priority_down where id=v_question_id and parent_id=0;
update questions set priority=priority_current where id=id_down and parent_id=0;
    end if;
end if ;
END;
Return current item: els PHP web quiz