Ok, I'm probably pushing myself too much over my MySQL's skills, but here's my question: I have a table where I store the categories of the products giving them a sort order specified in the sort column
-----------------------
| category | sort |
|---------------------|
| category3 | 1 |
| category1 | 2 |
| category2 | 3 |
-----------------------
When I display the categories, I display them ORDER BY sort ASC. Now I want to give the admin the possibility to change the sort order and what I would do is something like extract the categories associated to the sort number and working them in PHP, adding +1 to the numbers that follow the position where the admin wants to place the category and sending multiple queries to the db updating all the sort values.
(i.e if the admin wants the category2 at position n.1, I would extract * the sort values adding +1 from 1-2, 3 excluded)
Since I think this is the most inefficient method and I was wondering if I can do all just with a MySQL query. For this reason I thought at something like this
$sql = 'CREATE PROCEDURE rowperrow()
BEGIN
SET @position = '.$newCatPosition.'
DECLARE rowNum INT DEFAULT 0
DECLARE i INT DEFAULT 0
SELECT COUNT(*) FROM productsareas INTO rowNum
SET i=0
WHILE i<rowNum DO
SELECT @sortNum = sort FROM productsareas
IF @sortNum>@position-1 THEN
UPDATE productsareas SET sort=@sortNum +1
END IF
SET i=i+1
END WHILE
END';
This query should check row-per-row if the sort number is > $newCatPosition-1 (that is the correspondent number where the admin wants to place the category -1, 'cause also the same number has to be incremented of +1 to left the space to the new category): if true, it replaces the sort number incrementing it of +1.
It's useless saying that I tried this query and it doesn't work, but the result I want to obtain is similar to what I wrote