create a sequence table. (Possibly in a third database?) sequence sequenceID bigint label varchar(25) NextValue bigint
Put 1 record in the table for each autoincrement field using the fieldname as the label. Then use it instead of the built-in autoincrement fields. begin; select @nextID:=NextValue from sequence; update sequence set NextValue = (NextValue+1); commit; ADODB does this (php.weblogs.com) but they create 1 table for each sequence. (for locking purposes) if you are using InnoDB tables (and why WOULDN'T you be?) then you can easily modify their code to use a single sequence table. (I did) HTH, =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -----Original Message----- From: Mike Patterson [mailto:mp72@;excite.com] Sent: Monday, November 04, 2002 1:19 PM To: [EMAIL PROTECTED] Subject: Maintaining #'s between 2 databases I have two databases (rt2 & jobs) and within each of these DBs tables that autoincrement email Ticket #'s and Work Request #'s respectively. I'm looking for the best way to maintain the numbers between the databases (e.g. if I had a rt2, jobs, then rt2 requests: rt2 issues ticket #1024, then jobs issues #1025, then rt2 issues #1026) More information: rt2 is the Request Tracker email ticketing system (http://www.bestpractical.com/rt) which uses Perl to insert tickets and "jobs" is a custom PHP front end for inserting tickets into a separate database (living on the same server, using same the mysql install) A messy solution would be one big database and one big table that uses the same autoindex key. I appreciate ideas on a better solution (e.g. Cross inserts?, a 3rd index table)? Thanks, Mike --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
