Professional Web Applications Themes

Can I determine the index (value) of an auto-increment field - MySQL

Hello MYSql Lovers, Is there a handy way (using PHP) to determine the value of an auto- increment field when I am going to insert a record into a table? by this I mean, without counting the records in the table already and adding 1? or using MAX(id) + 1 ,etc? A more challenging question (for me at least), suppose I have an empty table with an auto-increment field (id), and I am going to insert all the files in a given directory in the the database table, at insert time, is there a way I can predict (or know) ...

  1. #1

    Default Can I determine the index (value) of an auto-increment field

    Hello MYSql Lovers,

    Is there a handy way (using PHP) to determine the value of an auto-
    increment field when I am going to
    insert a record into a table?

    by this I mean, without counting the records in the table already and
    adding 1? or using MAX(id) + 1 ,etc?

    A more challenging question (for me at least), suppose I have an empty
    table with an auto-increment field (id),
    and I am going to insert all the files in a given directory in the the
    database table, at insert time, is there a way I can predict (or know)
    the value of "id"??

    I would like to create a value in another field based on the value (a
    number) of the id field.
    Like col1 value = id + (MOD %30 of id).

    I know that i can load the table first, then do an update on col1
    using the formula based on the value of "id".

    I was wondering if there was a better way, etc.

    thanks
    eholz1

    eholz1 Guest

  2. #2

    Default Re: Can I determine the index (value) of an auto-increment field

    eholz1 wrote: 

    Sorry, you can't reliably predict the value of the last insertid before
    the row is inserted. For instance - what if you had two connections
    inserting concurrently? Both could "predict" the same id, but only one
    would get it.

    Of course you can use mysql_insert_id() to get the last id inserted.
    However, it might be easier to fetch the first id you insert, then run
    one SQL statement to update all rows after that one, i.e.

    (insert row)
    $start = mysql_insert_id();
    (insert more rows)
    $result = mysql_query("UPDATE mytable " .
    "SET col1=MOD(id, 30)" .
    "WHERE id >= " . id);

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Can I determine the index (value) of an auto-increment field

    > (insert row) 

    Or, in one go, on the database server:
    INSERT INTO mytable(...) VALUES(...);
    SET Id=LAST_INSERT_ID();
    UPDATE mytable SET col1=MOD(Id, 30) WHERE id=Id;
    -- More rows:
    INSERT INTO mytable(...) VALUES(Id, ...);


    This should be done in one connection session, otherwise the variable
    Id is cleared.

    Best regards
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  4. #4

    Default Re: Can I determine the index (value) of an auto-increment field

    Willem Bogaerts wrote: 
    >
    > Or, in one go, on the database server:
    > INSERT INTO mytable(...) VALUES(...);
    > SET Id=LAST_INSERT_ID();
    > UPDATE mytable SET col1=MOD(Id, 30) WHERE id=Id;
    > -- More rows:
    > INSERT INTO mytable(...) VALUES(Id, ...);
    >
    >
    > This should be done in one connection session, otherwise the variable
    > Id is cleared.
    >
    > Best regards[/ref]

    Yes, but it's less efficient than when updating multiple rows. Each
    entry has an INSERT, a SET and an UPDATE. This requires three
    statements to be pd and executed for each insert - much slower,
    unless you have a very slow database connection.

    My way has one INSERT per row, on get last insert id and a single
    UPDATE. Parsing/execution will be much faster.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Can I determine the index (value) of an auto-increment field

    >> INSERT INTO mytable(...) VALUES(...); 
    >
    > Yes, but it's less efficient than when updating multiple rows. Each
    > entry has an INSERT, a SET and an UPDATE. This requires three
    > statements to be pd and executed for each insert - much slower,
    > unless you have a very slow database connection.
    >
    > My way has one INSERT per row, on get last insert id and a single
    > UPDATE. Parsing/execution will be much faster.[/ref]

    The last INSERT can have more than one row and could even be delayed, I
    think. If the data to insert is not too complicated, you could even
    write a stored procedure for the above queries. I think the above
    queries are not more load than the "external" ID handling, but it can
    take some load of the webserver or the data traffic.

    Best regards
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  6. #6

    Default Re: Can I determine the index (value) of an auto-increment field

    Willem Bogaerts wrote: 
    >> Yes, but it's less efficient than when updating multiple rows. Each
    >> entry has an INSERT, a SET and an UPDATE. This requires three
    >> statements to be pd and executed for each insert - much slower,
    >> unless you have a very slow database connection.
    >>
    >> My way has one INSERT per row, on get last insert id and a single
    >> UPDATE. Parsing/execution will be much faster.[/ref]
    >
    > The last INSERT can have more than one row and could even be delayed, I
    > think. If the data to insert is not too complicated, you could even
    > write a stored procedure for the above queries. I think the above
    > queries are not more load than the "external" ID handling, but it can
    > take some load of the webserver or the data traffic.
    >
    > Best regards[/ref]

    William,

    I suggest you try it with a few thousand rows. I think you'll find
    there is a significant difference in the processing time required.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Can I determine the index (value) of an auto-increment field

    On Feb 6, 2:57 pm, Jerry Stuckle <net> wrote: 







    >
    > Sorry, you can't reliably predict the value of the last insertid before
    > the row is inserted. For instance - what if you had two connections
    > inserting concurrently? Both could "predict" the same id, but only one
    > would get it.
    >
    > Of course you can use mysql_insert_id() to get the last id inserted.
    > However, it might be easier to fetch the first id you insert, then run
    > one SQL statement to update all rows after that one, i.e.
    >
    > (insert row)
    > $start = mysql_insert_id();
    > (insert more rows)
    > $result = mysql_query("UPDATE mytable " .
    > "SET col1=MOD(id, 30)" .
    > "WHERE id >= " . id);
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Hello,

    Thanks for for reply, Always good info. Seems like jerry always
    wins!!!!
    Thanks all for the tip, I will put it into action!!

    ewholz

    eholz1 Guest

Similar Threads

  1. Auto increment VALUE attribute extension
    By arzo2000 in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: November 11th, 05:55 PM
  2. auto-increment
    By tony in forum MySQL
    Replies: 2
    Last Post: October 6th, 05:17 PM
  3. How to Handle Primary Key Auto Increment?
    By tbemcf14 in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: March 22nd, 09:47 PM
  4. id after auto increment from a form
    By Faith in forum ASP
    Replies: 7
    Last Post: August 6th, 05:18 PM
  5. Auto Increment a number field
    By jaycee in forum Microsoft Access
    Replies: 0
    Last Post: July 30th, 11:22 PM

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