Incrementing Primary Key

Dear List -

Thanks for all your excellent help.

I am setting up a database for medical research, which will be
conducted at various sites. The sites will be identified by a letter
{A,B,C ....}. The medical record number [primary key] will start at
1001 and increment by one(1) for each patient at each site; ie, A
1001, A1002, B1001, B1002 ...... How do I do this?

Do I need a separate database for each site?

Ethan



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Incrementing Primary Key

I think having a table for each site would be sufficent

-----Original Message-----
From: Ethan Rosenberg [mailto:ethros@earthlink.net]
Sent: Wednesday, October 27, 2010 9:11 AM
To: php-db-lists.php.net
Subject: [PHP-DB] Incrementing Primary Key

Dear List -

Thanks for all your excellent help.

I am setting up a database for medical research, which will be conducted at
various sites. The sites will be identified by a letter {A,B,C ....}. The
medical record number [primary key] will start at
1001 and increment by one(1) for each patient at each site; ie, A 1001,
A1002, B1001, B1002 ...... How do I do this?

Do I need a separate database for each site?

Ethan



--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Incrementing Primary Key

Just set the primary key to AUTO_INCREMENT on each field and you won't have
to worry about it

-----Original Message-----
From: Ethan Rosenberg [mailto:ethros@earthlink.net]
Sent: Wednesday, October 27, 2010 9:11 AM
To: php-db-lists.php.net
Subject: [PHP-DB] Incrementing Primary Key

Dear List -

Thanks for all your excellent help.

I am setting up a database for medical research, which will be conducted at
various sites. The sites will be identified by a letter {A,B,C ....}. The
medical record number [primary key] will start at
1001 and increment by one(1) for each patient at each site; ie, A 1001,
A1002, B1001, B1002 ...... How do I do this?

Do I need a separate database for each site?

Ethan



--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

Hi Ethan,
in this case, you should manage the "keys" by your application, and
store the used ids in aonther table,
similar to a "sequence" in Oracle or Postgresql.
Thats so if you want all the records un one table (a field for the
letter, and the other for the numeric part).

Otherwise, having a table for each site will be enough, as Jimmy suggests.

--=20
Andrés G. Montañez
Zend Certified Engineer
Montevideo - Uruguay

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Incrementing Primary Key

In my opinion, having a database for each site would require a lot of =
unnecessary work, as you would have to connect to every database in =
order to handle the sites.
Having one database with tables for all the sites would be more =
productive as you would not have to change the connection info for PHP =
every time you want to use a different database.
I use the PDO mysql adapter, what do you use?

-----Original Message-----
From: Andrés G. Montañez [mailto:andresmontanez@gmail.com]=20
Sent: Wednesday, October 27, 2010 9:19 AM
To: Ethan Rosenberg
Cc: php-db-lists.php.net
Subject: Re: [PHP-DB] Incrementing Primary Key

Hi Ethan,
in this case, you should manage the "keys" by your application, and =
store the used ids in aonther table, similar to a "sequence" in Oracle =
or Postgresql.
Thats so if you want all the records un one table (a field for the =
letter, and the other for the numeric part).

Otherwise, having a table for each site will be enough, as Jimmy =
suggests.

--
Andrés G. Montañez
Zend Certified Engineer
Montevideo - Uruguay

--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: =
http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

Hi Ethan,=20

I am working currently as clinical developer and have developed multi site =
databases for clinical trials.=20

I would say the approach you take will depend on number of factors.=20

How is the site connectivity?=20

I had a scenario where sites had erratic and slow connectivity so i had to =
setup MySql server at each site and let that data sync over to a central lo=
cation, with help of mysqldump then i put all the data together into one da=
tabase from 3 replicas.=20

The application is more responsive over LAN and users are less likely to co=
mplain when entering data(can be a major difference between success / failu=
re)

If connectivity is not an issue and the app will reside on a central web se=
rver then putting everything in a single database is an option.=20

Of course your database design will have to reflect that using appropriate =
keys to be able to tie the CRF data to a particular site patient visit comb=
ination.

If you chose to go with replication, then you have to take care on how you =
auto increment values so that there are no collisions, best is to assign a =
range to each site this is the simplest way.=20

1001 - 5000 Site A
5001 - 10001 Site B

Of course you will need some input from your statisticians on how they want=
the data, that will also drive your design decisions on what works best.

Separate db or not:=20

I would separate data for each study into its own database, its much easier=
in terms of administration and locking the study.=20

Is the study single blinded, double blinded ?=20

How will randomization take place ? =20

These are all questions you need to know before you can decide how you stru=
cture your database and application therefore.=20

If you have other questions feel free to get in touch.

with kind regards,

Max.
------------------------------------------------
Max Kimambo
Franz-Stenzer-Straße, 51=20
12679, Berlin.
T: +493057706550 (new number)
M: +4917649520175
------------------------------------------------

----- Original Message -----
From: "Ethan Rosenberg"
To: "php-db-lists.php.net"
Sent: Wednesday, October 27, 2010 3:11:00 PM GMT +01:00 Amsterdam / Berlin =
/ Bern / Rome / Stockholm / Vienna
Subject: [PHP-DB] Incrementing Primary Key

Dear List -

Thanks for all your excellent help.

I am setting up a database for medical research, which will be=20
conducted at various sites. The sites will be identified by a letter=20
{A,B,C ....}. The medical record number [primary key] will start at=20
1001 and increment by one(1) for each patient at each site; ie, A=20
1001, A1002, B1001, B1002 ...... How do I do this?

Do I need a separate database for each site?

Ethan



--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

Yes indeed, as Jimmy says, having a database for each site is
impractical and a waste of resources.


On 27 October 2010 11:23, Jimmy Sole wrote:
> In my opinion, having a database for each site would require a lot of unn=
ecessary work, as you would have to connect to every database in order to h=
andle the sites.
> Having one database with tables for all the sites would be more productiv=
e as you would not have to change the connection info for PHP every time yo=
u want to use a different database.
> I use the PDO mysql adapter, what do you use?
>
> -----Original Message-----
> From: Andrés G. Montañez [mailto:andresmontanez@gmail.com]
> Sent: Wednesday, October 27, 2010 9:19 AM
> To: Ethan Rosenberg
> Cc: php-db-lists.php.net
> Subject: Re: [PHP-DB] Incrementing Primary Key
>
> Hi Ethan,
> in this case, you should manage the "keys" by your application, and store=
the used ids in aonther table, similar to a "sequence" in Oracle or Postgr=
esql.
> Thats so if you want all the records un one table (a field for the letter=
, and the other for the numeric part).
>
> Otherwise, having a table for each site will be enough, as Jimmy suggests=
..
>
> --
> Andrés G. Montañez
> Zend Certified Engineer
> Montevideo - Uruguay
>
> --
> PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: ht=
tp://www.php.net/unsub.php
>
>
>



--=20
Andrés G. Montañez
Zend Certified Engineer
Montevideo - Uruguay

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

On 27 October 2010 14:11, Ethan Rosenberg wrote:
> Dear List -
>
> Thanks for all your excellent help.
>
> I am setting up a database for medical research, which will be conducted =
at
> various sites.  The sites will be identified by a letter {A,B,C ....=
}.  The
> medical record number [primary key]  will start at 1001 and incremen=
t by
> one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 ...=
....
> How do I do this?
>
> Do I need a separate database for each site?
>
> Ethan

I'd use an INSERT trigger to generate the value.

I use MS SQL - no idea what DB you are using - and so here are what I'd do =
....

Table: Sites
UniqueID int identity(1,1)
SiteCode char(1)
LastMedicalRecordNumber int default 0

Table:MedicalRecords
UniqueID int identity(1,1)
SiteID int // Foreign key to Sites.UniqueID
MedicalRecordNumber int default 0

The trigger would be something like [UNTESTED] ...

CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
UPDATE Sites
SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
WHERE UniqueID IN (Inserted.SiteID)

UPDATE MedicalRecords
SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
FROM
INSERTED
INNER JOIN
MedicalRecords ON INSERTED.UniqueID =3D MedicalRecords.UniqueID
INNER JOIN
Sites ON INSERTED.SiteID =3D Sites.UniqueID


The app need not have any part is assigning something as important as
the unqiue id of a row. That sort of integrity needs to be part of the
database.

The client app really wants to be as simple as possible. Using stored
procedures and views (which are tuned once by the SQL Server) benefit
the app in returning the required data faster and with less
utilisation. Compare that against every identical query being compiled
from scratch every single time.

If you want to put the SiteCode on the MedicalRecord rather than the
SiteID, you could. And then break the link between the MedicalRecords
and Site tables. The trigger would use the SiteCode to link rather
then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites.

As far as the app goes?

You tell it which of the available sites the medical record is for and
insert it (along with any other user supplied data). The integrity is
preserved by the DB. "Just doing my job, sir!"

Richard.

--=20
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

----- Original Message -----
From: "Richard Quadling"
To: "Ethan Rosenberg"
Cc: "php-db-lists.php.net"
Sent: Wednesday, October 27, 2010 4:10:52 PM GMT +01:00 Amsterdam / Berlin =
/ Bern / Rome / Stockholm / Vienna
Subject: Re: [PHP-DB] Incrementing Primary Key

On 27 October 2010 14:11, Ethan Rosenberg wrote:
> Dear List -
>
> Thanks for all your excellent help.
>
> I am setting up a database for medical research, which will be conducted =
at
> various sites.  The sites will be identified by a letter {A,B,C ....=
}.  The
> medical record number [primary key]  will start at 1001 and incremen=
t by
> one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 ...=
.....
> How do I do this?
>
> Do I need a separate database for each site?
>
> Ethan

I'd use an INSERT trigger to generate the value.

I use MS SQL - no idea what DB you are using - and so here are what I'd do =
.....

Table: Sites
UniqueID int identity(1,1)
SiteCode char(1)
LastMedicalRecordNumber int default 0

Table:MedicalRecords
UniqueID int identity(1,1)
SiteID int // Foreign key to Sites.UniqueID
MedicalRecordNumber int default 0

The trigger would be something like [UNTESTED] ...

CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
UPDATE Sites
SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
WHERE UniqueID IN (Inserted.SiteID)

UPDATE MedicalRecords
SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
FROM
INSERTED
INNER JOIN
MedicalRecords ON INSERTED.UniqueID =3D MedicalRecords.UniqueID
INNER JOIN
Sites ON INSERTED.SiteID =3D Sites.UniqueID


The app need not have any part is assigning something as important as
the unqiue id of a row. That sort of integrity needs to be part of the
database.

The client app really wants to be as simple as possible. Using stored
procedures and views (which are tuned once by the SQL Server) benefit
the app in returning the required data faster and with less
utilisation. Compare that against every identical query being compiled
from scratch every single time.

If you want to put the SiteCode on the MedicalRecord rather than the
SiteID, you could. And then break the link between the MedicalRecords
and Site tables. The trigger would use the SiteCode to link rather
then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites.

As far as the app goes?

You tell it which of the available sites the medical record is for and
insert it (along with any other user supplied data). The integrity is
preserved by the DB. "Just doing my job, sir!"

Richard.

--=20
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Well pointed out Richard,=20

The App should be as simple as possible and should not try to replicate thi=
ngs are done easily by databases.=20

Using stored procedures for all your CRUD and data integrity checks will sa=
ve you quite a lot of time.=20

with kind regards,

Max.
------------------------------------------------
Max Kimambo
Franz-Stenzer-Straße, 51=20
12679, Berlin.
T: +493057706550 (new number)
M: +4917649520175
------------------------------------------------

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

At 10:10 AM 10/27/2010, Richard Quadling wrote:
>On 27 October 2010 14:11, Ethan Rosenberg wrote:
> > Dear List -
> >
> > Thanks for all your excellent help.
> >
> > I am setting up a database for medical research, which will be conducted=
at
> > various sites. =C2 The sites will be identified=20
> by a letter {A,B,C ....}. =C2 The
> > medical record number [primary key] =C2 will start at 1001 and increment=
by
> > one(1) for each patient at each site; ie, A=20
> 1001, A1002, B1001, B1002 ......
> > How do I do this?
> >
> > Do I need a separate database for each site?
> >
> > Ethan
>
>I'd use an INSERT trigger to generate the value.
>
>I use MS SQL - no idea what DB you are using - and so here are what I'd do=
...
>
>Table: Sites
> UniqueID int identity(1,1)
> SiteCode char(1)
> LastMedicalRecordNumber int default 0
>
>Table:MedicalRecords
> UniqueID int identity(1,1)
> SiteID int // Foreign key to Sites.UniqueID
> MedicalRecordNumber int default 0
>
>The trigger would be something like [UNTESTED] ...
>
>CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
> UPDATE Sites
> SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
> WHERE UniqueID IN (Inserted.SiteID)
>
> UPDATE MedicalRecords
> SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
> FROM
> INSERTED
> INNER JOIN
> MedicalRecords ON INSERTED.UniqueID =3D MedicalRecords.UniqueID
> INNER JOIN
> Sites ON INSERTED.SiteID =3D Sites.UniqueID
>
>
>The app need not have any part is assigning something as important as
>the unqiue id of a row. That sort of integrity needs to be part of the
>database.
>
>The client app really wants to be as simple as possible. Using stored
>procedures and views (which are tuned once by the SQL Server) benefit
>the app in returning the required data faster and with less
>utilisation. Compare that against every identical query being compiled
>from scratch every single time.
>
>If you want to put the SiteCode on the MedicalRecord rather than the
>SiteID, you could. And then break the link between the MedicalRecords
>and Site tables. The trigger would use the SiteCode to link rather
>then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites.
>
>As far as the app goes?
>
>You tell it which of the available sites the medical record is for and
>insert it (along with any other user supplied data). The integrity is
>preserved by the DB. "Just doing my job, sir!"
>
>Richard.
>
>--
>Richard Quadling
>Twitter : EE : Zend
>@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

Thank you.

I'm using MySQL, which I do not think has the=20
ability to auto-increment a primary key from any value other than 1.

Here is some pseudo code. Please help me to set it up properly.

Table Intake
SiteID char(2) primary key not null, //=20
This is A for site1, B for site 2....
RecordNum int(10,0) primary key not=20
null auto_increment, // increment starts from 10001
etc.....

Thanks

Ethan

MySQL 5.1 PHP 5 Linux [Debian (sid)]=20



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

From: "Ethan Rosenberg"
To: RQuadling@googlemail.com, "Ethan Rosenberg"
Cc: "php-db-lists.php.net"
Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin /=
Bern / Rome / Stockholm / Vienna
Subject: Re: [PHP-DB] Incrementing Primary Key

At 10:10 AM 10/27/2010, Richard Quadling wrote:
>On 27 October 2010 14:11, Ethan Rosenberg wrote:
> > Dear List -
> >
> > Thanks for all your excellent help.
> >
> > I am setting up a database for medical research, which will be conducte=
d at
> > various sites. Â The sites will be identified=20
> by a letter {A,B,C ....}. Â The
> > medical record number [primary key] Â will start at 1001 and incre=
ment by
> > one(1) for each patient at each site; ie, A=20
> 1001, A1002, B1001, B1002 ......
> > How do I do this?
> >
> > Do I need a separate database for each site?
> >
> > Ethan
>
>I'd use an INSERT trigger to generate the value.
>
>I use MS SQL - no idea what DB you are using - and so here are what I'd do=
...
>
>Table: Sites
> UniqueID int identity(1,1)
> SiteCode char(1)
> LastMedicalRecordNumber int default 0
>
>Table:MedicalRecords
> UniqueID int identity(1,1)
> SiteID int // Foreign key to Sites.UniqueID
> MedicalRecordNumber int default 0
>
>The trigger would be something like [UNTESTED] ...
>
>CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
> UPDATE Sites
> SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
> WHERE UniqueID IN (Inserted.SiteID)
>
> UPDATE MedicalRecords
> SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
> FROM
> INSERTED
> INNER JOIN
> MedicalRecords ON INSERTED.UniqueID =3D MedicalRecords.UniqueID
> INNER JOIN
> Sites ON INSERTED.SiteID =3D Sites.UniqueID
>
>
>The app need not have any part is assigning something as important as
>the unqiue id of a row. That sort of integrity needs to be part of the
>database.
>
>The client app really wants to be as simple as possible. Using stored
>procedures and views (which are tuned once by the SQL Server) benefit
>the app in returning the required data faster and with less
>utilisation. Compare that against every identical query being compiled
>from scratch every single time.
>
>If you want to put the SiteCode on the MedicalRecord rather than the
>SiteID, you could. And then break the link between the MedicalRecords
>and Site tables. The trigger would use the SiteCode to link rather
>then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites.
>
>As far as the app goes?
>
>You tell it which of the available sites the medical record is for and
>insert it (along with any other user supplied data). The integrity is
>preserved by the DB. "Just doing my job, sir!"
>
>Richard.
>
>--
>Richard Quadling
>Twitter : EE : Zend
>@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

Thank you.

I'm using MySQL, which I do not think has the=20
ability to auto-increment a primary key from any value other than 1.

Here is some pseudo code. Please help me to set it up properly.

Table Intake
SiteID char(2) primary key not null, //=20
This is A for site1, B for site 2....
RecordNum int(10,0) primary key not=20
null auto_increment, // increment starts from 10001
etc.....

Thanks

Ethan

MySQL 5.1 PHP 5 Linux [Debian (sid)]=20



Hi Ethan,=20

This will set a new auto increment value for a table .=20

ALTER TABLE RecordNum AUTO_INCREMENT=3D1001

Regards,=20

Max.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

At 03:40 AM 10/28/2010, Max E.K wrote:

>From: "Ethan Rosenberg"
>To: RQuadling@googlemail.com, "Ethan Rosenberg"
>Cc: "php-db-lists.php.net"
>Sent: Thursday, October 28, 2010 4:55:34 AM GMT=20
>+01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna
>Subject: Re: [PHP-DB] Incrementing Primary Key
>
>At 10:10 AM 10/27/2010, Richard Quadling wrote:
> >On 27 October 2010 14:11, Ethan Rosenberg wrote:
> > > Dear List -
> > >
> > > Thanks for all your excellent help.
> > >
> > > I am setting up a database for medical=20
> research, which will be conducted at
> > > various sites. Â The sites will be identified
> > by a letter {A,B,C ....}. Â The
> > > medical record number [primary key] Â will=20
> start at 1001 and increment by
> > > one(1) for each patient at each site; ie, A
> > 1001, A1002, B1001, B1002 ......
> > > How do I do this?
> > >
> > > Do I need a separate database for each site?
> > >
> > > Ethan
> >
> >I'd use an INSERT trigger to generate the value.
> >
> >I use MS SQL - no idea what DB you are using -=20
> and so here are what I'd do ...
> >
> >Table: Sites
> > UniqueID int identity(1,1)
> > SiteCode char(1)
> > LastMedicalRecordNumber int default 0
> >
> >Table:MedicalRecords
> > UniqueID int identity(1,1)
> > SiteID int // Foreign key to Sites.UniqueID
> > MedicalRecordNumber int default 0
> >
> >The trigger would be something like [UNTESTED] ...
> >
> >CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
> > UPDATE Sites
> > SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
> > WHERE UniqueID IN (Inserted.SiteID)
> >
> > UPDATE MedicalRecords
> > SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
> > FROM
> > INSERTED
> > INNER JOIN
> > MedicalRecords ON INSERTED.UniqueID =3D MedicalRecords.UniqueID
> > INNER JOIN
> > Sites ON INSERTED.SiteID =3D Sites.UniqueID
> >
> >
> >The app need not have any part is assigning something as important as
> >the unqiue id of a row. That sort of integrity needs to be part of the
> >database.
> >
> >The client app really wants to be as simple as possible. Using stored
> >procedures and views (which are tuned once by the SQL Server) benefit
> >the app in returning the required data faster and with less
> >utilisation. Compare that against every identical query being compiled
> >from scratch every single time.
> >
> >If you want to put the SiteCode on the MedicalRecord rather than the
> >SiteID, you could. And then break the link between the MedicalRecords
> >and Site tables. The trigger would use the SiteCode to link rather
> >then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites.
> >
> >As far as the app goes?
> >
> >You tell it which of the available sites the medical record is for and
> >insert it (along with any other user supplied data). The integrity is
> >preserved by the DB. "Just doing my job, sir!"
> >
> >Richard.
> >
> >--
> >Richard Quadling
> >Twitter : EE : Zend
> >@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
>
>Thank you.
>
>I'm using MySQL, which I do not think has the
>ability to auto-increment a primary key from any value other than 1.
>
>Here is some pseudo code. Please help me to set it up properly.
>
>Table Intake
> SiteID char(2) primary key not null, //
>This is A for site1, B for site 2....
> RecordNum int(10,0) primary key not
>null auto_increment, // increment starts from 10001
> etc.....
>
>Thanks
>
>Ethan
>
>MySQL 5.1 PHP 5 Linux [Debian (sid)]
>
>
>
>Hi Ethan,
>
>This will set a new auto increment value for a table .
>
>ALTER TABLE RecordNum AUTO_INCREMENT=3D1001
>
>Regards,
>
>Max.
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php

==========
Max -

Thanks.

I must be doing something wrong, since the=20
RecordNum starts from 1, and increments by=20
1. Maybe I am setting up the table incorrectly?

Ethan

MySQL 5.1 PHP 5 Linux [Debian (sid)]=20



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg wro=
te:
> At 03:40 AM 10/28/2010, Max E.K wrote:
>
>> From: "Ethan Rosenberg"
>> To: RQuadling@googlemail.com, "Ethan Rosenberg"
>> Cc: "php-db-lists.php.net"
>> Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berli=
n
>> / Bern / Rome / Stockholm / Vienna
>> Subject: Re: [PHP-DB] Incrementing Primary Key
>>
>> At 10:10 AM 10/27/2010, Richard Quadling wrote:
>> >On 27 October 2010 14:11, Ethan Rosenberg wrote:
>> > > Dear List -
>> > >
>> > > Thanks for all your excellent help.
>> > >
>> > > I am setting up a database for medical research, which will be
>> > > conducted at
>> > > various sites. Â The sites will be identified
>> > by a letter {A,B,C ....}. Â The
>> > > medical record number [primary key] Â will start at 1001 and
>> > > increment by
>> > > one(1) for each patient at each site; ie, A
>> > 1001, A1002, B1001, B1002 ......
>> > > How do I do this?
>> > >
>> > > Do I need a separate database for each site?
>> > >
>> > > Ethan
>> >
>> >I'd use an INSERT trigger to generate the value.
>> >
>> >I use MS SQL - no idea what DB you are using - and so here are what I'd
>> > do ...
>> >
>> >Table: Sites
>> > =A0UniqueID int identity(1,1)
>> > =A0SiteCode char(1)
>> > =A0LastMedicalRecordNumber int default 0
>> >
>> >Table:MedicalRecords
>> > =A0UniqueID int identity(1,1)
>> > =A0SiteID int // Foreign key to Sites.UniqueID
>> > =A0MedicalRecordNumber int default 0
>> >
>> >The trigger would be something like [UNTESTED] ...
>> >
>> >CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
>> > =A0UPDATE Sites
>> > =A0 SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
>> > =A0 WHERE UniqueID IN (Inserted.SiteID)
>> >
>> > =A0UPDATE MedicalRecords
>> > =A0 SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
>> > =A0 FROM
>> > =A0 =A0INSERTED
>> > =A0 =A0INNER JOIN
>> > =A0 =A0MedicalRecords ON INSERTED.UniqueID =3D MedicalRecords.UniqueID
>> > =A0 =A0INNER JOIN
>> > =A0 =A0Sites ON INSERTED.SiteID =3D Sites.UniqueID
>> >
>> >
>> >The app need not have any part is assigning something as important as
>> >the unqiue id of a row. That sort of integrity needs to be part of the
>> >database.
>> >
>> >The client app really wants to be as simple as possible. Using stored
>> >procedures and views (which are tuned once by the SQL Server) benefit
>> >the app in returning the required data faster and with less
>> >utilisation. Compare that against every identical query being compiled
>> >from scratch every single time.
>> >
>> >If you want to put the SiteCode on the MedicalRecord rather than the
>> >SiteID, you could. And then break the link between the MedicalRecords
>> >and Site tables. The trigger would use the SiteCode to link rather
>> >then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites.
>> >
>> >As far as the app goes?
>> >
>> >You tell it which of the available sites the medical record is for and
>> >insert it (along with any other user supplied data). The integrity is
>> >preserved by the DB. "Just doing my job, sir!"
>> >
>> >Richard.
>> >
>> >--
>> >Richard Quadling
>> >Twitter : EE : Zend
>> >@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
>>
>> Thank you.
>>
>> I'm using MySQL, which I do not think has the
>> ability to auto-increment a primary key from any value other than 1.
>>
>> Here is some pseudo code. =A0Please help me to set it up properly.
>>
>> Table Intake
>> =A0 =A0 =A0 =A0 SiteID char(2) primary key not null, //
>> This is A for site1, B for site 2....
>> =A0 =A0 =A0 =A0 RecordNum =A0int(10,0) primary key not
>> null auto_increment, // increment starts from 10001
>> =A0 =A0 =A0 =A0 etc.....
>>
>> Thanks
>>
>> Ethan
>>
>> MySQL 5.1 =A0PHP 5 =A0Linux [Debian (sid)]
>>
>>
>>
>> Hi Ethan,
>>
>> This will set a new auto increment value for a table .
>>
>> ALTER TABLE RecordNum AUTO_INCREMENT=3D1001
>>
>> Regards,
>>
>> Max.
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>
> ==========
> Max -
>
> Thanks.
>
> I must be doing something wrong, since the RecordNum starts from 1, and
> increments by 1. =A0Maybe I am setting up the table incorrectly?
>
> Ethan
>
> MySQL 5.1 =A0PHP 5 =A0Linux [Debian (sid)]
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

see http://dev.mysql.com/doc/refman/5.0/en/example-auto-incremen t.html

To set an auto increment start value

ALTER TABLE tbl AUTO_INCREMENT =3D 100;



--=20

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

At 01:17 PM 10/28/2010, Bastien Koert wrote:
>On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg =
wrote:
> > At 03:40 AM 10/28/2010, Max E.K wrote:
> >
> >> From: "Ethan Rosenberg"
> >> To: RQuadling@googlemail.com, "Ethan Rosenberg"
> >> Cc: "php-db-lists.php.net"
> >> Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam /=
Berlin
> >> / Bern / Rome / Stockholm / Vienna
> >> Subject: Re: [PHP-DB] Incrementing Primary Key
> >>
> >> At 10:10 AM 10/27/2010, Richard Quadling wrote:
> >> >On 27 October 2010 14:11, Ethan Rosenberg =
wrote:
> >> > > Dear List -
> >> > >
> >> > > Thanks for all your excellent help.
> >> > >
> >> > > I am setting up a database for medical research, which will be
> >> > > conducted at
> >> > > various sites. Â The sites will be identified
> >> > by a letter {A,B,C ....}. Â The
> >> > > medical record number [primary key] Â will start at 1001 and
> >> > > increment by
> >> > > one(1) for each patient at each site; ie, A
> >> > 1001, A1002, B1001, B1002 ......
> >> > > How do I do this?
> >> > >
> >> > > Do I need a separate database for each site?
> >> > >
> >> > > Ethan
> >> >
> >> >I'd use an INSERT trigger to generate the value.
> >> >
> >> >I use MS SQL - no idea what DB you are using - and so here are what=
I'd
> >> > do ...
> >> >
> >> >Table: Sites
> >> > UniqueID int identity(1,1)
> >> > SiteCode char(1)
> >> > LastMedicalRecordNumber int default 0
> >> >
> >> >Table:MedicalRecords
> >> > UniqueID int identity(1,1)
> >> > SiteID int // Foreign key to Sites.UniqueID
> >> > MedicalRecordNumber int default 0
> >> >
> >> >The trigger would be something like [UNTESTED] ...
> >> >
> >> >CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
> >> > UPDATE Sites
> >> > SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
> >> > WHERE UniqueID IN (Inserted.SiteID)
> >> >
> >> > UPDATE MedicalRecords
> >> > SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
> >> > FROM
> >> > INSERTED
> >> > INNER JOIN
> >> > MedicalRecords ON INSERTED.UniqueID =3D MedicalRecords.UniqueID
> >> > INNER JOIN
> >> > Sites ON INSERTED.SiteID =3D Sites.UniqueID
> >> >
> >> >
> >> >The app need not have any part is assigning something as important as
> >> >the unqiue id of a row. That sort of integrity needs to be part of the
> >> >database.
> >> >
> >> >The client app really wants to be as simple as possible. Using stored
> >> >procedures and views (which are tuned once by the SQL Server) benefit
> >> >the app in returning the required data faster and with less
> >> >utilisation. Compare that against every identical query being compiled
> >> >from scratch every single time.
> >> >
> >> >If you want to put the SiteCode on the MedicalRecord rather than the
> >> >SiteID, you could. And then break the link between the MedicalRecords
> >> >and Site tables. The trigger would use the SiteCode to link rather
> >> >then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites.
> >> >
> >> >As far as the app goes?
> >> >
> >> >You tell it which of the available sites the medical record is for and
> >> >insert it (along with any other user supplied data). The integrity is
> >> >preserved by the DB. "Just doing my job, sir!"
> >> >
> >> >Richard.
> >> >
> >> >--
> >> >Richard Quadling
> >> >Twitter : EE : Zend
> >> >@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
> >>
> >> Thank you.
> >>
> >> I'm using MySQL, which I do not think has the
> >> ability to auto-increment a primary key from any value other than 1.
> >>
> >> Here is some pseudo code. Please help me to set it up properly.
> >>
> >> Table Intake
> >> SiteID char(2) primary key not null, //
> >> This is A for site1, B for site 2....
> >> RecordNum int(10,0) primary key not
> >> null auto_increment, // increment starts from 10001
> >> etc.....
> >>
> >> Thanks
> >>
> >> Ethan
> >>
> >> MySQL 5.1 PHP 5 Linux [Debian (sid)]
> >>
> >>
> >>
> >> Hi Ethan,
> >>
> >> This will set a new auto increment value for a table .
> >>
> >> ALTER TABLE RecordNum AUTO_INCREMENT=3D1001
> >>
> >> Regards,
> >>
> >> Max.
> >>
> >> --
> >> PHP Database Mailing List (http://www.php.net/)
> >> To unsubscribe, visit: http://www.php.net/unsub.php
> >
> > ==========
> > Max -
> >
> > Thanks.
> >
> > I must be doing something wrong, since the RecordNum starts from 1, and
> > increments by 1. Maybe I am setting up the table incorrectly?
> >
> > Ethan
> >
> > MySQL 5.1 PHP 5 Linux [Debian (sid)]
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>see: deleted because of spam filter.

>To set an auto increment start value
>
>ALTER TABLE tbl AUTO_INCREMENT =3D 100;
>
>Bastien
>
>Cat, the other other white meat


Bastien -

Thanks.

It still does not work.

This is what I have done to change the auto_increment:

drop exiting_table;//called intake
create table intake2 (Site char not null, Record=20
int(10) not null auto_increment, BMI int(2),primary key(Site,Record));
alter table intake2 auto_increment=3D1000;
insert into intake2 (Site,Record,BMI) values ('A',(null),15);
insert into intake2 (Site,Record,BMI) values ('A',(null),18);
insert into intake2 (Site,Record,BMI) values ('A',(null),13);
mysql> select * from intake2;
+------+--------+------+
| Site | Record | BMI |
+------+--------+------+
| A | 1 | 15 |
| A | 2 | 18 |
| A | 3 | 19 |
+------+--------+------+
3 rows in set (0.00 sec)

What is my mistake?

Ethan

MySQL 5.1 PHP 5 Linux [Debian (sid)]=20



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Incrementing Primary Key

The () around null seem like that could be the issue, just try to put =
NULL
for the value

-----Original Message-----
From: Ethan Rosenberg [mailto:ethros@earthlink.net]=20
Sent: Thursday, October 28, 2010 2:02 PM
To: Bastien Koert
Cc: Max E.K; php-db-lists.php.net; RQuadling@googlemail.com
Subject: Re: [PHP-DB] Incrementing Primary Key

At 01:17 PM 10/28/2010, Bastien Koert wrote:
>On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg
wrote:
> > At 03:40 AM 10/28/2010, Max E.K wrote:
> >
> >> From: "Ethan Rosenberg"
> >> To: RQuadling@googlemail.com, "Ethan Rosenberg"=20
> >>
> >> Cc: "php-db-lists.php.net"
> >> Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam /=20
> >> Berlin / Bern / Rome / Stockholm / Vienna
> >> Subject: Re: [PHP-DB] Incrementing Primary Key
> >>
> >> At 10:10 AM 10/27/2010, Richard Quadling wrote:
> >> >On 27 October 2010 14:11, Ethan Rosenberg
wrote:
> >> > > Dear List -
> >> > >
> >> > > Thanks for all your excellent help.
> >> > >
> >> > > I am setting up a database for medical research, which will be=20
> >> > > conducted at various sites. Â The sites will be identified
> >> > by a letter {A,B,C ....}. Â The
> >> > > medical record number [primary key] Â will start at 1001 =
and=20
> >> > > increment by
> >> > > one(1) for each patient at each site; ie, A
> >> > 1001, A1002, B1001, B1002 ......
> >> > > How do I do this?
> >> > >
> >> > > Do I need a separate database for each site?
> >> > >
> >> > > Ethan
> >> >
> >> >I'd use an INSERT trigger to generate the value.
> >> >
> >> >I use MS SQL - no idea what DB you are using - and so here are=20
> >> >what I'd do ...
> >> >
> >> >Table: Sites
> >> > UniqueID int identity(1,1)
> >> > SiteCode char(1)
> >> > LastMedicalRecordNumber int default 0
> >> >
> >> >Table:MedicalRecords
> >> > UniqueID int identity(1,1)
> >> > SiteID int // Foreign key to Sites.UniqueID
> >> > MedicalRecordNumber int default 0
> >> >
> >> >The trigger would be something like [UNTESTED] ...
> >> >
> >> >CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
> >> > UPDATE Sites
> >> > SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
> >> > WHERE UniqueID IN (Inserted.SiteID)
> >> >
> >> > UPDATE MedicalRecords
> >> > SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
> >> > FROM
> >> > INSERTED
> >> > INNER JOIN
> >> > MedicalRecords ON INSERTED.UniqueID =3D =
MedicalRecords.UniqueID
> >> > INNER JOIN
> >> > Sites ON INSERTED.SiteID =3D Sites.UniqueID
> >> >
> >> >
> >> >The app need not have any part is assigning something as important =

> >> >as the unqiue id of a row. That sort of integrity needs to be part =

> >> >of the database.
> >> >
> >> >The client app really wants to be as simple as possible. Using=20
> >> >stored procedures and views (which are tuned once by the SQL=20
> >> >Server) benefit the app in returning the required data faster and=20
> >> >with less utilisation. Compare that against every identical query=20
> >> >being compiled from scratch every single time.
> >> >
> >> >If you want to put the SiteCode on the MedicalRecord rather than=20
> >> >the SiteID, you could. And then break the link between the=20
> >> >MedicalRecords and Site tables. The trigger would use the SiteCode =

> >> >to link rather then the SiteID / Sites.UniqueId to get INSERTED
connecting to Sites.
> >> >
> >> >As far as the app goes?
> >> >
> >> >You tell it which of the available sites the medical record is for =

> >> >and insert it (along with any other user supplied data). The=20
> >> >integrity is preserved by the DB. "Just doing my job, sir!"
> >> >
> >> >Richard.
> >> >
> >> >--
> >> >Richard Quadling
> >> >Twitter : EE : Zend
> >> >@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
> >>
> >> Thank you.
> >>
> >> I'm using MySQL, which I do not think has the ability to=20
> >> auto-increment a primary key from any value other than 1.
> >>
> >> Here is some pseudo code. Please help me to set it up properly.
> >>
> >> Table Intake
> >> SiteID char(2) primary key not null, // This is A for=20
> >> site1, B for site 2....
> >> RecordNum int(10,0) primary key not null auto_increment,=20
> >> // increment starts from 10001
> >> etc.....
> >>
> >> Thanks
> >>
> >> Ethan
> >>
> >> MySQL 5.1 PHP 5 Linux [Debian (sid)]
> >>
> >>
> >>
> >> Hi Ethan,
> >>
> >> This will set a new auto increment value for a table .
> >>
> >> ALTER TABLE RecordNum AUTO_INCREMENT=3D1001
> >>
> >> Regards,
> >>
> >> Max.
> >>
> >> --
> >> PHP Database Mailing List (http://www.php.net/) To unsubscribe,=20
> >> visit: http://www.php.net/unsub.php
> >
> > ==========
> > Max -
> >
> > Thanks.
> >
> > I must be doing something wrong, since the RecordNum starts from 1,=20
> > and increments by 1. Maybe I am setting up the table incorrectly?
> >
> > Ethan
> >
> > MySQL 5.1 PHP 5 Linux [Debian (sid)]
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/) To unsubscribe,=20
> > visit: http://www.php.net/unsub.php
> >
> >
>
>see: deleted because of spam filter.

>To set an auto increment start value
>
>ALTER TABLE tbl AUTO_INCREMENT =3D 100;
>
>Bastien
>
>Cat, the other other white meat


Bastien -

Thanks.

It still does not work.

This is what I have done to change the auto_increment:

drop exiting_table;//called intake
create table intake2 (Site char not null, Record
int(10) not null auto_increment, BMI int(2),primary key(Site,Record));
alter table intake2 auto_increment=3D1000;
insert into intake2 (Site,Record,BMI) values ('A',(null),15);
insert into intake2 (Site,Record,BMI) values ('A',(null),18);
insert into intake2 (Site,Record,BMI) values ('A',(null),13);
mysql> select * from intake2;
+------+--------+------+
| Site | Record | BMI |
+------+--------+------+
| A | 1 | 15 |
| A | 2 | 18 |
| A | 3 | 19 |
+------+--------+------+
3 rows in set (0.00 sec)

What is my mistake?

Ethan

MySQL 5.1 PHP 5 Linux [Debian (sid)]=20



--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Incrementing Primary Key

Actually, after looking at it further, you have the records set to only =
have
not null values yet you are passing a null value to it.

-----Original Message-----
From: Ethan Rosenberg [mailto:ethros@earthlink.net]=20
Sent: Thursday, October 28, 2010 2:02 PM
To: Bastien Koert
Cc: Max E.K; php-db-lists.php.net; RQuadling@googlemail.com
Subject: Re: [PHP-DB] Incrementing Primary Key

At 01:17 PM 10/28/2010, Bastien Koert wrote:
>On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg
wrote:
> > At 03:40 AM 10/28/2010, Max E.K wrote:
> >
> >> From: "Ethan Rosenberg"
> >> To: RQuadling@googlemail.com, "Ethan Rosenberg"=20
> >>
> >> Cc: "php-db-lists.php.net"
> >> Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam /=20
> >> Berlin / Bern / Rome / Stockholm / Vienna
> >> Subject: Re: [PHP-DB] Incrementing Primary Key
> >>
> >> At 10:10 AM 10/27/2010, Richard Quadling wrote:
> >> >On 27 October 2010 14:11, Ethan Rosenberg
wrote:
> >> > > Dear List -
> >> > >
> >> > > Thanks for all your excellent help.
> >> > >
> >> > > I am setting up a database for medical research, which will be=20
> >> > > conducted at various sites. Â The sites will be identified
> >> > by a letter {A,B,C ....}. Â The
> >> > > medical record number [primary key] Â will start at 1001 =
and=20
> >> > > increment by
> >> > > one(1) for each patient at each site; ie, A
> >> > 1001, A1002, B1001, B1002 ......
> >> > > How do I do this?
> >> > >
> >> > > Do I need a separate database for each site?
> >> > >
> >> > > Ethan
> >> >
> >> >I'd use an INSERT trigger to generate the value.
> >> >
> >> >I use MS SQL - no idea what DB you are using - and so here are=20
> >> >what I'd do ...
> >> >
> >> >Table: Sites
> >> > UniqueID int identity(1,1)
> >> > SiteCode char(1)
> >> > LastMedicalRecordNumber int default 0
> >> >
> >> >Table:MedicalRecords
> >> > UniqueID int identity(1,1)
> >> > SiteID int // Foreign key to Sites.UniqueID
> >> > MedicalRecordNumber int default 0
> >> >
> >> >The trigger would be something like [UNTESTED] ...
> >> >
> >> >CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
> >> > UPDATE Sites
> >> > SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
> >> > WHERE UniqueID IN (Inserted.SiteID)
> >> >
> >> > UPDATE MedicalRecords
> >> > SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
> >> > FROM
> >> > INSERTED
> >> > INNER JOIN
> >> > MedicalRecords ON INSERTED.UniqueID =3D =
MedicalRecords.UniqueID
> >> > INNER JOIN
> >> > Sites ON INSERTED.SiteID =3D Sites.UniqueID
> >> >
> >> >
> >> >The app need not have any part is assigning something as important =

> >> >as the unqiue id of a row. That sort of integrity needs to be part =

> >> >of the database.
> >> >
> >> >The client app really wants to be as simple as possible. Using=20
> >> >stored procedures and views (which are tuned once by the SQL=20
> >> >Server) benefit the app in returning the required data faster and=20
> >> >with less utilisation. Compare that against every identical query=20
> >> >being compiled from scratch every single time.
> >> >
> >> >If you want to put the SiteCode on the MedicalRecord rather than=20
> >> >the SiteID, you could. And then break the link between the=20
> >> >MedicalRecords and Site tables. The trigger would use the SiteCode =

> >> >to link rather then the SiteID / Sites.UniqueId to get INSERTED
connecting to Sites.
> >> >
> >> >As far as the app goes?
> >> >
> >> >You tell it which of the available sites the medical record is for =

> >> >and insert it (along with any other user supplied data). The=20
> >> >integrity is preserved by the DB. "Just doing my job, sir!"
> >> >
> >> >Richard.
> >> >
> >> >--
> >> >Richard Quadling
> >> >Twitter : EE : Zend
> >> >@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
> >>
> >> Thank you.
> >>
> >> I'm using MySQL, which I do not think has the ability to=20
> >> auto-increment a primary key from any value other than 1.
> >>
> >> Here is some pseudo code. Please help me to set it up properly.
> >>
> >> Table Intake
> >> SiteID char(2) primary key not null, // This is A for=20
> >> site1, B for site 2....
> >> RecordNum int(10,0) primary key not null auto_increment,=20
> >> // increment starts from 10001
> >> etc.....
> >>
> >> Thanks
> >>
> >> Ethan
> >>
> >> MySQL 5.1 PHP 5 Linux [Debian (sid)]
> >>
> >>
> >>
> >> Hi Ethan,
> >>
> >> This will set a new auto increment value for a table .
> >>
> >> ALTER TABLE RecordNum AUTO_INCREMENT=3D1001
> >>
> >> Regards,
> >>
> >> Max.
> >>
> >> --
> >> PHP Database Mailing List (http://www.php.net/) To unsubscribe,=20
> >> visit: http://www.php.net/unsub.php
> >
> > ==========
> > Max -
> >
> > Thanks.
> >
> > I must be doing something wrong, since the RecordNum starts from 1,=20
> > and increments by 1. Maybe I am setting up the table incorrectly?
> >
> > Ethan
> >
> > MySQL 5.1 PHP 5 Linux [Debian (sid)]
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/) To unsubscribe,=20
> > visit: http://www.php.net/unsub.php
> >
> >
>
>see: deleted because of spam filter.

>To set an auto increment start value
>
>ALTER TABLE tbl AUTO_INCREMENT =3D 100;
>
>Bastien
>
>Cat, the other other white meat


Bastien -

Thanks.

It still does not work.

This is what I have done to change the auto_increment:

drop exiting_table;//called intake
create table intake2 (Site char not null, Record
int(10) not null auto_increment, BMI int(2),primary key(Site,Record));
alter table intake2 auto_increment=3D1000;
insert into intake2 (Site,Record,BMI) values ('A',(null),15);
insert into intake2 (Site,Record,BMI) values ('A',(null),18);
insert into intake2 (Site,Record,BMI) values ('A',(null),13);
mysql> select * from intake2;
+------+--------+------+
| Site | Record | BMI |
+------+--------+------+
| A | 1 | 15 |
| A | 2 | 18 |
| A | 3 | 19 |
+------+--------+------+
3 rows in set (0.00 sec)

What is my mistake?

Ethan

MySQL 5.1 PHP 5 Linux [Debian (sid)]=20



--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Incrementing Primary Key

On Thu, Oct 28, 2010 at 2:14 PM, Jimmy Sole wrote:
> Actually, after looking at it further, you have the records set to only h=
ave
> not null values yet you are passing a null value to it.
>
> -----Original Message-----
> From: Ethan Rosenberg [mailto:ethros@earthlink.net]
> Sent: Thursday, October 28, 2010 2:02 PM
> To: Bastien Koert
> Cc: Max E.K; php-db-lists.php.net; RQuadling@googlemail.com
> Subject: Re: [PHP-DB] Incrementing Primary Key
>
> At 01:17 PM 10/28/2010, Bastien Koert wrote:
>>On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg
> wrote:
>> > At 03:40 AM 10/28/2010, Max E.K wrote:
>> >
>> >> From: "Ethan Rosenberg"
>> >> To: RQuadling@googlemail.com, "Ethan Rosenberg"
>> >>
>> >> Cc: "php-db-lists.php.net"
>> >> Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam /
>> >> Berlin / Bern / Rome / Stockholm / Vienna
>> >> Subject: Re: [PHP-DB] Incrementing Primary Key
>> >>
>> >> At 10:10 AM 10/27/2010, Richard Quadling wrote:
>> >> >On 27 October 2010 14:11, Ethan Rosenberg
> wrote:
>> >> > > Dear List -
>> >> > >
>> >> > > Thanks for all your excellent help.
>> >> > >
>> >> > > I am setting up a database for medical research, which will be
>> >> > > conducted at various sites. Â The sites will be identified
>> >> > by a letter {A,B,C ....}. Â The
>> >> > > medical record number [primary key] Â will start at 1001 and
>> >> > > increment by
>> >> > > one(1) for each patient at each site; ie, A
>> >> > 1001, A1002, B1001, B1002 ......
>> >> > > How do I do this?
>> >> > >
>> >> > > Do I need a separate database for each site?
>> >> > >
>> >> > > Ethan
>> >> >
>> >> >I'd use an INSERT trigger to generate the value.
>> >> >
>> >> >I use MS SQL - no idea what DB you are using - and so here are
>> >> >what I'd =A0do ...
>> >> >
>> >> >Table: Sites
>> >> > =A0UniqueID int identity(1,1)
>> >> > =A0SiteCode char(1)
>> >> > =A0LastMedicalRecordNumber int default 0
>> >> >
>> >> >Table:MedicalRecords
>> >> > =A0UniqueID int identity(1,1)
>> >> > =A0SiteID int // Foreign key to Sites.UniqueID
>> >> > =A0MedicalRecordNumber int default 0
>> >> >
>> >> >The trigger would be something like [UNTESTED] ...
>> >> >
>> >> >CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
>> >> > =A0UPDATE Sites
>> >> > =A0 SET LastMedicalRecordNumber =3D 1 + LastMedicalRecordNumber
>> >> > =A0 WHERE UniqueID IN (Inserted.SiteID)
>> >> >
>> >> > =A0UPDATE MedicalRecords
>> >> > =A0 SET MedicalRecordNumber =3D Sites.LastMedicalRecordNumber
>> >> > =A0 FROM
>> >> > =A0 =A0INSERTED
>> >> > =A0 =A0INNER JOIN
>> >> > =A0 =A0MedicalRecords ON INSERTED.UniqueID =3D MedicalRecords.Uniqu=
eID
>> >> > =A0 =A0INNER JOIN
>> >> > =A0 =A0Sites ON INSERTED.SiteID =3D Sites.UniqueID
>> >> >
>> >> >
>> >> >The app need not have any part is assigning something as important
>> >> >as the unqiue id of a row. That sort of integrity needs to be part
>> >> >of the database.
>> >> >
>> >> >The client app really wants to be as simple as possible. Using
>> >> >stored procedures and views (which are tuned once by the SQL
>> >> >Server) benefit the app in returning the required data faster and
>> >> >with less utilisation. Compare that against every identical query
>> >> >being compiled from scratch every single time.
>> >> >
>> >> >If you want to put the SiteCode on the MedicalRecord rather than
>> >> >the SiteID, you could. And then break the link between the
>> >> >MedicalRecords and Site tables. The trigger would use the SiteCode
>> >> >to link rather then the SiteID / Sites.UniqueId to get INSERTED
> connecting to Sites.
>> >> >
>> >> >As far as the app goes?
>> >> >
>> >> >You tell it which of the available sites the medical record is for
>> >> >and insert it (along with any other user supplied data). The
>> >> >integrity is preserved by the DB. "Just doing my job, sir!"
>> >> >
>> >> >Richard.
>> >> >
>> >> >--
>> >> >Richard Quadling
>> >> >Twitter : EE : Zend
>> >> >@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
>> >>
>> >> Thank you.
>> >>
>> >> I'm using MySQL, which I do not think has the ability to
>> >> auto-increment a primary key from any value other than 1.
>> >>
>> >> Here is some pseudo code. =A0Please help me to set it up properly.
>> >>
>> >> Table Intake
>> >> =A0 =A0 =A0 =A0 SiteID char(2) primary key not null, // This is A for
>> >> site1, B for site 2....
>> >> =A0 =A0 =A0 =A0 RecordNum =A0int(10,0) primary key not null auto_incr=
ement,
>> >> // increment starts from 10001
>> >> =A0 =A0 =A0 =A0 etc.....
>> >>
>> >> Thanks
>> >>
>> >> Ethan
>> >>
>> >> MySQL 5.1 =A0PHP 5 =A0Linux [Debian (sid)]
>> >>
>> >>
>> >>
>> >> Hi Ethan,
>> >>
>> >> This will set a new auto increment value for a table .
>> >>
>> >> ALTER TABLE RecordNum AUTO_INCREMENT=3D1001
>> >>
>> >> Regards,
>> >>
>> >> Max.
>> >>
>> >> --
>> >> PHP Database Mailing List (http://www.php.net/) To unsubscribe,
>> >> visit: http://www.php.net/unsub.php
>> >
>> > ==========
>> > Max -
>> >
>> > Thanks.
>> >
>> > I must be doing something wrong, since the RecordNum starts from 1,
>> > and increments by 1. =A0Maybe I am setting up the table incorrectly?
>> >
>> > Ethan
>> >
>> > MySQL 5.1 =A0PHP 5 =A0Linux [Debian (sid)]
>> >
>> >
>> > --
>> > PHP Database Mailing List (http://www.php.net/) To unsubscribe,
>> > visit: http://www.php.net/unsub.php
>> >
>> >
>>
>>see: deleted because of spam filter.
>
>>To set an auto increment start value
>>
>>ALTER TABLE tbl AUTO_INCREMENT =3D 100;
>>
>>Bastien
>>
>>Cat, the other other white meat
>
>
> Bastien -
>
> Thanks.
>
> It still does not work.
>
> This is what I have done to change the auto_increment:
>
> drop exiting_table;//called intake
> create table intake2 (Site char not null, Record
> int(10) not null auto_increment, BMI int(2),primary key(Site,Record));
> =A0alter table intake2 auto_increment=3D1000;
> =A0insert into intake2 (Site,Record,BMI) values ('A',(null),15);
> =A0insert into intake2 (Site,Record,BMI) values ('A',(null),18);
> =A0insert into intake2 (Site,Record,BMI) values ('A',(null),13);
> =A0mysql> select * from intake2;
> +------+--------+------+
> | Site | Record | BMI =A0|
> +------+--------+------+
> | A =A0 =A0| =A0 =A0 =A01 | =A0 15 |
> | A =A0 =A0| =A0 =A0 =A02 | =A0 18 |
> | A =A0 =A0| =A0 =A0 =A03 | =A0 19 |
> +------+--------+------+
> 3 rows in set (0.00 sec)
>
> What is my mistake?
>
> Ethan
>
> MySQL 5.1 =A0PHP 5 =A0Linux [Debian (sid)]
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
> http://www.php.net/unsub.php
>
>
>

I generally make the AI field the first one in the table

then just insert without referencing that field

=A0insert into intake2 (Site,BMI) values ('A',15);

Actually just try no referencing that field

--=20

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php