delete number modify order

[dragon_sa]

I have a mysql table with the following sequential number

 

Order

 

1

2

3

4

5

6

7

 

Say I choose to delete number 4, how do I create a script to subtract 1 from each number greater than 4 in the list to keep the remaining numbers in sequence?

[intellix]

if its an autonumber column then it should do it automatically anyway...

 

Perhaps you could do a SELECT max statement to see what the biggest number is, then INSERT +1 that

 

$result = mysql_query("SELECT max(numbercolumn) FROM table",$link);

$newnumber = mysql_result($result,0,0) + 1;

[dragon_sa]

The column is not an auto number, its a display order

it gets created with max +1 everytime a new item is added to the table, if I delete any of the ordered numbers I would like to -1 from every item number above that number deleted to bring the sequence back into line and not skip any numbers.

[trq]

UPDATE tbl SET fld = fld-1 WHERE fld > 4

[dragon_sa]

I like that thorpe can I throw 1 little spanner into that

 

The fields in the columns are also part of categories which are numbered eg

 

Order  Category

1        1

2        1

3        1

4        1

5        1

6        1

7        1

1        2

2        2

3        2

1        3

2        3

3        3

4        3

 

Can I use that with GROUP so only the numbers that are part of that GROUP are affected or would i be better with some sort of for each statement?

 

I believe that is the only spanner

[intellix]

If it's being done on the fly though, can't you just do that as you go along and make sure you're editing the right category?

 

UPDATE tbl SET fld = fld-1 WHERE fld > 4 AND category = 1

 

It sounds like this is what you want

 

User: deletes item at 4th order in category 5, update category 5

User: deletes item at 2nd order in category 3, update category 3

[dragon_sa]

thank you very much for your help this is what worked a treat fro me

 

// update order of catalogs
$ordsql="UPDATE catalog SET catOrder=(catOrder-1) WHERE catOrder > '$catNum' AND collectionID='$catCollID'";
$resultorder = mysql_query($ordsql);