NYCPHP Meetup

[nycphp-talk] mysql_insert_id Strangeness

Brian Pang bpang at bpang.com
Tue Aug 5 10:11:49 EDT 2003


I usually send 0 to the primary key


using the mysql client, are you able to replicate the problem? meaning,
do you get the right mysql_insert_id when performing inserts in the
mysql client

from the mysql manual:
"mysql_insert_id() is updated after INSERT and UPDATE statements that
generate an AUTO_INCREMENT value or that set a column value to
LAST_INSERT_ID(expr). See section 6.3.6.2 Miscellaneous Functions.

Also note that the value of the SQL LAST_INSERT_ID() function always
contains the most recently generated AUTO_INCREMENT value, and is not
reset between queries because the value of that function is maintained
in the server."

They say it doesn't get reset [to zero?]

still can't get into the php.net site to look at the php manual :(  so I
can't cross check exactly what php's mysql_insert_id() is doing or for
an alternative




> 
> 
> Russ Demarest wrote:
> 
> > There are a couple strange things, I am not accustom to.
> > 
> > Why do you define the link column as NOT NULL and then default to
''? Is 
> > this not the equivalent of a contradiction? I doubt this is related.
> 
> Defaulting to '' means it'll default to the empty string, which is
different from a NULL column.  You're right as in this case it probably
isn't related, but I'm not a fan of NULL columns :)
> 
> > Maybe I am old school or something but I normally set my mysql_query() 
> > equal to something like so
> > 
> > $sql = "INSERT into table blah blah";
> > $res = mysql_query($sql) or die("Error insert");
> > $new_id = mysql_insert_id($res);     # The $res is not needed, it 
> > default to the last result set returned.
> 
> Hmm, I'd take another look here.  For INSERT statements, mysql_query()
will only return TRUE or FALSE; not a result resource.  Furthermore,
mysql_insert_id() takes a link resource; not a result resource. 
Nevertheless, still no joy in trying the code.
> 
> Thanks,
> 
> H
> 
> 
> > 
> > This is a little different than you have it. Maybe try it and see what 
> > happens.
> > 
> > Good Luck
> > 
> > On Tuesday, August 5, 2003, at 09:37 AM, Hans Zaunere wrote:
> > 
> >>
> >> 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
> >>
> > 
> > _______________________________________________
> > talk mailing list
> > talk at lists.nyphp.org
> > http://lists.nyphp.org/mailman/listinfo/talk
> 
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
> 
> 






More information about the talk mailing list