PHP MySQL inventory likeness percentage

Edit:

I removed the confusing stuff and I wanted to simplify what I want to accomplish:

Let's say John had 2 baskets and 1 of them contained 1 stone, 2 marbles and 2 sticks, the other one contained 2 stones and 2 sticks.

Eric had 1 basket which contained 3 bottles, 2 caps and 1 stone.

Meanwhile Jack had 1 basket which contained 1 stick. Also, Jack has a stone somewhere but it's not inside his basket yet.

开发者_JAVA技巧For the purpose of this query, let's say we're focusing on Jack's items. So we would go over everybody's baskets and see which basket looks pretty much the same as all the items he has overall (a) without thinking whether Jack's items are inside a basket or not and (b) only compare to Jack's items with items that are inside other people's baskets, not outside.

This would be the ideal end result when searching which baskets Jack can match the easiest with all his items regardless of where his items are currently:

Fact:

• Jack has in total 1 stick and 1 stone.

Result:

• 1st: John's second basket containing 2 stones and 2 sticks
• 2nd: John's first basket containing 1 stone, 2 marbles and 2 sticks
• 3rd: Eric's basket containing 3 bottles, 2 caps and 1 stone

---

This will do what you asked for a single basket:

SELECT SUM(item.qty/(SELECT sum(qty) total FROM basket WHERE bid = xxx)
       * 1/all_item.qty) likeness, basket.bid, all_basket.bid all_bid
FROM basket JOIN item USING (bid)
     LEFT JOIN
     (basket all_basket JOIN item all_item USING (bid))
     USING (iid)
WHERE basket.bid = xxx
GROUP BY basket.bid, all_basket.bid
ORDER BY likeness DESC

It's probably possible to join this to the users table to do all his baskets at once. Then wrap it in yet another query to find the best match.

Try this first, and let me know if it works. If it does I'll see if I can do that part tomorrow.

---

Whilst I'm not sure that there's a simple way to calculate basket likeness as a percentage using SQL, I assume you are simply trying to product product recommendations.

You could formulate a query to produce product recommendations based on the current basket. You can do this by:

1. Finding any basket that contains at least one item from the current customer's basket.
2. Limiting the results to products that are in the same category.

You can fetch the appropriate information with the following query:

SELECT b1.basket_id, b1.product_id, b1.quantity, p1.product_category FROM baskets b1
    INNER JOIN products p1 ON p1.product_id = b1.product_id
    WHERE b1.product_id IN
    (
        SELECT b2.product_id FROM baskets b2
            INNER JOIN products p2 ON p2.product_id = b2.product_id
            WHERE b2.basket_id = $basket
            AND p2.product_category = p1.product_category
    )
    AND b1.basket_id != $basket;

This will return the basket ID, product ID, quantity and product category ID for all items in all other baskets that share at least one product with your selected basket. It also filters the results so that the suggested products are always in the same category as the source product.

Here's a sample:

baskets table:
basket_id | product_id | quantity
1,          2,           1
1,          1,           2
1,          3,           5
2,          5,           1
2,          6,           1
3,          1,           1
3,          2,           1
3,          4,           1

products table:
product_id | product_name | product_price | product_category
1,           'cat1 prod1',  14,             1
2,           'cat1 prod2',  1.5             1
3,           'cat1 prod3',  2,              1
4,           'cat2 prod1',  22,             2
5,           'cat2 prod2',  6,              2
6,           'cat2 prod3',  45,             2
7,           'cat3 prod1',  24,             3
8,           'cat3 prod2',  55.4,           3
9,           'cat3 prod3',  22,             3

result of query:
basket_id | product_id | quantity | product_category
3,          1,           1,         1
3,          2,           1,         1

Hopefully that's of some use to you.