#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    11
    Rep Power
    0

    Unique Identifier


    Hi All,

    I guess this is possible but I am just unaware of how to make this happen!!

    Ok so I have people coming to my site and filling out a form which, when submitted gets posted to a DB (SQL)
    My question is can I take for instance, the Country code (GB) & date (2012-11-01) and add an 5 digit auto incrementing number on the end. So the unique identifier will be GB2012110100001

    Can this happen? If so How?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    DROP TABLE IF EXISTS my_table;
    
    CREATE TABLE my_table 
    (id SMALLINT(5) NOT NULL AUTO_INCREMENT
    ,country_code CHAR(2) NOT NULL
    ,dt DATE NOT NULL
    ,PRIMARY KEY(country_code,dt,id)
    ) ENGINE = MyISAM;
    
    INSERT INTO my_table VALUES 
    (NULL,'GB',CURDATE()),
    (NULL,'GB',CURDATE()),
    (NULL,'DE',CURDATE()),
    (NULL,'DE',CURDATE()),
    (NULL,'GB',CURDATE()),
    (NULL,'GB','2012-11-02');
    
    SELECT * FROM my_table;
    +----+--------------+------------+
    | id | country_code | dt         |
    +----+--------------+------------+
    |  1 | DE           | 2012-11-01 |
    |  2 | DE           | 2012-11-01 |
    |  1 | GB           | 2012-11-01 |
    |  2 | GB           | 2012-11-01 |
    |  3 | GB           | 2012-11-01 |
    |  1 | GB           | 2012-11-02 |
    +----+--------------+------------+
    
    SELECT CONCAT(country_code,DATE_FORMAT(dt,'%Y%m%d'),LPAD(id,5,0)) x FROM my_table;
    +-----------------+
    | x               |
    +-----------------+
    | DE2012110100001 |
    | DE2012110100002 |
    | GB2012110100001 |
    | GB2012110100002 |
    | GB2012110100003 |
    | GB2012110200001 |
    +-----------------+
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    11
    Rep Power
    0
    Originally Posted by cafelatte
    Code:
    DROP TABLE IF EXISTS my_table;
    
    CREATE TABLE my_table 
    (id SMALLINT(5) NOT NULL AUTO_INCREMENT
    ,country_code CHAR(2) NOT NULL
    ,dt DATE NOT NULL
    ,PRIMARY KEY(country_code,dt,id)
    ) ENGINE = MyISAM;
    
    INSERT INTO my_table VALUES 
    (NULL,'GB',CURDATE()),
    (NULL,'GB',CURDATE()),
    (NULL,'DE',CURDATE()),
    (NULL,'DE',CURDATE()),
    (NULL,'GB',CURDATE()),
    (NULL,'GB','2012-11-02');
    
    SELECT * FROM my_table;
    +----+--------------+------------+
    | id | country_code | dt         |
    +----+--------------+------------+
    |  1 | DE           | 2012-11-01 |
    |  2 | DE           | 2012-11-01 |
    |  1 | GB           | 2012-11-01 |
    |  2 | GB           | 2012-11-01 |
    |  3 | GB           | 2012-11-01 |
    |  1 | GB           | 2012-11-02 |
    +----+--------------+------------+
    
    SELECT CONCAT(country_code,DATE_FORMAT(dt,'%Y%m%d'),LPAD(id,5,0)) x FROM my_table;
    +-----------------+
    | x               |
    +-----------------+
    | DE2012110100001 |
    | DE2012110100002 |
    | GB2012110100001 |
    | GB2012110100002 |
    | GB2012110100003 |
    | GB2012110200001 |
    +-----------------+
    Ok so my question now is, can that concatenation be saved on the same line as the form entry when posted?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    11
    Rep Power
    0
    Ok so I have put this

    Code:
    CONCAT(country,DATE_FORMAT(date,'%Y%m%d'),LPAD(id,5,0))
    Against the value of my uid!

    However the incrementing number on the end is producing 00000 everytime, help?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Originally Posted by BenWalters
    Ok so my question now is, can that concatenation be saved on the same line as the form entry when posted?
    I guess it could. But why would you?

    UPDATE my_table SET my_column = ...
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    11
    Rep Power
    0
    Originally Posted by cafelatte
    I guess it could. But why would you?

    UPDATE my_table SET my_column = ...
    Hi, firstly, thanks for your help
    I am a novice! I don't understand what you are saying here?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Don't store data that can be easily calculated 'on-the-fly' - unless you have a good reason for doing so, in which case, use an INSERT command followed by an UPDATE command.

IMN logo majestic logo threadwatch logo seochat tools logo