SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Smartest and most efficient way to randomize rows in an entire table?

    Hi there,

    This can be done in a number of ways like recreating the new table with randomized rows from the old one, or simply randomize the primary key serial number field numbers... or whatnot.

    The problem is this particular table will need to be say randomized quite frequently and will have almost 10,000 entries (never less). What would be the best way to approach this? The one thing i can say is that it has a serial field with INT type and it auto increments itself with each entry.

    Any ideas?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Two suggestions:

    1) ORDER BY RAND() when you SELECT, rather than changing the data

    2) Add a new column to order by, which you update with random values when you need to "re-randomize". E.g. "UPDATE table SET randcol = RAND()"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Displaying them randomly isn't what I want to achieve here. I mean what I'm doing here is developing a sort of a betting game where players guess what number a bot (simply their character/avatar) will be on and the ones to guess the correct serial where their bot would be in every 3 hour wins.

    What happens here is that every three hours the entire serial needs to be mixed up, shuffled, simply randomized.

    - Assume there is a bot "Kawaii Tenshi" (Serial #235) belonging to a player Alyshia.
    - Assume there is a bot "Deathscythe" (Serial #186) belonging to a player Bruce.

    ... among other thousands of bots. Here every three hours the listing will need to update the serial (INT, auto-increment, primary) field with randomized. Here Alyshia and Bruce bet (guess) that their bots will be on "Kawaii Tenshi" (Serial #180) and "Deathscythe" (Serial #234) respectively within the three hours.

    When the three hours are up the serial has to be physically changed there by randomizing the positions of all the bots... hope you get the general idea?

    I don't know another way to explain it. >.<

    E.g. "UPDATE table SET randcol = RAND()"
    I'm not sure something like that would work, you know its supposed to be a unique serial with auto-increment in tact... what if a rand() returns an existing value wouldn't the script come to a halt? ..

    I think what i really want to do is not change the serial order per see but rather the positions of the bots i.e exchange places with one another.

  4. #4
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't randomise the order of rows in a table because they're not stored in any particular order.

    What you can do is have two tables: user and bot. In user.bot_id store a reference to bot.id (and randomise it every 3 hours). Is that feasible? I'm not sure because I don't really understand your problem.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My first thought is to just do
    Code:
    bot_serials
    ---
    serial_id (auto_increment) | bot_id (int)
    
    
    TRUNCATE TABLE bot_serials;
    INSERT INTO bot_serials (bot_id) SELECT bot_id FROM bots ORDER BY RAND();
    Letting the auto increment be the new serial.


Bookmarks

Posting Permissions

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