Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2009
    Posts
    7

    Unanswered: Auto Increment Prefix

    Hi All,

    I have searched for an answer to this question with mixed results. I have found everything from "NO" to "yes but don't" to "you have no idea how to/need to redesign the DB".

    Basically what I have is a DB with 1 table and 4 fields. Of the 4 fields the first is called ID, is set to auto increment, integer and is the primary Key. That field is used to identify each entry submitted by a php form. The others fields are customer name, part number and comments.

    The problem is I would like this auto increment ID field to have a 2 or 3 (or 4 or 5) letter prefix (or suffix for that matter) before, or after the generated number and I'll tell you why so it makes sense. As the DB grows it would make it supremely easier to search by the generated ID as each will be the most unique in the entire DB. For example if there are 100 orders for the same customer over time each one will have a different alpha-numeric ID and therefore be the only listed search result where a given number could have dozens or hundreds.

    Re-writing the search around a new DB is not really what I had in mind. Not because it is a large DB, it is still experimental. The problem is in the only working search code I could find and joining 4 tables and 4 fields as opposed to one table and 4 fields.

    Thanks,

    Jim

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    4 table and 4 fields?

    about the prefix, you do not need it, and patching it onto the id is going to hamper your effectiveness in countless ways

    if you're sure you need to have an auto_increment number at all (which is a separate thread), then use it the way a surrogate key should be used -- under the covers, behind the scene, irrelevant to the users of the application

    so no prefix, because if the auto_increment is under the covers, behind the scene, irrelevant to the users of the application, then the prefix doesn't matter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2009
    Posts
    7
    1 table w/4 fields.

    I have previously posted this same question in the "official" MySQL forum and gotten a similar answer yet I still fails to grasp why. I do not imply this is the fault of the responders but rather my own lack of understanding.

    The DB model and search are posted tutorials on building a searchable customer DB with an end user entry form. the auto increment ID field was used as a customer assignment routine and it seems to work and display correctly. I was hoping to take this further making it even more unique for easier searches.

    Is it that building a query for 4 tables with 1 field each is the correct layout? And if it is why can't I seem to find a piece of PHP code that will search and display connected results from this arrangement? How about just making the initial valve of the auto increment very high (like 6 or 8 digits)?

    Thanks in advance,

    Jim

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where do you get this "4 tables with 1 field each" business?

    setting a high auto_increment value will have nothing to do with this

    remember, the users should not be shown the auto_increment values at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Posts
    64
    Quote Originally Posted by Jimi_l
    I'll tell you why so it makes sense. As the DB grows it would make it supremely easier to search by the generated ID as each will be the most unique in the entire DB.
    If you just want to make sure the ID is unique, use MySQL UUID instead. It'll, in theory, be unique in the universe. Even better than your requirement to be unique in the entire DB.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just wondering if the OP is clear about primary keys and foreign keys.
    the autogenerated value is one way of guaranteeing a unique value.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2009
    Posts
    7
    just wondering if the OP is clear about primary keys and foreign keys.
    No, not entirely. The tutorial I found used the ID field as the PK as did I.

    where do you get this "4 tables with 1 field each" business?
    From the "official" MySQL forum. At least I though that is what they were trying to tell me.

    Maybe I should just show you what I am doing and what I used to do it. Yes I know it is insecure but I don't really care at the moment and eventually it will run on an Intranet/local machine with no outside access.

    Add Orders

    This is where end users will enter an order. It is then entered to the DB and can be searched for later on by any field. With each new order a prefix to determine location and to make it (the whole order) a uniquely search able item. For example if I could use ABCDE1 for the first order and ABCDE2 for the second and so on. When I searched for any given order at a later time using the order number it would be the only result where customer number or part would likely yield many results.

    The tutorials I used are here. First the insert form-
    DreamWeaver MySQL Record Insertion

    Then the search-
    Creating a Form that will Search a MySQL Database | Team Tutorials

    Thanks in advance for your time and advice,

    Jim

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    an autogenrerated number makes a row uniquely indentifable.. thats its sole role. so adding something to that makes no sense to me. I have used masking systems to hide the value of such a number (eg prefixing some form of year and month to mask the amount of transactions doen on a web shop).

    it sounds like you have some confusion also on how to search for data

    you can use multiple terms in a SQL where clause

    eg
    select my,comma,seperated,column,list from MyTable
    where orderdate<"2009\03\21"

    or
    select my,comma,seperated,column,list from MyTable
    where orderdate<"2009\03\21" and CustomerID = "36143545" and deliveryaddress like "%Manchester%"
    order by My,sort,order
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2009
    Posts
    7
    Confusion I got

    The biggest issue I had with other search scripts was that they either returned no results or returned results specific to the search value and not the entire string (for lack of a better term).

    In other words, if I search for customer "joe's garage" I need to see ALL the information from ALL the orders for that name not just the instances of that name which was what I was getting.

    A work in progress to be sure,

    Jim

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the manual on using a fulltext index that allows you partial matches.
    mind you you would make life easier for yourself by defining a customer table... why
    you'd have a fixed value for any one customer. so you wouldn't be at the mercy of miss spelling or typos
    JOES GARAGE is not the same as JOE'S GARAGE. your users could know its JOE'S GARAGE by just call it JOE'S, or JOE'S AUTOSHOP, or JOE SMITH & CO
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2009
    Posts
    64
    Let me get it straight. You want to identify customer's A order number 1, order number 2, order number 3 etc as A1, A2, A3. I'm also assuming that you want to identify customer B's order number 1, 2, 3 and so on as B1, B2, B3, etc.

    If that's what you want, then what you need is a sequence. So basically, if customer A places order, you get the next sequence number for customer A and assign that as order number. Similarly, for B, you'll get the next sequence number for B and assign it to B's order.

    MySQL doesn't have built-in support for sequence. However, I've written a little blog post on how to emulate it here: Microshell Emulating nextval() function to get sequence in MySQL.

    What you need, then is to create a sequence for each customers, then increment that particular customer's sequence number each time they place an order.

    Personally, I don't think that's a good design though. But then again, I don't know the full requirements of your project.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can';t see anal accoutnants being overly happy with that
    they like the idea of an order number which can be married to a despatch note (or more), which can be married to a Invoice which can be married to payment(s) and statements and so on.

    its quite common to create account numbers using the first 3 or 4 digits of the company name or the surname plus a number.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Oct 2009
    Posts
    7
    Healdem is close I think.

    If anyone orders anything from anywhere, everyone else everywhere needs to be able to find that order multiple ways (customer #, part # or even better ORDER#) when it shows up. This is the heart of the project.

    So, let's say I order an ACDelco R45TS spark plug for Joe Blow. I call the order in and also key in the order info-

    Cust#
    Joe Blow
    Vendor/Part#
    ACDelco#R45TS
    Comments:
    ETA tomorrow. Bill and deliver when in

    Currently after it is entered (and you can try this on the site I listed with any name or part you like) you get a auto generated number under the "Special Order" column. I would give Joe this number, I would likely also use this number as a purchase order to the vendor as well as print a copy of the order and hang it up for all to see.

    Tomorrow arrives and the order comes in, the first person to receive it can go to the program and try a search. If he tries the vendor and/or part number (ACDelco#R45TS) he is likely to get thousands of hits as it is a very popular number. If he tries the search by name he will get every order ever entered for "Joe Blow". If he tries "10" (the current special order number I am up to) he will also get every order with a "10" in it. Again, way too many hits to be useful. Even if the sequence gets to 100 or 1000 it is too common to be useful.

    However, if the MySQL auto increment generated a special order number that has some sort of prefix or suffix or other oddly unique attribute, when searched for only the correct order will appear. So lets say I CAN use a prefix and I choose to use USA for that prefix. A search for USA10 would only produce the correct order. There will never be another USA10 special order number and there is very likely no part number (or name or account number or whatever) on Earth with this number.

    This is what I seek. Along with a way to "finish" the order after it arrives but that is another problem altogether.

    BTW you guys are great all tossing ideas and help out. It is not unappreciated.

    Jim

  14. #14
    Join Date
    Sep 2009
    Posts
    64
    Quote Originally Posted by Jimi_l
    I choose to use USA for that prefix. A search for USA10 would only produce the correct order.
    I still don't get it ... You choose USA ... I assume you'll have some kind of pull down or something like it in the search form. Then why don't you just search in 2 fields? like "location" and "id" ? I failed to see why you'd need to concatenate the 2 values to be in 1 column if having them in 2 columns works fine. In this case you'd do something like:

    Code:
    SELECT * FROM orders WHERE location = 'USA' and id = 10
    instead of

    Code:
    SELECT * FROM orders WHERE id = 'USA10'

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it just smacks of a poor understanding of db design
    for the example you are using 'USA' as a prefix gives you nothing

    I would separate vendor and part name, so that youcan allow for equivalencies (ie the supplier doesn't have an AC Delco, but does have a Champion, or NGK equivalent

    if you want the correct order you 'just' select where orderid=10. a autogenerated nuymber is guaranteed to be unique.


    now it possible that your organisation has order books which are sequential with a prefix and you want to chase a specific order based on that document that the supplier knows.. in whic case you will need to make up your own order ID sequencing function or 'just' type in the number from the piece of paper..

    so before going round the houses again
    what benefit do you see in having a prefix (not in generalbut in specifics), why do you think an autogenerated number won't work.

    what do you hope to achieve through this prefix, how do users recognise what prefix to attach to the ID.

    waht I find odd about your table is it doens't identify the supplier, I'd expect that to be a Foreign Key to a table of suppliers. I suspect that is where your USA is coming from IE USA is something like Uncle Sam's Autoparts, and if thats the case thats crap design...
    read up on normalisation.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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