Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    Unanswered: problem with mysql_insert_id()

    I have an odd problem with mysql_insert_id()
    its stopped working.. it returns a value of 0

    MySQL version is 5.0.18-nt
    running on windows XP Home
    MySQL Client 5.11
    php version is 4.4.4

    ..heres the really odd thing it used to work fine

    the PHP code is
    PHP Code:
    $strSQL "insert into DT_TX (Country_ID, pRateID) values ($pCountry, 32)";    
    $sqlr = @mysql_query($strSQL$cnn);
    //get the transaction ID
    $pTXID mysql_insert_id();
    if (
    $pTXID == 0) {die ("Transaction ID=0!<BR>$strSQL<BR>".date("H:i:s")."<BR>Mysql said:".@mysql_insert_id()."<BR>ErroNo:".mysql_errno()."<BR>ErroNo:".mysql_error());} 
    Ive been staring at this for around 3..4 hours off and on.. it must be a slaphead moment but i cannot see why it isn't working

    I've checked the MYSQL site
    the only thing thats changed is that I inserted some new SQL upstream of this request. I re use the same connection / link identifier in the script

    any suggestions
    I'd rather be riding on the Tiger 800 or the Norton

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    mysql_insert_id would only have a value if the previous INSERT query is successful. Your query has both an @ to suppress any error message output and it does not have any error checking to see if the function failed. What are the chances that the mysql_query is failing?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..thanks for the response

    in short none..
    the error handler was deliberately switched off in the code posted here to save space & hide the gory details of how I handle errors on the site
    the records are being inserted.. that's the real head scratcher here in my books
    my code is doing what I expect.. inserting a record its just the insert_id isn't returning a value, or is returning a 0

    DDL is as follows
    -- MySQL Administrator dump 1.4
    --
    -- ------------------------------------------------------
    -- Server version 5.0.18-nt
    -- Definition of table `dt_tx`
    --DROP TABLE IF EXISTS `dt_tx`;
    CREATE TABLE `dt_tx` ( `TX_ID` int(10) unsigned NOT NULL auto_increment,
    `LastEdit` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    `Country_ID` smallint(6) default NULL,
    `pNoR` tinyint(4) NOT NULL default '30',
    `pPic` tinyint(4) NOT NULL default '1',
    `pDet` tinyint(4) NOT NULL default '0',
    `pRateID` int(11) default NULL,
    `ProtXFudge` text COMMENT 'This column exists as a workaround for the shortcomings in the ProtX system. Its theire becuase the lying tossers dont do what their docuemntation says it does.',
    PRIMARY KEY (`TX_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `dt_tx`
    --
    INSERT INTO `dt_tx` (`TX_ID`,`LastEdit`,`Country_ID`,`pNoR`,`pPic`,`pD et`,`pRateID`,`ProtXFudge`) VALUES
    (144692,'2007-03-19 23:54:56',-1,30,1,0,32,NULL),
    ........
    Last edited by healdem; 03-20-07 at 11:55.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Is there a chance you have more than one link open to a mysql server?

    Your query might be running using the $cnn link, but the mysql_insert_id(), without a specific link identifier in the function call, would use the last link created from a mysql_connect().

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    problem fixed.....
    nothing changed
    on system reboot script worked fine
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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