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...