SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best type for alphanumeric field

    Hi all

    I have a database of auction items. Each item has a unique Id and also a lot number.

    The lot numbers occationally need adjusting and new lots added in so 1, 2, 3, 4, becomes 1, 2, 2b, 2c, 3, 4, etc.

    So I need to be able to store the lot numbers as alphanumeric BUT ALSO be able to sort them by lot number so they appear in the correct order.

    Can anyone suggest the right way of doing this please?

    Kind regards

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Are the lot numbers determined by a known standard? For example, are there lots a,b,c,d,f,g?

    If this is the case I would use two tables:

    auctions
    -- id
    -- lot_id

    lots
    -- id
    -- num

    If the lot is only a character you could just use one table and separate the lot character.

    auctions
    -- id
    -- lot (varchar(1))

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Oddz

    Thanks for taking a look

    Bit more info

    There is an auctions table that contains info on each auction, there is also this items table that contains all of the lots for all auctions, each lot has a foreign key linking to the auctions table.

    I am after a list of all lots for a given auction. Currently each lot has a unique Id and also a Lot number. I need to list all lots by Lot number Ascending.

    so 1, 2, 3, 3b, 3c, 3d, 4, 5, 6 etc.

    Currently the lot number field is set as Int which works when all lot numbers are just numbers (no letters) but I need to be able to include letters in the lot numbers field and then sort the list as shown above.

    Maybe this isnt possible. I've tried setting the field as varchar and text but neither will list correctly. They go 1, 10, 100, 101, 102 etc etc.

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah! This may have cracked it. It looks like I need to force mysql to do a natural sort.

    if I ask it to ORDER BY lots.lotNumber + 0 ASC then it seems to work

    I will carry out some more testing and see if this solves the issue.

    Are there any issues with carrying out a natural sort? there are usually 2000+ lots in a single auction

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that won't sort properly in all cases. you need to sort like this:
    Code:
    ORDER BY lots.lotNumber + 0 ASC, lots.lotNumber ASC
    a couple thousand rows won't take long to sort like this. when you break the 25-50k mark, then you might want to reconsider.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •