Ika Pkhakadze Ika Pkhakadze - 7 months ago 17
SQL Question

Updating mysql numeric field knowing only direction

I have mysql table like this:

id, [.....], priority


The priority field is used to sort table as I want. It's based on human intelligence. I have some values in it:

id, priority
1, 1
2, 7
3, 2
4, 4
5, 6
6, 5
7, 3


and second (BIGGEST) problem:
For example I want to move the 4th element up or down depending on priority.

4th element moved UP(left) and DOWN(right)

1, 1 1,1
2, 7 2,7
3, 2 3,2
4, 3 4,5
5, 6 5,6
6, 5 6,4
7, 4 7,3


i want to do it with single mysql query, because it's called via ajax and I don't want a lot of queries to be executed on serverside.

Answer
$int_prio = (1 - ((int)($_POST['move_up'] == 'TRUE') * 2));
$int_id = (int)$_POST['id'];

mysql_query("
INSERT INTO table_name (id, priority)
SELECT h2.id, 
    (h2.priority + ({$int_prio} * (1 - (2 * (h.id != h2.id))))) AS priority
FROM (  SELECT id, priority
        FROM table_name
        WHERE id = {$int_id}
        HAVING priority BETWEEN 2 AND ( SELECT MAX(priority)
                                        FROM priority
                                        GROUP BY '1') - 1) AS h
INNER JOIN priority AS h2
ON h.priority IN (h2.priority, h2.priority + {$int_prio})
ON DUPLICATE KEY UPDATE priority = VALUES(priority)
");

Basicly change the $_POST (preferable to more safier) and the table_name.

I've added validation so that it doesn't try to change highest and lowest priority aswell.

Sorry if you find above query confusing, but that's a way to update it all in a single query with added validation.

Comments