SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Resetting AUTO_INCREMENT field when date changes

    I want to remove a "DateJoined" field from a membership database. I would like to hold this information in the userid field. That is, I want to set MYSQL (using a trigger?) such that when a new user joins the user id assigned will be in the format: yydddxxx where yy = year, ddd = day of the year, xxx = index of user for that day.
    I know how to generate the 'yyddd' portion. My question is: How can I set up the database to reset the 'userid' (AUTO_INCREMENT field) to begin counting afresh when the date changes?

    Example:
    Today is - 11321 (321st day of 2011)
    At midnight the 'userid' field should reset to begin counting at 11322000 (322nd day of 2011) such that the first user will be assigned userid = 11322000

    Thank you

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,870
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    You can't reset the autoincrement and appear to be getting completely confused as to just what the autoincrement value represents.

    Autoincrement simply provides a unique value for each entry - there is no sequence intended to be implied by the value an d there can very easily be gaps in the sequence (which don't matter because it is the uniqueness that autoincrement is intended to supply and not sequence).

    There is no easy way to number entries in the order they are added as the one that someone starts adding first might end up having the next 50 finish being added before it is actually added.

    If you want to be able to report on the entries in the order they were added on the day then simply save the time they were added and order them by the time when you retrieve them. You can then number them in the resultset you return from the query.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, yes, you can have an auto_increment that resets for each new date

    however, you have to use two separate columns for this, and it has to be certain table type --
    Quote Originally Posted by da manual
    For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
    -- http://dev.mysql.com/doc/refman/5.0/...increment.html
    all you have to do is create a "userdate" column as CHAR(5) and give it the yyddd values, with a "usernum" subsidiary AUTO_INCREMENT column as MEDIUMINT or something like that

    to show them as one column, use CONCAT in the retrieval SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Rudy! Your method works as you said! It's not as easy as i thought it would be, but I can put the 2 fields together for a unique id. Now I can code the date in the userid and can drop the 'dateJoined' field. The nice thing about your solution is that when the 'userdate' changes, the 'usernum' starts at 1 again. That's what I was looking for.

    Thanks again,

    Steve


Tags for this Thread

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
  •