NYCPHP Meetup

[Carlos A Hoyos]

It's just ambiguous in the documentation, but mysql_insert_id() doesn't
default to 0 for this insert fail.

It's safer to check the return value of mysql_query in this case (will
return false if insertion fails).

Looking at the source code (file php-4.3.2/ext/mysql/php_mysql.c), the
insert_id value gets set under the "get_info:" label in the
mysql_read_query_result function, there's no variable reset on error, so
what you describe is the expected behavior (codewise).

Using LAST_INSERT_ID() in SQL can also be misleading, as it gets calculated
before insertion (and thus key validation), so it might increase even if
the query fails.


Carlos


                                                                                                                                       
                      Hans Zaunere                                                                                                     
                      <hans at nyphp.org>         To:       talk at lists.nyphp.org                                                          
                      Sent by:                 cc:                                                                                     
                      talk-bounces at list        Subject:  [nycphp-talk] mysql_insert_id Strangeness                                      
                      s.nyphp.org                                                                                                      
                                                                                                                                       
                                                                                                                                       
                      08/05/2003 09:37                                                                                                 
                      AM                                                                                                               
                      Please respond to                                                                                                
                      NYPHP Talk                                                                                                       
                                                                                                                                       




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


_______________________________________________
talk mailing list
talk at lists.nyphp.org
http://lists.nyphp.org/mailman/listinfo/talk