copy auto_increment value to another field

[Paul Lamonby]

Hi,

i want to create a unique serial number to my Db entries. I thought
the best way would be to add the auto_increment primary key value to a
string, then insert it into a table field 'serial_num'.

$serial_num = "NAME000" . $auto_increment _value;

Is this possible in one function? Or do I have to insert all the other
data first, then get the last inserted id value and then update the
entry with the $serial_num variable?

Cheers

[Nel]

Re: copy auto_increment value to another field

"Paul Lamonby" <paul@popimages .com> wrote in message
news:86406024.0 405181039.59f97 eff@posting.goo gle.com...[color=blue]
> Hi,
>
> i want to create a unique serial number to my Db entries. I thought
> the best way would be to add the auto_increment primary key value to a
> string, then insert it into a table field 'serial_num'.
>
> $serial_num = "NAME000" . $auto_increment _value;
>
> Is this possible in one function? Or do I have to insert all the other
> data first, then get the last inserted id value and then update the
> entry with the $serial_num variable?
>
> Cheers[/color]

Hi Paul,

If you set one field type, lets call it "id", to an INT and set
auto_increment, primary, unique, this would automatically create a unique
number within the "id" field for all entries. You can the prefix the id
number with "NAME"+$id within php to achieve the above.

It really depends on what you want the serial number for, human readable
stuff or just passing on id within your site(s). You could use an md5()
hash of your number + some other variable to provide you with a more secure
id.

Nel.

[Paul Lamonby]

Re: copy auto_increment value to another field

"Nel" <nelly@ne14.co. NOSPAMuk> wrote in message news:<jKuqc.473 5$wI4.554577@wa rds.force9.net> ...[color=blue]
> "Paul Lamonby" <paul@popimages .com> wrote in message
> news:86406024.0 405181039.59f97 eff@posting.goo gle.com...[color=green]
> > Hi,
> >
> > i want to create a unique serial number to my Db entries. I thought
> > the best way would be to add the auto_increment primary key value to a
> > string, then insert it into a table field 'serial_num'.
> >
> > $serial_num = "NAME000" . $auto_increment _value;
> >
> > Is this possible in one function? Or do I have to insert all the other
> > data first, then get the last inserted id value and then update the
> > entry with the $serial_num variable?
> >
> > Cheers[/color]
>
> Hi Paul,
>
> If you set one field type, lets call it "id", to an INT and set
> auto_increment, primary, unique, this would automatically create a unique
> number within the "id" field for all entries. You can the prefix the id
> number with "NAME"+$id within php to achieve the above.
>
> It really depends on what you want the serial number for, human readable
> stuff or just passing on id within your site(s). You could use an md5()
> hash of your number + some other variable to provide you with a more secure
> id.
>
> Nel.[/color]

Nel,

Cheers for the reply, I have got it sorted, but my post was more
concerned with the 'correct way' to do this.

I have my auto_increment, primary, unique 'id' field and my 'serial'
field.
What I am doing is INSERTing data into the Db, then retrieving the
mysql_insert_id (), then UPDATEing the same entry with the $serial
prefixed variable, $serial = "NAME000" . $id;

Is the the correct way to do it? Or can I retrieve the id in the same
INSERT statement? Or can mysql add a prefix to a auto_increment,
primary, unique field?

Paul

[Tim Van Wassenhove]

Re: copy auto_increment value to another field

In article <86406024.04051 90734.46a71165@ posting.google. com>, Paul Lamonby wrote:[color=blue]
> "Nel" <nelly@ne14.co. NOSPAMuk> wrote in message news:<jKuqc.473 5$wI4.554577@wa rds.force9.net> ...
> What I am doing is INSERTing data into the Db, then retrieving the
> mysql_insert_id (), then UPDATEing the same entry with the $serial
> prefixed variable, $serial = "NAME000" . $id;[/color]

I presume it will work. But i don't see why you want to add a prefix to
the key? To me, it seems that that prefix is quite redundant.

I haven't had the time to check it out, but i think you might notice a
difference in speed (i know there is no theoretical base for this, but
it might to try it out in the real world) if you use an INT instead of
a CHAR for your primary key.

--
Tim Van Wassenhove <http://home.mysth.be/~timvw/contact.php>

[Paul Lamonby]

Re: copy auto_increment value to another field

Tim Van Wassenhove <euki@pi.be> wrote in message news:<2h1j8dF81 rksU1@uni-berlin.de>...[color=blue]
> In article <86406024.04051 90734.46a71165@ posting.google. com>, Paul Lamonby wrote:[color=green]
> > "Nel" <nelly@ne14.co. NOSPAMuk> wrote in message news:<jKuqc.473 5$wI4.554577@wa rds.force9.net> ...
> > What I am doing is INSERTing data into the Db, then retrieving the
> > mysql_insert_id (), then UPDATEing the same entry with the $serial
> > prefixed variable, $serial = "NAME000" . $id;[/color]
>
> I presume it will work. But i don't see why you want to add a prefix to
> the key? To me, it seems that that prefix is quite redundant.
>
> I haven't had the time to check it out, but i think you might notice a
> difference in speed (i know there is no theoretical base for this, but
> it might to try it out in the real world) if you use an INT instead of
> a CHAR for your primary key.[/color]

Tim,

I know it seems strange, but I need this unique 'serial number' for
people to enter in via SMS to download an image from the server. I
could easily use the auto_increment primary key value, but people just
entering '1, 2, 3.. 34' as a serial number doesnt really have the same
kudos as a prefixed string, like IMAGE0023.

My post was wondering whether it is possible to set up a field in
mysql that auto_increments onto a string prefix, instead of having to
retrieve the id value and add it into another field, but it seems this
is not possible, so i have opted to retrieve the value and add it
manually.

Cheers

[Geoff Berrow]

Re: copy auto_increment value to another field

I noticed that Message-ID:
<86406024.04052 11033.3b1a9a54@ posting.google. com> from Paul Lamonby
contained the following:
[color=blue]
>My post was wondering whether it is possible to set up a field in
>mysql that auto_increments onto a string prefix, instead of having to
>retrieve the id value and add it into another field, but it seems this
>is not possible, so i have opted to retrieve the value and add it
>manually.[/color]

Wouldn't it be just as easy to get the system to ignore any prefix?
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/