SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Create unique 8-digit number with existing rows

    Before anybody says anything, this isn't about security

    I need to be able to create a unique 8-digit number for each record - new records as-well-as existing records.

    For example, the IMDB. They use a system with a 6 digit number in their URL and then use mod rewrite. That's what I'd like to do. Unfortunately, I didn't think about this when planning the site so I'm stuck trying to get this working after the fact. Can this be done with mysql alone? I hope so!

    Like so: http://www.mysite.com/12345678/

  2. #2
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To my knowledge, what you should do is extract every bit of the data in the database, re-design the initial tables and such, and make a custom script to insert data creating a unique 8 digit number using server-side technology, checking to make sure it isn't already in there.

    Though, I'm curious if anyone has a better solution to what I suggested.
    Cross browser css bugs

    Dan Schulz you will be missed

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if you don't mind them being sequential, just add an auto_increment column to the table. then add 9999999 to that column when you're done. this makes adding new rows easy, too.

    if you need random, then do this:
    1. create a column to hold your 8 digit number
    2. run this statement: update datable set dacolumn = floor(1000000 + (rand() * 89999999))
    3. create a temporary table with a single column that matches your new column
    4. run this statement: insert datemptable select dacolumn from datable group by dacolumn having count(*) > 1
    5. if 0 rows are inserted, that means you have no duplicates, so you're done! skip the rest of the steps
    6. run this statement: update datable set dacolumn = floor(10000000 + (rand() * 89999999)) where dacolumn in (select * from datemptable)
    7. empty datemptable
    8. go to step 4


    you should also add a primary key or unique index on that column.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    if you don't mind them being sequential, just add an auto_increment column to the table. then add 9999999 to that column when you're done. this makes adding new rows easy, too.

    if you need random, then do this:
    1. create a column to hold your 8 digit number
    2. run this statement: update datable set dacolumn = floor(1000000 + (rand() * 89999999))
    3. create a temporary table with a single column that matches your new column
    4. run this statement: insert datemptable select dacolumn from datable group by dacolumn having count(*) > 1
    5. if 0 rows are inserted, that means you have no duplicates, so you're done! skip the rest of the steps
    6. run this statement: update datable set dacolumn = floor(10000000 + (rand() * 89999999)) where dacolumn in (select * from datemptable)
    7. empty datemptable
    8. go to step 4


    you should also add a primary key or unique index on that column.
    Nice thinking, wouldn't have thought of that.
    Cross browser css bugs

    Dan Schulz you will be missed

  5. #5
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't mind them being sequential, but the table already has an auto_increment column - the pk, and phpmyadmin won't let me add another auto_increment column.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    then just add 9999999 to that column. that will make all of your numbers at least 8 digits. just make sure your data type is big enough to handle that.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  7. #7
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The pk column is BIGINT(20).

    I'm not sure what you mean by 'add 9999999 to that column'

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    update datable set dacolumn = dacolumn + 9999999

    just make sure that whatever other tables reference dacolmn also get the same update.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  9. #9
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gotcha. Thanks


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
  •