On Thu, Oct 28, 2010 at 2:14 PM, Jimmy Sole <jimmysole@xxxxxxxxx> wrote: > 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@xxxxxxxxxxxxx] > Sent: Thursday, October 28, 2010 2:02 PM > To: Bastien Koert > Cc: Max E.K; php-db-lists.php.net; RQuadling@xxxxxxxxxxxxxx > Subject: 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 <ethros@xxxxxxxxxxxxx> > wrote: >> > At 03:40 AM 10/28/2010, Max E.K wrote: >> > >> >> From: "Ethan Rosenberg" <ethros@xxxxxxxxxxxxx> >> >> To: RQuadling@xxxxxxxxxxxxxx, "Ethan Rosenberg" >> >> <ethros@xxxxxxxxxxxxx> >> >> Cc: "php-db-lists.php.net" <php-db@xxxxxxxxxxxxx> >> >> Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / >> >> Berlin / Bern / Rome / Stockholm / Vienna >> >> Subject: Re: Incrementing Primary Key >> >> >> >> At 10:10 AM 10/27/2010, Richard Quadling wrote: >> >> >On 27 October 2010 14:11, Ethan Rosenberg <ethros@xxxxxxxxxxxxx> > 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 > > > I generally make the AI field the first one in the table then just insert without referencing that field insert into intake2 (Site,BMI) values ('A',15); Actually just try no referencing that field -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
