Okay! Well, it seems like my shopping cart, made using sessions, is finally working right. I can add various produts, modify them, and delete them. From the mycart.php page, I have a checkout button the user clicks to take them to the checkout.php page, which asks them for the typical, personal information.

At this point, because I'm not dealing with anything vitally important, such as credit cards, i was just going to do the whole thing in PHP. Thus, when a user fills that final form, and submits cart, I would code it to send me an email of the details.

One user gave a very nice advise; he mentioned that, although rare, my email servers could go down. And then what? That order would be lost.

So it does make sense to, at the very least, use the database somewhere in the ordering process. So for me, once the user fills the final, personal information form, I want to submit everything to a database. But how i do this is the question I need to ask. I know this is pretty broad and vague, so I'll try to clarify my situation.

I would obviously need a table in the database called "Orders", or something as such, that php will submit this data to. What would be the primary key of this table? Let's say that the primary key included the first and last name of the customer. But when they order more than one item, that won't work, as we will have two rows with duplicate keys. So then I include the item number as part of the key. In our type of trade, because of the individual personalization on each item, a customer could very well make more than one order of the same item. So at that point, should I make everything a primary key, just to avoid duplicates?

And how do I then get notified once this order is sent? How do I know that a customer just ordered something?

Thanks for your help.
Jonathan

    Make a column called orderID, INT, auto_increment, primary key. Nothing else need to be key or auto increment.

    Also, search for some tutorials for shopping cart on pixel2life.com and you'll find some stuff. If not, use something like osCommerce because you DO NOT want to get hacked

      well if a column with orderID is key, then EACH new row will have a new orderID. That would not make sense when one customer may order three items....thus three rows of data...each with a different orderID (cause you have on auto_increment.

        yahyac wrote:

        And how do I then get notified once this order is sent?

        If I'm adding my cart to a database upon checkout, how do I code it so i can be notified once a cart has been sent?

        Any ideas?

          well, if the email server is down, sending email wont work. why not write a script as part of the backend that will look in that database for orders that have been added since your last login and display a message if there are any? or just manually check that database every day or something?

            you could:

            create a field ordernumber or orderID, not auto-increment, but plan on having an auto-increment LIKE system, where each full order will be 1 more than the last one
            then query the largest ID, before inserting, and insert each item ordered by the customer as that ID + 1.. that way, you have a system that creates a new ID for each order as it is placed, groups all the items in 1 order together, and will stop any duplication and mixing up of customers who place multiple seperate orders

            make sense?

            and as for checking new orders.. you could have each row have a field "new" or something similar, and update each row from new="yes" to new="no" as you view them on an administration page.

              There's gotta be a way, in mysql, to trigger an email when new data is added. Am i wrong on this?

              For example, right now, i use some third party shopping cart, which is really cheesy. When a customer orders something, I get an email with a link. I click this link, it asks for my username and password, and whala, i come to a page where i see that order, the customer information, etc. And all that was stored in a database. Somehow, this third party company, has coded their system in such a way, that as soon as an order is placed, it sends me that email, and when i log in, it calls the database, based on that orderID, and shows me a nice html output of the order and customer info.

              This is basically what I'm looking to do. But for now, i'm stuck on the how do i send myself an email notifying me that a new order has been placed.

                well that email from that shopping cart is generated by php or whatever language it uses, nt mysql. and with code and probably a value in the url it sees who the order belongs to, makes them log in and then displays the info.

                no way for mysql to send an email upon a query yet, you can code mysql extensions to do it though. you could always send an email to yourself and if the server is down the delivery agent should keep trying to send it periodically

                  Well, so you are saying that the ONLY way, as of now, or at least the only easy, common way, is to use php.

                  In this case, once the order is added to the database, and perhaps assigned its own unique cart number, I could then code php to send me an email with a link to some page on my site. This link will have the cart # inside it. And when that link is called up, I can then use that cart # to query the database however I please and show whatever I like on the page.

                  Is that correct?

                  Can I also, at the time of check out, send as many emails as I want. Specifically, I would want to send myself and email with the aforementioned link, and I also want to send the customer a confirmation email. Is this possible?

                  Lastly, is this the common format of the mail function:
                  mail($to, $subject, $message);

                  And is the only way to include a "from" address is to use $headers?

                  Thanks.

                    I made 2 tables

                    First is tblOrder (OrderID,........ )
                    Second is tblBuy (BuyID, ProductID, OrderID, NumberOfProducts,........)

                    This way allows you to make usefull statistics of your orders and buyings and also efficient Data Warehouses

                      Thanks for that input. I haven't had a chance yet to get back to this, as my hands have been full with other things.

                      However, your suggestion is similar to what I had thought of. In the implementation of my cart, I am storing individual cart items in an array where the $key value in this array is some number that is incremented each time an item is added. So each product has a unique value for $key...and that is how I access them in the cart for modification and deletion purposes.

                      So i figured that I would make a table called orders and a seperate table called order_details. The orders table has one primary key: orderID, and this value is auto-incremented. When a customer checks out, I plan to make a new entry in the orders table. This entry will story the orderID (auto-incremented), and the customer information (billing, shipping, etc.). Then, I will retrieve that orderID and use it to post entries into the order_details table. This table has two primary keys: orderID and key, which represents the variable $key that I am using (has unique integers for each cart item). This way, I avoid making every item as a key.

                      Hopefully that way will work okay.

                        Just my 2 cents: I would have a third table for the customer - allowing you to track multiple orders from the same customer. You would then be able to improve the customer experience by filling in their personal details for them. You can do this via cookies so that you can personalise their experience eg 'Hello John, nice to see you again' whenever they revisit your site.

                        Tracking customers rather than orders provides essential 'business intelligence' allowing you to optimise your site and increase your sales.

                          Of cource that Customers must have separate SQL table in webshop database.

                            Thanks for the input Roger.

                            I do intend (hopefully) to do that in the future. But for now, I can only take on so many php/mysql additions, as this is all very new to me.

                            Also, as mentioned on another thread, for our particular business, even though we are a b2b company, the majority (over 99%) of our web-based business is first time customers. This is because repeat customers always deal directly with their salesperson. Our industry is very sales oriented with a high service side. The website is mainly there to be found in search engines and to garner new customers.

                            Nevertheless, I do want to make it as user friendly as possible and plan to implement your suggestions, among others, in the future.

                            Thanks.

                              Glas to be of help. I personally have always coded scripts to store shopping cart data in the db as the user adds things to the cart. That way they can break off half-way through and not have to start all over again when they return. Of course, that was back in the days of dial-up when users could get cut-off at any time. Still, it is the little things that make for good customer-service and set your organisation apart from the rest.

                                so if you don't mind, just for future knowledge, how would you go about implementing that, not the coding details, but just in general.

                                So the customer is shopping, and as they add items to the cart, each item is stored in the database, in some table called, for example, open_cart_items. Then if they close the browser of if power cuts off and the customer returns later, what happens? Do you code it so that once the customer logs in, or once they are recognized by the cookie, that you first check to see if they have anything in the open_cart_items table based on their customerID, and if so, that is then added back to their cart.?.

                                And then once they are ready to check out, all items in open_cart_items are then send to an orders table, and then deleted from the open_cart_items table.

                                Is that the gist of it?

                                  I recommend that you put a timestamp for about 1 hour. If your user visits your website after 1 week thats how you wouldnt confuse him.

                                  You have a good idea and I will think of implementing it.

                                    Yes, if it is an anonymous user then as soon as they hit your site and begin the shopping process you generate an entry in the carts table and dump a cookie.
                                    You will actually have 2 tables: carts and cart_items, just the same as orders and order_items. Carts identifies the user, date-time and anything else you want, and items contains item data. Then as they add items to their cart you record them in the cart_items table. When they checkout their items you clear both the cart tables.

                                    You can put a timestamp on things and run a cleanup routine at regular intervals, or just leave them 'forever', it is really down to site traffic and available storage. Remember, millions of rows are no problem for a relational database like mysql, so long as the file system has disk space the db engine can manage the data.

                                    To take up your point about confusing the user Duje, it really depends on what you are selling and to whom. Personally, I would display the cart contents and ask if they wanted to keep them or not.

                                      Write a Reply...