sorting numbers

[pgsjoe]

situation: I have a jobs database. All job info is in there. My primary key is a field called "pk" and automatically increments. I also have a field called "id" which is a 30 character randomly generated string used for editing purposes. the jobs 'print' out on the page in order of the PK field, but now when I delete a job, I need to almost do a ripple delete and have the numbers resort back into an order beginning at 1.

example: I have jobs 1-45. I delete jobs 5-10. I now need this list to reflect this change and label the jobs as 1-40, instead of what it's doing which is 1-4, 11-45. I'm quite the newbie still...so please be gentle.

- JoE -

[ToonMariner]

Firstly ehy do you need to do this? The beauty of databases and primary keys with auto increment is that they are simply a way to identfy and cross reference items. IMO you should avoid using keys of a table for anything else.

BUT if you really must (this could get messy).....

You know which items you have deleted - if they are all consective (5,6,7,8) then there is not so much of a problem...

create an comma separated list of the ids to go, count how many and then update the table like so...

This assumes you have a form with checkboxes next to each job you are deleting called "jobdel[]" (the [] creates an array of jobdel) and a value set to the id of the job.
[code]<?php
$delarr = implode(',',$_POST[jobsdel]);

$number = count($delarr);

// do the delete....
$sql = "DELETE FROM `jobs` WHERE `pk` IN (" . $delarr . ")";
$sql = mysql_query($sql);

// do teh update...
$sql = "UPDATE `jobs` SET `pk` = `pk` - $number WHERE ``pk` > " . $delarr[$number - 1] . "";
$sql = mysql_query($sql);

?>
[/code]

If the job numbers are not consecutive then the update would be a little more complex - you could either loop through the array doing an update wit `pk` = `pk` - 1 where pk > than element each time OR more efficient loop and generate a query string each time ending in ; and then mysql_query the whole lot.