Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    9
    Thanks
    1
    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?

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,609
    Thanks
    0
    Thanked 645 Times in 635 Posts
    For just 50 entries the most efficient way to retrieve them in a random order is to use ORDER BY RAND()
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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:
    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
    You see it? Using your example (1,2,3,4,5 and moving 2 to the bottom):
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •