RE: [PHP-DB] 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] 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 <ethros@earthlink.net> wrote: > > At 03:40 AM 10/28/2010, Max E.K wrote: > > > >> From: "Ethan Rosenberg" <ethros@earthlink.net> > >> To: RQuadling@googlemail.com, "Ethan Rosenberg" > >> <ethros@earthlink.net> > >> Cc: "php-db-lists.php.net" <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 <ethros@earthlink.net> 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 = 1 + LastMedicalRecordNumber > >> > WHERE UniqueID IN (Inserted.SiteID) > >> > > >> > UPDATE MedicalRecords > >> > SET MedicalRecordNumber = Sites.LastMedicalRecordNumber > >> > FROM > >> > INSERTED > >> > INNER JOIN > >> > MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID > >> > INNER JOIN > >> > Sites ON INSERTED.SiteID = 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=1001 > >> > >> 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 = 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=1000; 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)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php