Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    May 2007
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Generate Numbers

    Hello:

    I hope someone can help me out.

    I have a table which stores client information. In this table I also need to store a field called case_number.

    My client would like the case_number to be automatically generated when she adds a new client. And, she would like the case numbers to be sequential.

    So if I have ClientA in a table with case number 2020 and I add ClientB to the table, I would like their case number to be 2021. And, if I add ClientC to the table, their case number sould be 2022. Etc.

    Is it possible to write a script to generate a number in sequential order and store that number into a table?

    If so, how do I go about scripting something like this?

    Thank you in advance.

  • #2
    Senior Coder rafiki's Avatar
    Join Date
    Aug 2006
    Location
    Floating around somewhere...
    Posts
    2,046
    Thanks
    19
    Thanked 42 Times in 42 Posts
    set it to auto_increment.

  • #3
    New Coder
    Join Date
    May 2007
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can I have two auto_increment fields in a table? I tried that one time and I got an error.

  • #4
    Senior Coder rafiki's Avatar
    Join Date
    Aug 2006
    Location
    Floating around somewhere...
    Posts
    2,046
    Thanks
    19
    Thanked 42 Times in 42 Posts
    you should be able to. i dont see why not

  • #5
    New Coder
    Join Date
    May 2007
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I gave it a quick try. When I went to save the table, I received the following message:
    #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

    I already have a primary key set to auto_increment. I need to have another column which increments numbers as well and it will need to increment from a starting number of 2000.

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,128
    Thanks
    2
    Thanked 328 Times in 320 Posts
    Two questions - Do clients and case numbers always have a one to one relationship (is a repeat client considered to be a different client because it will have a different case number, this is what your description implies)? Can clients have more than one case number (ClientA, case numbers 2002, 2040, 2140...)?

    If the answer to question one is yes, then use your existing primary key as the client ID and as the case number and set it to a starting value of 2000.

    If the answer to question two is yes, you need two tables, one to hold the client information and one to hold the case information and have a column in the case table with the corresponding client ID.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    New Coder
    Join Date
    May 2007
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    A client can be a repeat client. If the client is a repeat client, then he/she will have a new case number assigned. Therefore, a client will never have more than one case number assigned to them.

    Let me see if I'm understanding your suggestion. My table currently looks like this:

    client_id | name
    1 | joe
    2 | mary

    I need to change my table to look like this:
    client_id | name | case_no
    1 | joe | 2000
    2 |mary | 2001

    Are you suggesting that my table should actually look like this?
    client_id | name | case_no
    2000 | joe | 2000
    2001 | mary | 2001

    Where the client_id is auto_incremented starting at 2000 and then I do a mysql_insert_id into the case_no field? So now client_id and case_no are the same?

    Am I on the right track here?

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,128
    Thanks
    2
    Thanked 328 Times in 320 Posts
    This part makes sense: "A client can be a repeat client. If the client is a repeat client, then he/she will have a new case number assigned."

    This part contradicts the first part: "Therefore, a client will never have more than one case number assigned to them."

    By stating that a repeat client will have a new case number assigned, that client will then have more than one case number in the database (even if a previous case is marked as being closed/completed/dormant...)

    This is what you need to achieve -

    Client table:
    client_id | client detail columns (name | address | phone | ...)
    1 | joe
    2 | mary

    Case table:
    case_no | client_id | case detail columns (start, end, status... whatever you need)
    2000 | 1 | details...
    2001 | 2 | details...
    ....
    2020 | 1 | details... // joe is a repeat customer with a new case and a new case number, but he already exists as a client in the client table

    One thing to remember with database design is don't duplicate information. Store each piece of information only once.

    I suspect that each case could have multiple log entries/notes/expenses... or something similar? You would make one more table to hold them -

    Case log table:
    log_no | case_no | date | type (whatever you need) | details... (whatever you need)
    1 | 2000 | 2007-08-25 | note | "held initial meeting with client"
    2 | 2000 | 2007-08-25 | business meal expense | $55.00
    3 | 2020 | 2008-08-25 | note | "met with an old client about a new case"
    ...
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #9
    New Coder
    Join Date
    May 2007
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I see what you are saying. After reading my statement again, I realized that I missed a bit of info.

    If a client becomes a repeat client, he/she will be assigned a new client_id number as well as a new case_no.

    For whatever reason, in this particular case, probably for regulatory reasons, they need to treat everyone as new since the situation may be different each time.

  • #10
    Regular Coder
    Join Date
    Mar 2007
    Location
    Quebec
    Posts
    261
    Thanks
    6
    Thanked 7 Times in 7 Posts
    If you're starting from a new table with no data in it client_id & case_no should be the same using auto_increment. Although whether its the best idea or not i dont know.

    This code should work for the 2 auto_increments.
    PHP Code:
    mysql_query("CREATE TABLE example (
    client_id INT auto_increment NOT NULL,
    case_no INT auto_increment NOT NULL,
    primary key(client_id),
    primary key(case_no)
    )"
    ); 


  •  

    Posting Permissions

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