On Thu, Oct 28, 2010 at 2:14 PM, Jimmy Sole <[email protected]> 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:[email protected]]
> Sent: Thursday, October 28, 2010 2:02 PM
> To: Bastien Koert
> Cc: Max E.K; php-db-lists.php.net; [email protected]
> 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 <[email protected]>
> wrote:
>> > At 03:40 AM 10/28/2010, Max E.K wrote:
>> >
>> >> From: "Ethan Rosenberg" <[email protected]>
>> >> To: [email protected], "Ethan Rosenberg"
>> >> <[email protected]>
>> >> Cc: "php-db-lists.php.net" <[email protected]>
>> >> 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 <[email protected]>
> 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
