automatic call reference creation

hi all,

i need to create an automatic call reference. i though it would work using
the date as ccyymmdd and by adding a '001', '002', etc to the end.

now, i know how to format the date but how would i increment the 001, 002 at
the end... ? i understand that i would have to check back to the database to
check the last one used. but how... ?

alternatively is to just generate a random number, check it against the
database to see if it's not already used. if not use it. if its used
already, just create another one... ?

OR

do you guys have any laternative suggestions ?


Regards,

Gawie.

*** Gawie Marais wrote/escribió (Thu, 10 Jun 2004 11:52:01 +0200):
> i need to create an automatic call reference. i though it would work using
> the date as ccyymmdd and by adding a '001', '002', etc to the end.

What database type are you using? Most DBMS have autoincrement fields.


--
--
-- Álvaro G. Vicario - Burgos, Spain
--

i'm using MySQL 3.23 and i am aware of the auto_increment function.

but i was hoping to use the date i..e 20040101 plus a 001 added to the back
of that and store that into a field. but the problem is, how do i know what
the next available 00? is ??

gm./


"Alvaro G Vicario" <alvaro_QUITAR_REMOVE@telecomputeronline.com> wrote in
message news:sjyfsbzqaksc.1s1cl2pntkd7a.dlg@40tude.net...
> *** Gawie Marais wrote/escribió (Thu, 10 Jun 2004 11:52:01 +0200):
> > i need to create an automatic call reference. i though it would work
using
> > the date as ccyymmdd and by adding a '001', '002', etc to the end.
>
> What database type are you using? Most DBMS have autoincrement fields.
>
>
> --
> --
> -- Álvaro G. Vicario - Burgos, Spain
> --

*** Gawie Marais wrote/escribió (Thu, 10 Jun 2004 12:11:22 +0200):
> but i was hoping to use the date i..e 20040101 plus a 001 added to the back
> of that and store that into a field. but the problem is, how do i know what
> the next available 00? is ??

I can think of two options, both involving a separate numeric ID field.

1) Use ID column as autoincremental. Insert new record, leaving date field
empty. Read last inserted ID. Update date field.

2) SELECT MAX(id) as previous_id FROM table. Insert new record using
previous_id+1.


--
--
-- Álvaro G. Vicario - Burgos, Spain
--

*** Alvaro G Vicario wrote/escribió (Thu, 10 Jun 2004 13:32:22 +0200):
> I can think of two options, both involving a separate numeric ID field.

Or even easier, just keep one date_id field:

200410060001

When inserting new records, you can check for maximum value using MAX(),
get the substring with 0001, convert to numeric and increment it.

--
--
-- Álvaro G. Vicario - Burgos, Spain
--

sounds as a piece of cake... if you know php raw coding.... :(



"Alvaro G Vicario" <alvaro_QUITAR_REMOVE@telecomputeronline.com> wrote in
message news:1qr41i7pzp1by.133phn1r28voi.dlg@40tude.net...
> *** Alvaro G Vicario wrote/escribió (Thu, 10 Jun 2004 13:32:22 +0200):
> > I can think of two options, both involving a separate numeric ID field.
>
> Or even easier, just keep one date_id field:
>
> 200410060001
>
> When inserting new records, you can check for maximum value using MAX(),
> get the substring with 0001, convert to numeric and increment it.
>
> --
> --
> -- Álvaro G. Vicario - Burgos, Spain
> --