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 6 of 6
  1. #1
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts

    Need help on creating ticket ID

    Hi friends,

    I want to create a field in the MYSQL which should be auto increment and have started from ABCD000001 and so on.......


    Please help me !!!


    Regards,
    Nani
    Last edited by nani_nisha06; 10-09-2012 at 09:18 AM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You can't, auto increment only works on numbers. They are literally surrogate keys.
    Perhaps a better approach would be to use a char(36) and run against a UUID(). UUID will generate a 128bit hex encoded string. The documentation suggests is uses a UUID version 2, but it also specifies it uses random numbers on any OS that isn't freebsd based. Otherwise it is random replaced which is more appropriate of a version 4, even though it doesn't resign it as a version 4 UUID.
    UUID is supposed to give you global unique numbers, but with the above mention that doesn't actually guarantee it now. Since its the key, it will inform you if it fails and you can try again.

  • #3
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Fou-Lu View Post
    You can't, auto increment only works on numbers. They are literally surrogate keys.
    Perhaps a better approach would be to use a char(36) and run against a UUID(). UUID will generate a 128bit hex encoded string. The documentation suggests is uses a UUID version 2, but it also specifies it uses random numbers on any OS that isn't freebsd based. Otherwise it is random replaced which is more appropriate of a version 4, even though it doesn't resign it as a version 4 UUID.
    UUID is supposed to give you global unique numbers, but with the above mention that doesn't actually guarantee it now. Since its the key, it will inform you if it fails and you can try again.
    Fou-Lu,

    Also suggest on the below thread.


    Need Ticket Number generating and updating script using mysql & php

    Regards,
    Nani

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    But another answer is to simply use TWO fields and then a VIEW when you SELECT from the table.

    For example:
    Code:
    CREATE TABLE tickets_base (
        prefix CHAR(4),
        ticketcount INT AUTO_INCREMENT,
        name VARCHAR(50),
        PRIMARY KEY ( prefix, ticketcount )
    );
    And then
    Code:
    CREATE VIEW tickets 
    AS
    SELECT CONCAT(prefix, RIGHT( CONVERT( 1000000 + ticketcount, CHAR ), 6 ) ) AS ticket, name
    FROM tickets_base;
    So when you add records, you always use INSERT into ticket_base, for example
    Code:
    INSERT INTO tickets_base ( prefix, ticketcount, name )
    VALUES( 'ABCD', NULL, 'Abigail Adams' );
    But when you GET a record you always SELECT from the view:
    Code:
    SELECT * FROM tickets WHERE name LIKE 'Abigail%'
    And that will return you
    Code:
    ABCD000073  Abigail Adams
    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.

  • Users who have thanked Old Pedant for this post:

    nani_nisha06 (10-09-2012)

  • #5
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts
    Old Pedant Sir,

    The above procedure worked like a charm but, I did small change in the create table, As below.

    Code:
    CREATE TABLE tickets_base (
        prefix CHAR(4),
        ticketcount INT AUTO_INCREMENT,
        name VARCHAR(50),
        PRIMARY KEY ( prefix), KEY (ticketcount) 
    );  
    
    //Because You can have an auto-Incremental column that is not the PRIMARY KEY, as long as there is an index (key) on it:

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Your solution however is not as good as OP's solution.
    with the two column primary key, you will only have to enter the info in the prefix column and the autoincrement will start at 1 and increase for each prefix so you will end up with

    ABC 1
    ABC 2
    DEF 1
    DEF 2
    DEF 3
    GHI 1

    and for your solution you will just have the autoincrement column increase indefinitely, it won't start at 1 for each of your different values in the prefix column, whereas his will.


  •  

    Posting Permissions

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