Incrementing other data in table when one data is edited

Question

I have this page in my system that displays all the categories entered. The table has three fields as follows:

1. cat_id
2. category
3. priority

The homepage displays all entries (blog) and is grouped by category. Now the idea is the administrator can change the 'priority' of a 'category' so that the page will be arranged based on what he sees fit.

Example:

==============================
| Category     |  Priority   |
==============================
|  Category1   |   1         |
|  Category2   |   2         |
|  Category3   |   3         |
|  Category4   |   4         |
|  Category5   |   5         |
===============================

The idea is when the user edits the priority of, let's say, category 5 to 1, the rest of the priorities of each category less than the priority edited would be adjusted by incrementing it by 1 so that there would be no duplicates.

So far I only have this.

if (isset($_POST['edit'])) {
         if (is_numeric($_POST['cat_id']))

include('connect.php');

    $id = $_POST['cat_id'];
    $category = $_POST['category'];
    $priority = $_POST['priority'];

    mysql_query("UPDATE category 
                 SET category='$category', priority='$priority'
                 WHERE cat_id='$id'") or die(mysql_error());


     header("Location: addctg.php");                     

    }

Any help on how to structure?

Answer 1

Let's deal with a more general case than moving Category5 from priority 5 to priority 1. There are two (mirror image) better examples:

• Move Category4 to Priority 2.
• Move Category2 to Priority 4.

In each of these cases, there are two unaffected rows (those with priority 1 and 5), one row that is specified as moving, and two rows affected as 'collateral damage'.

Most of the trick with an UPDATE statement is working out a SELECT statement that generates the correct data for the 'after' state from the 'before' state. The other trick is identifying the parameters that control the operation. Here, there are two parameters:

1. The category (name) — or, equivalently, the category ID number.
2. The new priority for the category.

We might need separate algorithms for 'move up' (move a lower priority category to a higher priority), and for 'move down' (move a higher priority category to a lower priority).

Here are the before and after scenarios illustrated:

Before                        Category4 to Priority 2       Category2 to Priority 4
========================      ========================      ========================
| Category  | Priority |      | Category  | Priority |      | Category  | Priority |
========================      ========================      ========================
| Category1 | 1        |      | Category1 | 1        |      | Category1 | 1        |
| Category2 | 2        |      | Category4 | 2        |      | Category3 | 2        |
| Category3 | 3        |      | Category2 | 3        |      | Category4 | 3        |
| Category4 | 4        |      | Category3 | 4        |      | Category2 | 4        |
| Category5 | 5        |      | Category5 | 5        |      | Category5 | 5        |
========================      ========================      ========================

Let's try the 'move up' algorithm first.

Move Up

Using TDQD — Test-Driven Query Development — we can generate the list in stages:

1. Rows that are unaffected at high priority:

   SELECT cat_id, category, priority
     FROM categories
    WHERE priority < 2;
   

2. Rows that are unaffected at low priority:

   SELECT cat_id, category, priority
     FROM categories
    WHERE priority > (SELECT priority FROM categories WHERE category = 'Category4');
   

3. The row that is moved:

   SELECT cat_id, category, 2 AS priority
     FROM categories
    WHERE category = 'Category4';
   

4. The rows that have to have their priority adjusted:

   SELECT cat_id, category, priority + 1 AS priority
     FROM categories
    WHERE priority >= 2
      AND priority < (SELECT priority FROM categories WHERE category = 'Category4');
   

Clearly, these 4 queries can be combined via a 4-way UNION to generate the complete new ordering. Equally clearly, since queries 1 and 2 select rows that don't change, we only need to process the rows that do change, which are generated by queries 3 and 4.

So, the changed rows are:

SELECT cat_id, category, 2 AS priority
  FROM categories
 WHERE category = 'Category4'
UNION
SELECT cat_id, category, priority + 1 AS priority
  FROM categories
 WHERE priority >= 2
   AND priority < (SELECT priority FROM categories WHERE category = 'Category4');

Now we just have to derive an UPDATE statement to change the priorities (without changing the category ID or the category name):

UPDATE categories
   SET priority = 
       (SELECT x.priority
          FROM (SELECT cat_id, 2 AS priority
                  FROM categories
                 WHERE category = 'Category4'
                UNION
                SELECT cat_id, priority + 1 AS priority
                  FROM categories
                 WHERE priority >= 2
                   AND priority < (SELECT priority FROM categories WHERE category = 'Category4')
               ) AS x
         WHERE x.cat_id = categories.cat_id
       )
 WHERE cat_id IN
       (SELECT cat_id
          FROM categories
         WHERE category = 'Category4'
        UNION
        SELECT cat_id
          FROM categories
         WHERE priority >= 2
           AND priority < (SELECT priority FROM categories WHERE category = 'Category4')
       );

The WHERE clause in the main UPDATE statement can be simplified, yielding:

UPDATE categories
   SET priority = 
       (SELECT x.priority
          FROM (SELECT cat_id, 2 AS priority
                  FROM categories
                 WHERE category = 'Category4'
                UNION
                SELECT cat_id, priority + 1 AS priority
                  FROM categories
                 WHERE priority >= 2
                   AND priority < (SELECT priority FROM categories WHERE category = 'Category4')
               ) AS x
         WHERE x.cat_id = categories.cat_id
       )
 WHERE cat_id IN
       (SELECT cat_id
          FROM categories
         WHERE priority >= 2
           AND priority <= (SELECT priority FROM categories WHERE category = 'Category4')
       );

This produced the correct answer when tested against IBM Informix Dynamic Server 11.70.FC2 running on Mac OS X 10.7.4.

Move Down

The parallel analysis for 'Move Down' yields the UPDATE statement:

UPDATE categories
   SET priority = 
       (SELECT x.priority
          FROM (SELECT cat_id, 4 AS priority
                  FROM categories
                 WHERE category = 'Category2'
                UNION
                SELECT cat_id, priority - 1 AS priority
                  FROM categories
                 WHERE priority <= 4
                   AND priority > (SELECT priority FROM categories WHERE category = 'Category2')
               ) AS x
         WHERE x.cat_id = categories.cat_id
       )
 WHERE cat_id IN
       (SELECT cat_id
          FROM categories
         WHERE priority <= 4
           AND priority >= (SELECT priority FROM categories WHERE category = 'Category2')
       );

Magnum Opus

The ultimate answer would combine these queries in such a way that it would work regardless of whether the named category moved up or down (and would do nothing if the category was nominated to stay in the same place). While I have no doubt that such a query could be developed, I don't think it is worth the effort involved. You could write a stored procedure to take the category name and the new priority for the category, and it would execute the requisite UPDATE statement. This could easily be driven from a UI (web browser or more closely coupled). What you've got above is complicated enough, but is explicable. And 'explicable' means you have a chance of it being 'maintainable' too.