Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    74

    Unanswered: howto get preset auto-increment number ?

    hi,

    if the table is blank , how can we get the preset auto-increment number ?

    i have a try on this :

    code:
    --------------------------------------------------------------------------------

    select case when LAST_INSERT_ID() = " " then '10001' else LAST_INSERT_ID()+1 end as running_num from tbl_invoicehdr limit 1

    --------------------------------------------------------------------------------



    it return nothing if the table have no records

    even i try as follow but give me same blank result

    code:
    --------------------------------------------------------------------------------

    SELECT LAST_INSERT_ID() FROM tbl_invoicehdr

    --------------------------------------------------------------------------------




    besides in mysql , to compare the column is contains empty string, is it like this ?

    code:
    --------------------------------------------------------------------------------

    LAST_INSERT_ID() = " "

    --------------------------------------------------------------------------------



    thank you very much for helping

    database used : mysql

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. you can only use LAST_INSERT_ID right after an INSERT

    2. LAST_INSERT_ID returns a number, not a string

    3. if you want the preset auto_increment number before it is assigned, you are doing something wrong logically
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    74
    thank you r937 for guidance

    Code:
    select case when LAST_INSERT_ID() = " " then '10001' else LAST_INSERT_ID()+1 end as running_num from tbl_invoicehdr limit 1
    just want to know, why when no records, and LAST_INSERT_ID() give me empty , but running_num still show in empty and not '10001' ?

  4. #4
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Quote Originally Posted by alvincks
    select case when LAST_INSERT_ID() = " " then '10001' else LAST_INSERT_ID()+1 end as running_num from tbl_invoicehdr limit 1
    When you specify the table, you will get an empty result set since you select rows from an empty table. Even if you specify a function (in this case, LAST_INSERT_ID()) there are no rows, so you will never get any result set. You probably meant to do

    select case when LAST_INSERT_ID() IS NULL then '10001' else LAST_INSERT_ID()+1

    but since it will never be NULL (but 0) if you have not inserted any rows in the current transaction, you can skip the case statement. Are you sure you aren't looking for the information returned by

    SELECT CASE WHEN MAX(id) IS NULL THEN '10001' ELSE MAX(id)+1 END AS running_num FROM tbl_invoicehdr

    ?

    The latest query returns the highest id number plus one. However, if you delete the number with the highest id number, you will not get the next auto_increment value that will be used. On the other hand, you should probably not use the primary key in the way you think you want to!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no probably about it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    74
    hi snorp and r937 ,

    the reason i would like to get the initial value of max auto increment is because in my application , there have a module to create invoice, so before hand, when user keyin information for invoice, i need to show in invoice page about the invoice number, so when tbl_invoicehdr table have no records inside, i need a return value to show in invoice page as invoice number

  7. #7
    Join Date
    Mar 2004
    Posts
    480
    set the invoice number after the fact.

Posting Permissions

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