Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    12

    Unanswered: Autonumber assignements

    I am trying to find a way to get the autonumber of a table as soon as its entered into the database. How can this be done?

    Here is an example and what I have tried:
    I have a table that has a one to zero or one relationship with another table. At entry time if the item is of a type computer the user has to enter additional data about the item. I am pretty sure the insert has to be done in three(maybe two) stages. One: insert the asset. Two: get the just inserted asset number. Three: insert the detail. The problem is that I need to get the asset number of the item that was just inserted.

    I have been using: SELECT assetID FROM asset ORDER BY assetID DESC; The first record returned will be the last one added. The problem is that what if another user adds a record at the same time? however unlikely it will eventually happen and it would return the wrong ID. How can I prevent that? Trigger maybe?

    Here are the tables.
    Table1:
    Code:
    CREATE TABLE `asset` (
      `AssetID` int(10) unsigned NOT NULL auto_increment,
      `assignedEID` int(10) unsigned default NULL,
      `pdate` date default NULL,
      `MFGID` int(10) unsigned NOT NULL,
      `PTID` smallint(5) unsigned NOT NULL,
      `ADesc` text collate latin1_general_ci,
      PRIMARY KEY  (`AssetID`),
      KEY `FK_Asset_Employee` (`assignedEID`),
      KEY `FK_Asset_prodtype` (`PTID`),
      KEY `FK_Asset_mfg` (`MFGID`),
      CONSTRAINT `FK_asset_mfg` FOREIGN KEY (`MFGID`) REFERENCES `manufacturer` (`ManufacturerID`) ON UPDATE CASCADE,
      CONSTRAINT `FK_asset_prodtype` FOREIGN KEY (`PTID`) REFERENCES `producttypes` (`ProductType`) ON UPDATE CASCADE,
      CONSTRAINT `FK_Asset_Employee` FOREIGN KEY (`assignedEID`) REFERENCES `employee` (`eid`) ON DELETE SET NULL ON UPDATE CASCADE
    )
    ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
    Table2
    Code:
    CREATE TABLE `computerdetail` (
      `assetID` int(10) unsigned NOT NULL,
      `CPU` varchar(20) collate latin1_general_ci NOT NULL,
      `Ram` varchar(12) collate latin1_general_ci NOT NULL,
      `HDD` varchar(12) collate latin1_general_ci NOT NULL,
      `OS` smallint(4) unsigned NOT NULL,
      `VirusScan` varchar(15) collate latin1_general_ci default NULL,
      `office` varchar(20) collate latin1_general_ci default NULL,
      `VidCard` varchar(20) collate latin1_general_ci default NULL,
      `SndCard` varchar(20) collate latin1_general_ci default NULL,
      PRIMARY KEY  USING BTREE (`assetID`),
      CONSTRAINT `FK_ComputerDetail_asset` FOREIGN KEY (`assetID`) REFERENCES `asset` (`AssetID`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what fornt end language are you using. .usually there is a function in that language which will retrieve the last ID
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the mysql LAST_INSERT_ID() function

    if it's php, use mysql_insert_id()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2007
    Posts
    12
    Thanks r937, I didn't know about the LAST_INSERT_ID() function.

    The second part of my question I am still not sure about. What if two (or more) people insert a record at the same time? Wont that function return the same answer to all of them?

    Just thinking aloud would this prevent that:
    set {Variable} = INSERT INTO asset (...) VALUES (...); LAST_INSERT_ID(); I'd guess that the return would only be the second part of the statement thus would return the value as its being entered. Please Let me know.

    Thanks,
    -Brian

    Using VB and ADO for the front end.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you don't need to do anything about it, mysql takes care of it

    it's fully explained in the docs

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •