SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast Mounty's Avatar
    Join Date
    Mar 2008
    Location
    UK
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Auto increment based on a column value?

    I did a bit of searching but couldn't find anything, so:

    If I have a table structure similar to this:
    Code:
    table: productImages
    
    id	filename	productId	order
    
    1	10234.jpg	12		1
    2	13702.jpg	12		2
    3	23674.jpg	12		3
    4	98373.jpg	15		1
    5	44126.jpg	15		2
    Would it be possible to create an auto increment value in the 'order' column, based on the 'productId' column?

    As an example, if I insert another record with filename = 12345.jpg and productId = 12, then it would set order = 4 based on the previous three entries for that product?

    cheers

  2. #2
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I am not mistaken, auto-increment fields must also be unique. Somebody will be able to confirm or deny my suspicions.

  3. #3
    SitePoint Enthusiast Mounty's Avatar
    Join Date
    Mar 2008
    Location
    UK
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm probably right, ah well worth a shot


    cheers

  4. #4
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    update productImages set order = (select (max(order) +1) where productid = $x) and id = $y

    Would only work in MySQL 5+ though.
    Wayne Luke
    ------------


  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mounty View Post
    Would it be possible to create an auto increment value in the 'order' column, based on the 'productId' column?
    yes, it is possible

    please look it up in da manual under "using auto-increment"
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast Mounty's Avatar
    Join Date
    Mar 2008
    Location
    UK
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys you put me on the right track to figuring it out

    Here's my solution if anyone is interested

    Code:
    INSERT INTO productImages(order) SELECT (MAX(order)+1) FROM productImages;

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no no noooooo.....

    the "select max" technique has several deep flaws

    and besides, you aren;t getting a new number by product, that just gets you a new number overall

    did you look up the page in da manual as i suggested? they give a very clear example of exactly what you want
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I love this place. I never knew you oculd do that!
    MySQL v5.1.58
    PHP v5.3.6


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
  •