Unique Identifier

Dev Shed Forums Sponsor:

November 1st, 2012, 03:04 AM

BenWalters

Registered User

Join Date: Jul 2010

Posts: 10

Time spent in forums: 2 h 27 m 30 sec
Reputation 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?

November 1st, 2012, 04:03 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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 |
+-----------------+

November 1st, 2012, 04:15 AM

BenWalters

Registered User

Join Date: Jul 2010

Posts: 10

Time spent in forums: 2 h 27 m 30 sec
Reputation Power: 0

Quote:

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?

November 1st, 2012, 06:25 AM

BenWalters

Registered User

Join Date: Jul 2010

Posts: 10

Time spent in forums: 2 h 27 m 30 sec
Reputation 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?

November 1st, 2012, 06:40 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Quote:

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 = ...

November 1st, 2012, 06:42 AM

BenWalters

Registered User

Join Date: Jul 2010

Posts: 10

Time spent in forums: 2 h 27 m 30 sec
Reputation Power: 0

Quote:

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?

November 2nd, 2012, 02:25 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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.

Unique Identifier

Developer Shed Advertisers and Affiliates