Professional Web Applications Themes

Next Autoindex - MySQL

I know this is probablly really, really simple, but I can't figure out how to retreive the next autoindex in a table, so when I want to obtain it, I've been inserting a new row, then finding the unique index from some combination of other values that I know will be unique. So I would appreciate you're help in this regard: 1. What's the appropriate SQL query to get the next autoindex from a table (say 'user'). 2. How do I obtain that value in PHP (as in, what would I use as the index in the array returned by ...

  1. #1

    Default Next Autoindex

    I know this is probablly really, really simple, but I can't figure out
    how to retreive the next autoindex in a table, so when I want to
    obtain it, I've been inserting a new row, then finding the unique
    index from some combination of other values that I know will be
    unique.

    So I would appreciate you're help in this regard:
    1. What's the appropriate SQL query to get the next autoindex from a
    table (say 'user').
    2. How do I obtain that value in PHP (as in, what would I use as the
    index in the array returned by mysql_fetch_assoc())

    Thanks!

    PS - I'm asking a PHP question in a MySQL forum because I didn't get a
    response after several days in alt.php.sql - not because I'm a
    clueless idiot who expects all MySQL users to use PHP.

    Michael Guest

  2. #2

    Default Re: Next Autoindex

    Hi Michael,

    "Michael" <com> wrote:
     

    Nobody is doing *that* anyway. There is no reliable way to answer
    "What AUTO_INCREMENT value will I get if I execute $INSERT now?".
    Just think of race conditions with other clients inserting data.

    The ususal way is to just do the INSERT and then ask MySQL
    "What AUTO_INCREMENT did you create for my last INSERT?"

    MySQL answers that with the LAST_INSERT_ID() function:
    http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

    <cut>
     

    PHP gives you another possibility: use the mysql_insert_id() function:
    http://www.php.net/manual/en/function.mysql-insert-id.php

    The difference between the LAST_INSERT_ID() SQL function and the
    mysql_insert_id() API function is, that LAST_INSERT_ID() remembers
    the latest generated AUTO_INCREMENT value, even if there were other
    statements in between. OTOH mysql_insert_id() remembers the
    AUTO_INCREMENT value of the last statement. If you do some SQL
    statement that does not generate an AUTO_INCREMENT value, it will
    reset mysql_insert_id() to 0.

    Beware of the implicite connection sharing in PHP! If you use
    multiple database connections in the same PHP script, PHP may share
    connections (see here for an explanation)
    http://www.php.net/manual/en/function.mysql-connect.php

    This may spoil AUTO_INCREMENT values, even those from LAST_INSERT_ID.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Re: Next Autoindex

    On Wed, 21 Mar 2007 08:12:29 +0100, Axel Schwenke wrote:
     
    >
    > PHP gives you another possibility: use the mysql_insert_id() function:
    > http://www.php.net/manual/en/function.mysql-insert-id.php
    >
    > The difference between the LAST_INSERT_ID() SQL function and the
    > mysql_insert_id() API function is, that LAST_INSERT_ID() remembers
    > the latest generated AUTO_INCREMENT value, even if there were
    > other statements in between. OTOH mysql_insert_id() remembers the
    > AUTO_INCREMENT value of the last statement. If you do some SQL
    > statement that does not generate an AUTO_INCREMENT value, it will
    > reset mysql_insert_id() to 0.
    >
    > Beware of the implicite connection sharing in PHP! If you
    > use multiple database connections in the same PHP script,
    > PHP may share connections (see here for an explanation)
    > http://www.php.net/manual/en/function.mysql-connect.php
    >
    > This may spoil AUTO_INCREMENT values, even those from LAST_INSERT_ID.[/ref]

    IIRC, it's not impossible to make mysql_insert_id() all but
    completely safe at the PHP interpreter level, and there was some
    noise about doing so a few years back, so getting into the habit of
    using mysql_insert_id() for preference over rolling your own with
    LAST_INSERT_ID() might actually pay off someday.

    --
    For why should my freedom be judged by another's conscience?
    -- Paul (I Corinthians 10:29)
    Peter Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139