NYCPHP Meetup

[Hans Zaunere]

Bonjour,


I've got a strange situation here, and I'm hoping it's something I'm overlooking, rather than a bug.


The setup:

MySQL 4.0.13-max-log
PHP 4.3.2 running as a DSO under Apache 1.3.28
FreeBSD 4.8-STABLE 

MySQL is the mysql.com binary package; everything else is compiled from source


The table:  

CREATE TABLE `links` (
  `linkid` int(10) unsigned NOT NULL auto_increment,
  `link` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`linkid`),
  UNIQUE KEY `link` (`link`)
) TYPE=MyISAM;


The code:

<?php
$MYDB = mysql_connect('localhost','xxx','xxx');


$links = array('http://hans.zaunere.com',
               'http://zaunere.com',
               'http://hans.zaunere.com',
               'http://nyphp.org',
               'http://lists.nyphp.org',
               'http://nyphp.org'
              );


foreach( $links as $key => $link ) {

   $tmp = mysql_escape_string($link);

   mysql_query("INSERT INTO xxx.links (linkid,link)
                VALUES (NULL,'$tmp')", $MYDB);

   $R_linkid = mysql_insert_id($MYDB);

   if( !$R_linkid ) {
      $result = mysql_query("SELECT linkid FROM xxx.links WHERE link='$tmp'", $MYDB);
      echo '<pre>Selected '.mysql_num_rows($result).' rows.</pre>';
      $R_linkid = (int) mysql_result($result,0,0);
   }

   echo "<pre>Array key: $key <br>Link: $link <br>Linkid: $R_linkid </pre><br><br>";
}



The output:

Array key: 0 
Link: http://hans.zaunere.com 
Linkid: 1 

Array key: 1 
Link: http://zaunere.com 
Linkid: 2 

Array key: 2 
Link: http://hans.zaunere.com 
Linkid: 2 

Array key: 3 
Link: http://nyphp.org 
Linkid: 3 

Array key: 4 
Link: http://lists.nyphp.org 
Linkid: 4 

Array key: 5 
Link: http://nyphp.org 
Linkid: 4 


The problem:

It seems that mysql_insert_id() returns the inserted ID from the previous 'successful' INSERT query, rather than it getting reset to 0 since the immedieately previous INSERT fails to produce an AUTO_INCREMENT ID (as it's documented).  I sure hope I'm missing something; otherwise this bug is burning me in ways I have yet to discover.

Thanks,

H