Jump to content
Larry Ullman's Book Forums

Adding Login Option To Coffee Site


Recommended Posts

I've just finished Effortless E-commerce. I really enjoyed the entire book. I particularly like how advanced the database queries got. After reading Quickpro PHP 5 and MYSQL 6 and Quickpro Advanced PHP 5 I'm impressed how much new and fresh content was covered in Effortles E-com.

 

I'm curious now, how labor intensive it would be to add a registration/login function to the coffee site. I'm guessing the DB structure wouldn't require many changes, only a "pass" field in the Customers table and a "customer_id" field added to the Carts and Wishlist tables to use as an alternative to "user_session_id" (or maybe remove user_session_id altogether and reference "customer_id" the way the Orders table does).

 

What I find most daunting though would be trying to alter the stored procedures. I believe everywhere the user_id_session is referenced would have to be changed: add_to_cart, remove_from_cart, update_cart, get_shopping_cart_contents, clear_cart, order_contents, and add_order.

 

I'm also guessing you would have to run an IFELSE check in cart.php, wishlist.php,checkout.php, billing.php scripts to prevent any conflicts or duplicate records when a logged in user is also generated a user_session_id and updates his account.

 

From what I gather though, it doesn't appear there would need be any alterations to the admin scripts or prepared statements.

 

From a usership standpoint, you'd probably want to give a shopper who is not logged in the option to log in, continue as guest or register before adding an item to his cart. In this case you would have to create a $_SESSION['sku'] variable to carry through login or registration so the shopper does not lose his chosen item.

 

Would this work order sound about right?

  • Upvote 1
Link to comment
Share on other sites

Thanks for the nice words on the book and for the specific feedback on the advanced queries. Much appreciated. I'm also glad you felt the book was distinguished from the others (and thanks for the continued interest in my books!).

 

As for your question, you're right on target. However, you don't need to change Carts and Wishlists and user_session_id. You could just store the user_session_id in the customers table each time they log in. Then the other functionality stays the same.

 

You could do what you propose and change the uses of user_session_id to customer_id, but the only real change required to the stored procedures would be that customer_id would be an integer and not a string. However, the trick there is that you'd need to store the customer ID in a cookie (on the public side), which is a bad ID. Or you could store it in a session, which would be better, but then you'd need to use sessions on the public side and visibly pass the session ID to the checkout process (the HTTPS), which isn't ideal.

 

And that's really the trick here: bridging the HTTP/HTTPS gap (because the same cookie isn't available in both).

 

Does that make sense or did I just say a lot without actually answering the question?

Link to comment
Share on other sites

No, I think you have answered my questions (and raised some new ones). Keeping the user_session_id would make things a lot simpler. I guess though I was thinking in terms of creating a more permanent shopping cart for registered users, so that their items don't expire if they clear their cache or don't log in for 30 days. For this purpose wouldn't I still need to alter the stored procedures a bit, perhaps in the instance of adding products:

 

DELIMITER $$

CREATE PROCEDURE add_to_cart(uid MEDIUMINT, rid MEDIUMINT NULL, type VARCHAR(6), pid MEDIUMINT, qty TINYINT)

BEGIN

DECLARE cid INT;

SELECT id INTO cid FROM carts WHERE user_session_id=uid OR registered_user_id=rid AND product_type=type AND product_id=pid;

IF cid > 0 THEN

UPDATE carts SET quantity=quantity+qty, date_modified=NOW() WHERE id=cid;

ELSE

INSERT INTO carts (user_session_id, product_type, product_id, quantity) VALUES (uid, type, pid, qty);

END IF;

END$$

DELIMITER;

 

As for bridging the HTTP/HTTPS gap I'm not clear on how there would be new difficulties. The checkout.php script GETS the cart_id where it creates the new HTTPS session. Don't we have access to all the info we need at this point?

Link to comment
Share on other sites

If I were you, I'd make a decision about requiring login or not. Because if you want a permanent wish list, that requires login and you would store the customer ID in the wish lists table. If you want non-logged-in users to have a temporary wish list, as in the book, you'd use the cookie. If you want both features, I would use two separate tables, But then there might be issues about transferring non-logged-in wish list items to the other table when the user later logs in. So, it's complicated.

 

As for the HTTP/HTTPS gap, I'm not sure what I was thinking before, but I think you're right: it's just the same issue that's already been overcome.

Link to comment
Share on other sites

Thanks again for the info. I appreciate you coming so far off topic from the book to answer these questions. Just one last question if you don't mind. Would the difference in storage methods (cookie vs registered user ID) be your only motivation for separating the wishlist and cart tables each off into 2 separate tables for guests and registered users?

 

BTW, really looking forward to your upcoming Javascript book!

Link to comment
Share on other sites

You're quite welcome. And, actually, I'm glad you've raised this question, because it's what I intended with the book: take what you want from the two examples to make what you need. Thanks, too, for your interest in the JavaScript book.

 

In answer to your question, yes, it's the difference in storage method (or, technically, association method) that would suggest separate tables. In theory you could use the same table for both, with a user_session_id column AND a customer_id columns. For logged-in users, the customer ID gets used. For guests, the user session ID. Proper database design suggests not to have columns with lots of NULL values, which such a table would. But you'll need to slap some logic on the system one way or the other (unless, like Amazon, you insist upon login for wish list manipulation).

Link to comment
Share on other sites

 Share

×
×
  • Create New...