NYCPHP Meetup

[nycphp-talk] mysql_insert_id Strangeness

Hans Zaunere hans at nyphp.org
Tue Aug 5 10:08:22 EDT 2003



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




More information about the talk mailing list