CodingForums.com - View Single Post

Old Pedant · 10-08-2012, 08:44 PM

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

10-08-2012, 08:44 PM	PM User \| #4
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,162 Thanks: 59 Thanked 3,992 Times in 3,961 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.