Jump to content
Larry Ullman's Book Forums

Combining 2 Queries For The Shopping Cart


Recommended Posts

Larry,

 

I have created a shopping cart using the code in the book, but I had a question about how to optimize the queries. I wanted to add a check to ensure that a product id actually exists in the product's table before adding it to the cart.

 

I decided to do the check to make sure that a record with the user_session_id and product_id doesn't already exist in the carts table as a separate query. My site only allows one of each product to be in a user's cart at any time, so I don't need to update the quantity if they try to add the same product again. If the result is empty, then I do a check to make sure that the product exists in the products table. If it does, then I do the insert into the carts table.

 

The problem is that now I have 3 queries just to do an insert into the carts table. I want to do the query on the products table and the insert query together.

 

I did some research into this and there a few options:

 

The first creates a temp table where the values don't exist in an inner query result:

INSERT INTO carts (user_session_id, product_id)
SELECT * FROM (SELECT '34hu4hr78wrhu', '18') AS tmp
WHERE NOT EXISTS (
    SELECT user_session_id FROM carts WHERE user_session_id = '34hu4hr78wrhu' AND product_id = '18'
) LIMIT 1;

There is INSERT IGNORE, but I have heard that it just creates a warning if the record exists and then aborts, which is a little hacky to me.

 

There is also INSERT... ON DUPLICATE KEY IGNORE, but I have heard that it can have strange behavior on InnoDB tables. The AUTO_INCREMENT column gets incremented even on failed attempts. This also happens with INSERT IGNORE!

 

Then, there is this DUAL thing I have been hearing about. What is that and why would you use it?

 

Anyway, I just want to find a way to combine these queries so that they will be more efficient.

 

What is the best way to approach this Larry?

 

Thanks for any help or advice you can give!

 

Matt

 

 

Link to comment
Share on other sites

A couple of thoughts, Matt...Yes, restricting queries is good, but you have to be careful that you don't so at the cost of user experience. For example, can your query report back to the user all of the following scenarios: item is now in your cart; item was already in your cart; the item doesn't exist? I assume not. Also, while PHP is only executing one query, three queries are still being run in MySQL.

 

The best way to confirm you're on the right path is to do some benchmarking to see what's actually more or less efficient. You might be surprised to find that three simple queries is faster than one complex one. 

Link to comment
Share on other sites

Larry,

 

Thanks for the advice!

 

I thought about the things I need the cart to do over the weekend and I came up with the following list:

 

1) On every page that contains a link to the cart (which is almost every one), the count of products in it should be visible.

 

2) Users should be able to add / remove items from the cart and have the product count update immediately.

 

3) Queries to the database should be minimized so that the cart information is queried only when an item is added / removed.

 

The solution I came up with was quite simple: store everything in a session variable!

 

I created a file called cart_functions.inc.php and I include it on every page that has a link to the cart, including the shopping cart page itself. Also, I am not using stored procedures, so functions in this file acts as the model as well.

 

Here is the code for it:

    function get_cart_count($uid) {
        
        if (!(Session::get('cart'))) {
            
            set_cart($uid);
        
        }
            
        $cart_count = count(Session::get('cart'));
        
        return $cart_count;
        
    }
    
    function add_to_cart($uid, $product_id) {
        
        // Code for inserting an item into the database

        set_cart($uid);
        
        // $result is equal to "true" or "false" depending on insert success
        return $result;
        
    }
    
    function remove_from_cart($uid, $product_id) {
        
        // Code for deleting an item from the database

        set_cart($uid);
        
        // $result is equal to "true" or "false" depending on delete success
        return $result;
    
    }
    
    function get_cart_contents($uid) {
        
        // Code for getting the cart from the database
    
    }
    
    function set_cart($uid) {

        if (!(Session::get('cart'))) {
        
            $cart = get_cart_contents($uid);
        
            if (is_array($cart) && count($cart) > 0) {
        
                Session::set('cart', $cart);
                   
            } else {
            
                Session::set('cart', 0);
                   
            }

        }
        
    }
    
    function get_cart($uid) {
        
        if (!(Session::get('cart'))) {
            
            set_cart($uid);
        
        }
        
        $cart = Session::get('cart');
        
        return $cart;
    
    }

Then, at the top of each page which has a link to the cart I just do the following:

set_cart($uid);

$cart_count = get_cart_count($uid);

Since the data is used on almost every page, this is a perfect case for storing the data in the session. The user may not even look at their shopping cart during a visit, but the count data will be available, and if they do a search for a product, then I can use the data in the session do display a message if the item is already in the cart.

 

As far as displaying a message if the item doesn't exist, I could do it if the add_to_cart() function returns "false", but the only way this would happen is if the user were trying to break the site by passing in a random product id. Should I display a message anyway?

 

Larry, please let me know what you think about this. I have already implemented it and it works great, but let me know if you see any potential problems that could happen. Because this section of the site deals with sales, I want to make sure that it is solid! If you give me the "green light" on it, I can then move forward with the checkout!

 

Thanks Larry,

 

Matt

Link to comment
Share on other sites

I can definitely see the benefits of this approach. And it seems to be mostly good. Except, what happens if the user accidentally closes their browser and loses the session? Or comes back the next day? In general, what happens when the session is lost?

 

As for "no such product", yes, that'd presumably be for user malfeasance, but it could also be because a product was removed right at the exact moment. I don't have strong feelings about this, but I don't see a reason not to display an "item doesn't exist" message. 

Link to comment
Share on other sites

Larry,

 

Thanks for the reply!

 

As far as what happens when the session is lost, when the user returns all the data will be queried again and loaded into the session. I took out the database queries to make the code shorter, so it wasn't obvious. Basically, the code is essentially the same as yours, but instead of working directly with the database, most of the communication is done with the session. It is acting as a kind of intermediary between the user and the database.

 

When the user first goes to the site and a function is called to get some information from the cart, like get_cart_count(), that function will check if the 'cart' array is set in the session. If it's not, it will call set_cart() which will then call get_cart_contents() where the database query is performed. The data returned will be added to the session by set_cart(). From that point on, all calls to get information from the user's cart will get it from the 'cart' session variable. The database will only ever be queried again if the user either adds or removes an item from their cart. Of course, if the user closes their browser and returns, then the database will have to be queried again and the session variable recreated, but the cart data will still be there.

 

So far it is working great! Since it is getting almost everything from the session, it is lightening fast as well!

 

Good point about the 'no such product' message. I didn't think about that. I will add it!

 

Thanks Larry!

Link to comment
Share on other sites

That sounds like a really smart implementation, Matt! Kudos!

 

Also, one more point re: saying "no such product"...even if 99.9% of the time it applies to people manipulating values, gracefully and obviously handling that scenario conveys "this site is protected against your tomfoolery". 

Link to comment
Share on other sites

Larry,

 

Thank you very much for the endorsement of my code! I know we don't always agree on everything (usually it just has to do with the way we approach things), but it really means a lot to me!

 

Also, one more point re: saying "no such product"...even if 99.9% of the time it applies to people manipulating values, gracefully and obviously handling that scenario conveys "this site is protected against your tomfoolery". 

 

Excellent point, Larry! I didn't think about that!

 

Thanks again,

 

Matt

Link to comment
Share on other sites

 Share

×
×
  • Create New...