Unique Random Number and Autoincrement refreshing.

[RivaCom]

I have 2 questions that hopefully are easy enough.

1. I have a users table already filled out. I am looking to be able to insert userid into a new table. The new table has 2 fields, orderID and userid. I have orderID to auto increment. Now what I'm trying to do is select randomly from the users list and put them into the new table. However I can't have the same userid twice and I need to be able to use all the userids. Is there a easy way to do this?(EX: My userids range from 0-49, so I will have 50 orderids, but I don't want the userids to be in order.)

2. I will need to be able to select a userid, find out what the last orderID is and change that userid's orderid to that last number(or the number after). Then refresh the autoincrement so it isn't missing any numbers.

So for example I have 5 users. Say I take user 2 out and move him to the bottom. Now I have a orderid of 1,3,4,5,6, is there a way to find out the last order ID, add 1, and then refresh so it goes back to 1,2,3,4,5?

[felgall]

For just 50 entries the most efficient way to retrieve them in a random order is to use ORDER BY RAND()

[Old Pedant]

So for example I have 5 users. Say I take user 2 out and move him to the bottom. Now I have a orderid of 1,3,4,5,6, is there a way to find out the last order ID, add 1, and then refresh so it goes back to 1,2,3,4,5?

Don't do it that way.

Do this, instead:

UPDATE mytable SET orderid = -orderid WHERE orderid = $n;
UPDATE mytable SET orderid = orderid - 1 WHERE orderid > $n
UPDATE mytable SET orderid = 1 + (SELECT MAX(orderid) FROM mytable) WHERE orderid < 0

You see it? Using your example (1,2,3,4,5 and moving 2 to the bottom):

UPDATE mytable SET orderid = -2 WHERE orderid = 2
(so now you have -2,1,3,4,5)
UPDATE mytable SET orderid = orderid -1 WHERE orderid > 2
(so 3,4,5 become 2,3,4 and now you have -2,1,2,3,4)
UPDATE mytable SET orderid = 1 + (4) WHERE orderid < 0
(where 4 is the max(orderid)...so means -2 is changed to 5 because it is only one < 0)