SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Auto-Increment Problem - And two tables for customer purchase info. query

    Hi,

    I have a MySQL table that stores what customers have added to their basket. Obviously the number of entries will increase and I will need to delete some. ie. i do not need to store what a customer added to their basket 6 months ago (particularly if they never actually bought what they added to the cart).

    My problem is when I delete them the auto-incrementing ID continues from the highest number. If I delete 1-500 of a thousand entries, when someone new adds something they are given the ID 1001. Eventually I will have a table starting at something like 14,000 and ending at 14,300, etc. which seems a bit daft.

    I intend to have a second table that stores customer purchases. The table I refer to in the above 2 paragraphs will be stuff added to the basket. When the customer actually clicks BUY AND PAYS for the items in their basket I intend to add the details to another table and keep this information forever (or at least 6 years as required for the tax man).

    Perhaps you know a way to delete an entry from the first table when it is added to another table (at the point of purchase) and do you know a way of eliminating my Auto-Increment ID problem in the first table when information is transferred to the second table?

    Matt.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    there is no auto_increment problem -- leave the numbers alone, they don't mean anything, and if you try to renumber them, you will, and i say this with complete assurance, shoot yourself in the foot one day

    as for moving stuff out of the basket table into another table, that's also not necessary -- just add another column that indicates whether the item was bought

    if anything, you can periodically remove old rows from the table that weren't bought
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your reply. I will keep the single table as you suggest.

    Would you simply add a word, say "yes", after the user clicks to enter their card details? I was just thinking if someone clicks to enter their card details and it is declined or perhaps they change their mind and do not even enter their card details. Can you suggest the best way.

    As you probably know, the customers BILLING address is entered with their CARD DETAILS on a secure web page. I will be designing the table to retrieve the BILLING address from the card-processing company (to appear on their bill/invoice) because I will not have it in my MySQL table - I will only have the delivery address. Would it be advisable to add the word, say "yes" at this stage indicating the purchase was made?

    How would you recommend I do this?

    Matt.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    definitely don't update the item to 'yes' until the card has been billed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - I'll get this sorted in the near future. Thanks for your guidance, Matt.


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
  •