Jump to content

Archived

This topic is now archived and is closed to further replies.

pgsjoe

sorting numbers

Recommended Posts

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 -

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.