Re: [PHP-DB] MySQL Auto PK
On Wed, 05 Jan 2005 18:11:23 -0500, John Holmes [EMAIL PROTECTED] wrote: OOzy Pal wrote: Is it possible to have mysql at an ID as 20050105-1 as (MMDD-1), -2, etc. automatically? No. But you can always just use SELECT CONCAT(date_column,'-',pk_column) AS fixed_id ... if you _really_ need something like this. Or just join them together in PHP. I think the only downside to that solution is that the primary key will continue to increment regardless of the day, and I think the original poster wanted: 20050105-1 20050105-2 20050106-1 ...etc. This would be a great place for a stored procedure, but I don't know if I can recommend running MySQL 5 to you. The most platform-safe way I can think of is to get a count(*) of the number of rows with today's date, add 1 to it, and stick that number on the end of the string you've created to insert into a varchar field. It's an extra query per insert, but it'd do the job. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Auto PK
On Friday 07 January 2005 02:29, Andrew Kreps wrote: This would be a great place for a stored procedure, but I don't know if I can recommend running MySQL 5 to you. The most platform-safe way I can think of is to get a count(*) of the number of rows with today's date, add 1 to it, and stick that number on the end of the string you've created to insert into a varchar field. It's an extra query per insert, but it'd do the job. Don't forget to WRITE lock the table before counting, and only unlock after inserting the new id. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- New Year Resolution: Ignore top posted posts -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Auto PK
-Original Message- From: Andrew Kreps [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 12:30 PM Subject: Re: [PHP-DB] MySQL Auto PK On Wed, 05 Jan 2005 18:11:23 -0500, John Holmes [EMAIL PROTECTED] wrote: OOzy Pal wrote: Is it possible to have mysql at an ID as 20050105-1 as (MMDD-1), -2, etc. automatically? No. But you can always just use SELECT CONCAT(date_column,'-',pk_column) AS fixed_id ... if you _really_ need something like this. Or just join them together in PHP. I think the only downside to that solution is that the primary key will continue to increment regardless of the day, and I think the original poster wanted: 20050105-1 20050105-2 20050106-1 ...etc. This would be a great place for a stored procedure, but I don't know if I can recommend running MySQL 5 to you. The most platform-safe way I can think of is to get a count(*) of the number of rows with today's date, add 1 to it, and stick that number on the end of the string you've created to insert into a varchar field. It's an extra query per insert, but it'd do the job. Doing a count and tagging the number on the back could result in race conditions with a much larger window than one would prefer (albeit still small). Wouldn't tagging the time on the end of the date accomplish the overall goal? You can pull them from the database as is (with their full date/time - ordered by such) and then trim the date out (mysql could even do this for you) and tag on -$rownum. Still, no atomicity for guarantee of uniqueness. The primary key should be used solely for unique identification purposes, how it looks visually should be of little concern/interest - at least, given the data I've seen so far (e.g. it's just based on date/time). This really seems like a perfect candidate for an autoincrement and a separate date / datetime field. Cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Auto PK
Andrew Kreps wrote: ... 20050105-1 20050105-2 20050106-1 ...etc. This would be a great place for a stored procedure, but I don't know if I can recommend running MySQL 5 to you. The most platform-safe way an open source alternative that does offer this enterprise level functionality (stored procedures) is firebird. and its based on proven tech. (interbase), not to mention its not bleeding edge (firebird stayed in beta so long that if it had been a microsoft product it would have gone gold, shipped, and been put to pasture ;-). but firebird is a little more difficult to get into than MySQL and you may not have access to hosting that provides it. I can think of is to get a count(*) of the number of rows with today's date, add 1 to it, and stick that number on the end of the string you've created to insert into a varchar field. It's an extra query per insert, but it'd do the job. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Auto PK
OOzy Pal wrote: Dears, Is it possible to have mysql at an ID as 20050105-1 as (MMDD-1), -2, etc. probably, possibly, maybe. take your pick. as Martin Norland already pointed out to someone else; this is not [EMAIL PROTECTED] (that beauty of a comment is forever burned into my neo-cortex! :-) i.e. try a little more info... OOzy = Regards, OOzy What is the purpose of life? __ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Auto PK
OOzy Pal wrote: Is it possible to have mysql at an ID as 20050105-1 as (MMDD-1), -2, etc. automatically? No. But you can always just use SELECT CONCAT(date_column,'-',pk_column) AS fixed_id ... if you _really_ need something like this. Or just join them together in PHP. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
