Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
11-23-2011, 07:42 PM #1
- Join Date
- Sep 2011
- Thanked 0 Times in 0 Posts
Unique Random Number and Autoincrement refreshing.
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?
11-23-2011, 08:44 PM #2
- Join Date
- Sep 2005
- Sydney, Australia
- Thanked 632 Times in 622 Posts
For just 50 entries the most efficient way to retrieve them in a random order is to use ORDER BY RAND()
11-23-2011, 09:52 PM #3
Don't do it that way.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?
Do this, instead:
You see it? Using your example (1,2,3,4,5 and moving 2 to the bottom):Code: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
Code: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)
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.