[Resolved] Enforcing unique, non-auto-incrementing fields

[jasonmills58]

Ok, here's a fun one. My client asks for priority selector boxes. I make boxes that submit the item's selected priority onChange. No problem. But "No" they say, "What we really wanted was numbering boxes, that are auto-popped by how many total rows there are, and no two records can have the same "priority" value." With me so far?

I had to laugh, but basically, I'm not sure how to make sure the priority fields stays unique. So if we have:

clientid | fName | lName | priority

00001 | Joe | Smith | 1
00002 | Jane | Doe | 2
00003 | Jack | Sprat | 3

And I want to change Jane Doe to priority 1, how do I make sure that Joe Smith trades priorities with her? Oh, and if with the current set up, Jane Doe of priority 2 gets deleted, I need to make sure Jack is number 2 now. And if there are places 4, 5, 6, they all need to increment up one.

Here's what I used for my original priority selector:

form.php

                    <td align="center"><select name="servicePriority" onChange="selectPriority(this)">
<?php
for ($p = 1; $p <= 3; $p++) { 
	if ($p == $a_row['servicePriority']) { 
		$prioritySelected[$p] = "selected=\"selected\"";
	} else {
		$prioritySelected[$p] = "";
	}
?>
			<option <? print $prioritySelected[$p]; ?> value="actions/updateServiceAction.php?action=priority&priority=<? print $p; ?>&serviceid=<? print $a_row['serviceid']; ?>"><? print $p; ?></option>
<?
}
?>
                    </select></td>

selectPriority() just calls what I call an action page which has this on it:

action.php

		$query = "UPDATE services SET servicePriority='$priority'
					WHERE serviceid='$serviceid'";
		if ( ! mysql_query( $query, $link ) ) {
			print mysql_error();
			return false;
		}

[bubblenut]

The way the company I work for works with charts (top 100 products etc) works with priorities. It boils down to a very strict maintenance area. We do it by pulling the whole list out and then rearranging it with javascript and then updating the list afterwards. This would not be practical for a large list list though.
It's going to be very db intensive to do some actions if the user list is too big. Imagine extrapolating your example to a user table of 20 or 30 thousand! You would have to do that many individual updates. This should be made clear to the customer. You could create another table which just has the priority and the user_id, doesn't really adhere to the Relational data model (one-to-one relationship and all) but it would avoid the users table getting locked for long periods on end while a nasty priority change is goin on.
I'm interested as to why they need an ordering system like this for their users table.
HTH
Bubble

[jasonmills58]

Thanks Bubble, I will mention that to them. The user table was a bad example. Its being used this way in 3 or 4 different tables, but none of them are as big as their users. They are for ranking services and documents, etc, which will stay fairly small tables (probably under 50 records).

I think I figured out how to pop the select box with the number of rows - SELECT count(*) FROM services. I already use this for a pagination system.

Do you have any suggestions for maintaining the continuity here?

Thanks for your help!
cheers,
Jason

[LordShryku]

Sorry, but sounds silly....
The way I see it, there's really no need to update all of them if one is deleted. I usually just do a sudo-ranking with php, using order by ranking in the sql query.
Say you have this in there:

00001 | Joe | Smith | 1
00002 | Jane | Doe | 2
00003 | Jack | Sprat | 3 
00004 | Dumb | Manager | 4

You pull it up, and since everything is in order, it would look fine. Say you delete Jane Doe. Here's what I do:

$sql = "SELECT * FROM users ORDER BY rank ASC";
$res = mysql_query($sql);
$i = 1; // this is our php ranking
while($row=mysql_fetch_array($res)) {
   echo "Rank".$i.": ".$row['fName']." ".$row['lname']."<br />";
   $i++;
}

This would produce

Rank 1: Joe Smith
Rank 2: Jack Sprat
Rank 3: Dumb Manager

[jasonmills58]

Well, I think my problem is that I run in to problems like this in the database.

00001 | Joe | Smith | 5
00002 | Jane | Doe | 6
00003 | Jack | Sprat | 1 
00004 | Dumb | Manager | 4
00005 | Jerry | Skids | 7
00006 | Moes | Bar | 2
00007 | weird | client | 8 
00008 | odd | request | 3

Keeping the rankings updated gets kind of complex, particularly when you want to change Jerry Skids of rank 7 to rank 2, and everybody from rank 2 on down has to get bumped down.

[LordShryku]

Okay, so before you pull that update, you just bump them all down like this:

UPDATE table
   SET rank = (rank + 1)
 WHERE rank >= 2

Then update Jerry

UPDATE table
   SET rank = 2
 WHERE clientid = 00005

[jasonmills58]

how about

WHERE rank >=2 AND rank<=7

in this case?

[LordShryku]

Yeah, that would probably be better 😃

[jasonmills58]

Looks like the second part better be just $rank<7

Oh, and the whole thing has to go backwards if we're changing 2 to 7. How do we watch for backwards or forwards?

if ($newRank = $rank) {
  do nothing
}else{
    if ($newRank > $rank) {
        do it backwards
    }else{
        do it forwards
    }
}

eh?

[LordShryku]

Sounds like you got it...

[jasonmills58]

Thanks for all the help. I managed to talk them out of their madness. LOL

cheers!
jason