On Wed, Mar 11, 2009 at 5:44 PM, PJ <af.gourmet@xxxxxxxxxxxx> wrote:
> haliphax wrote:
> > On Wed, Mar 11, 2009 at 4:29 PM, PJ <af.gourmet@xxxxxxxxxxxx> wrote:
> >
> >> Lex Braun wrote:
> >>
> >>> PJ,
> >>>
> >>>
> >>>
> >>>> Â Â <snip>
> >>>>
> >>>>
> >>>>> Â Â $sql1 = "INSERT INTO book ( title, sub_title, descr,
> >>>>> Â Â Â Â Â Â Â Â comment, bk_cover, copyright, ISBN, language,
> sellers )
> >>>>> Â Â Â Â Â Â VALUES ('$titleIN', '$sub_titleIN', '$descrIN',
> '$commentIN',
> >>>>> Â Â Â Â Â Â Â Â '$bk_coverIN', '$copyrightIN', '$ISBNIN',
> '$languageIN',
> >>>>> Â Â Â Â Â Â Â Â '$sellersIN')";
> >>>>> Â Â Â Â $result1 = mysql_query($sql1, $db);
> >>>>> Â Â Â Â $autoid = mysql_insert_id($result1);
> >>>>>
> >>>>>
> >>> You're actually sending mysql_insert_id() the wrong parameter. It
> should be:
> >>> $autoid = mysql_insert_id($db); // you send the resource of your MySQL
> >>> connection (http://ca3.php.net/manual/en/function.mysql-insert-id.php)
> >>> This should be corrected in everywhere you call mysql_insert_id()
> >>>
> >>> -Lex
> >>>
> >>>
> >>>
> >> I tried this (from the link above)
> >> $result1 = mysql_query($sql1, $db);
> >> Â Â $autoid = mysql_insert_id();
> >> Â Â echo $autoid;
> >> works...
> >>
> >> but now, I have another problem... I am trying to debug this thing by
> >> going 1 query at a time ( I comment out the rest): every time I do an
> >> INSERT INTO book... the insert works fine but the ID is increased not
> >> from the highest id value but from the last id inserted. So my highest
> >> at the moment is 11; I insert test data and it goes in as id = 15; I
> >> delete this field and redo an insert and it goes in as id = 16. How can
> >> I change this so the auto_increment continues from the 11 ?
> >>
> >
> > In MySQL,
> >
> > ALTER TABLE [tablename] AUTO_INCREMENT = [value];
> >
> > HTH,
> >
> Ok, but why is it doing that? I like to "understand" - I must have asked
> too many questions when I was a kid... it stuck. :-)
>
It's doing that because an auto-incrementing column keeps incrementing with
each insertion. Thus, say you insert an entry into ID #10 and delete it
before another entry is made. MySQL will still have the auto_increment set
to 11 (one more than the last insertion) rather than subtracting one because
you deleted #10. It's the database method of ensuring you aren't
over-writing pre-existing auto_increment values.
NOTE: This doesn't take into account someone entering their own value into
the ID field greater than the current auto_increment value.
