SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: unique ids

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    unique ids

    I am integrating an online store with an epayment processing system (HSBC). They asked me to provide as part of every transaction, a unique order id. I need help with that.

    At the moment, I am using php session as a customer id, but I realise this may not be ideal.

    The problem is that I am not inserting any customer or order data into my database until the payment is complete and succesful (i.e. when i recieve the POST data back from HSBC and a flag that payment was succesful).

    My current table structure:

    Product (ProductCode(PK), ProductName, Price)
    Customer (UserId (PK), Name, etc) --> I'm inserting php session as UserId
    Order (OrderId (auto_increment), UserId (FK), date......)
    Order_Item (OrderItemId (auto_increment)(PK), OrderId (PK + FK), ProductCode (PK+FK)....)

    After the payment is succesful, i insert the session id into the UserId, and insert the order and orderItem details.

    Now, I'm not sure how to generate and POST a unique order ID to HSBC before even inserting it into the database. I thought about inserting the order anyway, retrieve the OrderId and POST it with the order to HSBC and if payment successful, leave it there, otherwsie delete. But I don't think this is the best solution and my client agrees.

    I would appreciate any help on:
    - Generating Customer id, other than the session
    - Sending a unique order with the data to HSBC.

    By the way, the customers do not have to sign up in order to make a purchase (this is the client request).

    many thanks

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mayada View Post
    Now, I'm not sure how to generate and POST a unique order ID to HSBC before even inserting it into the database. I thought about inserting the order anyway, retrieve the OrderId and POST it with the order to HSBC and if payment successful, leave it there, otherwsie delete. But I don't think this is the best solution and my client agrees.
    What is your problem with this? It seems reasonably workable to me.

    Alternatively, you could have a table that just holds an incrementing ID, grab the next value from that table, send it with the other data to HSBC, then insert it all into the db when it comes back from HSBC.

    What kind of data is HSBC expecting as a unique order ID? Could you get and send a GUID?

    Mike

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the problem with inserting, retreving and then keeping an deleting an order, is that the client will be accessing the data frequently and dynamically. we do not want to save any orders before payment is sucesful.

    HSBC want 1 to 36 characters long string as the order id.

    By the way how do i make a table hold an incrementing ID. do you mean store a list of ids into a table and then grab one for every order? i am not sure this is a good idea. what if we run out of ids while someone is making an order?
    Alternatively, you could have a table that just holds an incrementing ID, grab the next value from that table, send it with the other data to HSBC, then insert it all into the db when it comes back from HSBC.

  4. #4
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This question has been asked in various permutations several times. The general consensus from SitePoint visitors is that the best approach to this problem is to stick the order in your database before submitting to your payment processor, setting a flag to indicate that payment has not yet been received (you could then show this to your users as "PENDING" or such). Then, using the ID your database generated for you (see: mysql_insert_id if you're using the MySQL functions), submit the order to your processor, and when you receive confirmation update the flag to show that payment has been received (or a different value to indicate an error or payment declined).

    Alternatively, you could have a table that just holds an incrementing ID, grab the next value from that table, send it with the other data to HSBC, then insert it all into the db when it comes back from HSBC.
    BAD idea! What happens if two different users submit at the same time? They could very easily end up with the same "unique" ID, thus screwing up your system royally and possibly landing you in hot water with one or the other. The solution I outlined above is immune to this problem.
    PHP questions? RTFM
    MySQL questions? RTFM

  5. #5
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kromey View Post
    BAD idea! What happens if two different users submit at the same time? They could very easily end up with the same "unique" ID, thus screwing up your system royally and possibly landing you in hot water with one or the other. The solution I outlined above is immune to this problem.
    I don't know about bad - it can work and does in many situations, providing the correct precautions are taken. Remember in your solution you use mysql_insert_id to ensure you get the correct id.

    I agree that what you outlined is better / best, but the OP has already expressed a dislike for that method - I was simply throwing in an alternative.

    As HSBC can accept upto 32 characters, you could use a GUID:

    http://en.wikipedia.org/wiki/GUID

    with the dashes stripped out.

    On a Windows system there is an API call which will return a GUID, or there is a GUID column type in MSSQL-Server. I don't know how to grab a GUID on linux, but I have seen mention of some class that will do it, but you could grab a GUID from MySQL using the UUID() function.

    All that said, I think the best solution is the one that you were talking about in your first post, which was then fleshed out by kromey.

    Regards,

    Mike


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
  •