Insert lowest price in product table

[vistavision]

Hi all,

I have 2 columns:

1: PRODUCT TABLE (babyfoontest)
id|ean|typenummer|prijs|deeplink
1|123123|type 1|99|http://www.test.com
2|456456|type 2|199|http://www.test.com


2: PRICE TABLE (webshop)
id|shopnaam|eancode|typenummer|price|producturl|categorie
1|shop1|123123|type 1|99|http://www.test.com
2|shop1|44554|type 5|66|http://www.test.com
3|shop1|123123|type 1|49|http://www.test.com

I want to match those tables based on EAN and select the lowest price from the price table and insert it in the product table. I came up with something like:
 

SELECT webshop.eancode, webshop.shopnaam, webshop.typenummer, webshop.price, webshop.producturl, babyfoontest.ean, babyfoontest.afbeelding, babyfoontest.prijs, babyfoontest.deeplink
FROM `webshop`
LEFT JOIN `babyfoontest`
ON webshop.eancode=babyfoontest.ean
WHERE webshop.shopnaam = 'babypark'
ORDER BY babyfoontest.prijs DESC;
UPDATE babyfoontest
SET babyfoontest.prijs=webshop.price, babyfoontest.deeplink=webshop.producturl
WHERE babyfoontest.ean=webshop.eancode
AND (webshop.price < babyfoontest.prijs OR babyfoontest.prijs IS NULL OR babyfoontest.prijs = '')

But I'm not good in SQL so I know this is wrong! Can anybody help me out?

Help would be really appreciated!!!

Kind regards,
Mark
 

[Barand]

try

UPDATE babyfoontest
JOIN (
     SELECT eancode as ean
        , MIN(price) as price
        FROM webshop
        GROUP BY ean
     ) as minprice USING (ean)
SET babyfoontest.prijs = minprice.price;

If the product is always the min webshop price then don't store it in the product table, get it when you need it in a query.

Also typenummer should be in only one table, probably product.

Edited April 8, 2015 by Barand

[vistavision]

Hi Barand,

 

Thanks so much for your answer. This seems to be working but I have a few questions:

1) Typenummer is in both tables because sometimes I want to match on type number and not on EAN.

2) The webshop table is very big so for speed I thought it would be best to store it in the right table. Otherwise I think it takes a while to find the lowest price in the webshop table? Is this correct?

3) Now the price is filled in correctly, but I also need the webshop.producturl stored in the babyfoontest.deeplink. I tried

SET babyfoontest.prijs = minprice.price, webshop.producturl = babyfoontest.deeplink ;

But I think I need to add it also somewhere else in the code?

 

Thanks for the response!! I really appreciate this!!

 

Kind regards,

Mark

[Barand]

Given a fairly large table

mysql> SELECT COUNT(*) FROM test.votes;
+----------+
| COUNT(*) |
+----------+
|   182685 |
+----------+

Finding the the min value for a particular type takes 0.03 seconds

mysql> SELECT type, MIN(votes)
    -> FROM test.votes
    -> WHERE type = 9
    -> GROUP BY type;
+------+------------+
| type | MIN(votes) |
+------+------------+
|    9 |         50 |
+------+------------+
1 row in set (0.03 sec)

+ + +

 

Regarding the producturl, do all records for ean=X in webshop have the same url or can it vary from shop to shop?

[vistavision]

Hi Barand,

 

Thanks for the quick response again! Wow that is really quick indeed. I think my table is a little bit bigger, but it's so fast that I'll fix that issue this weekend. I hope I can fix it meself.

 

For the producturl, the column producturl is always the same column. each record has a different url, so:

producturl:
url1
url2
url3

etc. all products has different urls.

 

Thanks so much for helping me out here!!

 

Kind regards,

Mark

[Barand]

That wasn't quite the question regarding urls

 

If product X is sold in 100 different shops, will all those 100 records have the same url? (if the answer is yes then the url should be in the product table and not the shop table)

[vistavision]

Hi Barand,

 

Ooh ok sorry. If product x is sold in 100 different shops, all urls will be different. The url is unique per webshop per product. So:

Webshop 1 | Product X | url1
Webshop 2 | Product X | url2
Webshop 3 | Product X | url3

So I do need to store it in the product table? Could you help me with the MYSQL to do this? So not only the EAN, but also the related url.

 

Thanks sooo much!!

 

Kind regards,
Mark

[Barand]

In that case the url belongs in the webshop table.

 

The sql gets a bit more complicated though and needs an extra step. We currently have a subquery to find the min price for each product. We now need to use that to match against the webshop table to find the record for that ean that matches the min price and get the url from that record. So your update would now be

UPDATE babyfoontest
JOIN (
     SELECT minprice.eancode as ean
     , webshop.price
     , webshop.producturl
     FROM
     webshop
     INNER JOIN
         (
         SELECT eancode
            , MIN(price) as price
            FROM webshop
            GROUP BY eancode
         ) as minprice USING (eancode,price)
     ) wsrec USING (ean)
SET 
    babyfoontest.prijs = wsrec.price
  , babyfoontest.deeplink = wsrec.producturl;

[vistavision]

Hi Barand,

 

Thanks again for your time! I really appreciate this!! This looks very complicated. I just tested the code. If I run this without the "WHERE", I get a space error:

#3 - Error writing file '/tmp/MYddoxjF' (Errcode: 28 - No space left on device)

 

The webshop table is 1.396.878 rows. I can make a selection in the webshops but when I try to add a "WHERE" like this:

UPDATE babyfoontest
JOIN (
     SELECT minprice.eancode as ean
     , webshop.price
     , webshop.producturl
     FROM
     webshop
    WHERE
    webshop.shopnaam = 'babypark'
     INNER JOIN
         (
         SELECT eancode
            , MIN(price) as price
            FROM webshop
            GROUP BY eancode
         ) as minprice USING (eancode,price)
     ) wsrec USING (ean)
SET
    babyfoontest.prijs = wsrec.price
  , babyfoontest.deeplink = wsrec.producturl;

I get the error:

MySQL meldt: 
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING (eancode,price)
     ) wsrec USING (ean)
SET
    babyfoontest.prijs = ' at line 16

Is this because of my added WHERE-function?

 

Thanks! You're the best!!

[Barand]

1. Your WHERE clause is in the wrong place. It should be UPDATE ... SET ... WHERE ... (http://dev.mysql.com/doc/refman/5.6/en/update.html)

You are now completely changing the nature of the query. You originally wanted the minimum price but your WHERE clause will update with the price and url from the babyfarm records, which isn't necessarily the same thing. What are you really trying to achieve?

[vistavision]

Ok please let me explain what I want to accomplish.

I have a website where users can answer questions (test) and based on the answers, they get a top 5 result with products. I only want to show products which has a price and only show the lowest price available.

I have a directory on my server with .csv datafeeds which I enter in my webshop table with a foreach loop. So all feeds are included in this table (9 columns, 1396878 rows). So all webshop are in this table.

For example I have test with televisions. I only need to select the webshops which delivers televisions. So I thought of a query like:
 

... FROM webshop WHERE
(shopnaam = 'amazon' AND categorie= 'autostoelshop') OR
(shopnaam = 'fonk' AND categorie= 'Babyartikelen') OR
(shopnaam = 'kidsworld' AND categorie= 'Onderweg')

So, in this example, I want the minimum prices and deeplinks in my television table from the webshops.

Is this the right way to do this?

 

If I use the

WHERE webshop.shopnaam, 

I need to select it first in the query right??

 

Thanks!!

 

Kind regards,

Mark

[Barand]

If you are, for example, only interested in televisions then surely this is an attribute of the product (product type or product category) and not of the shop

[vistavision]

Yes, I have multiple tests and in each test am interested in one category, for example televisions. Multiple shops has televisions so I select the right shops in each test.

 

So this is the right way to go? Then the only thing left is embed the "WHERE"-function in the code right?

[Barand]

If there are over a 1m records then there's going to be a lot of shops. If you use the shop names in the WHERE clause, how can you be sure you include all the shops that sell televisions?

Also, by doing it the way you propose, you are are going to process every product that those shops sell - not just televisions but mouse-mats, dvds, microwaves etc

[vistavision]

Hi Barand,

 

I wish it is possible to just check ALL webshops and all products for EAN, but I think the webshop table is going to be even bigger because all webshops can join us for free. So I select per test which webshops have products and I include them. This is a manual process now. I don't see any other way because it's just too big.

 

Almost all webshops have a category column in the datafeeds. So for example amazon has a productfeed with categories like audio, video, televisions, computer, jewelery etc. So if I make a test about televisions I only select the television category of the webshop amazon.com. This is a little work per test but maybe the only way?

If there is a better way I'd love to hear it. But in this case, How can I add the "WHERE"-function in the MYSQL code?

 

Thanks again!! This really helps me!

 

Kind regards,

Mark

[Barand]

I would set it up as shown below. Then with "... WHERE cat_name = 'television' ..." you automatically include all webshop records for shops that sell televisions.

[vistavision]

Hi Barand,

 

But not all webshops have the same categories, so maybe shop x use "Television" as categorie and shop y use "Media" as categorie for televisions. Thanks for the database model. I have it almost like that, except I don't have a seperate shop table. Is that necessary?

I try to use the WHERE-function under the SET, like this:

UPDATE babyfoontest
JOIN (
     SELECT minprice.eancode as ean
     , webshop.price
     , webshop.producturl
     FROM
     webshop
     INNER JOIN
         (
         SELECT eancode
            , MIN(price) as price
            FROM webshop
            GROUP BY eancode
         ) as minprice USING (eancode,price)
     ) wsrec USING (ean)
SET
    babyfoontest.prijs = wsrec.price
  , babyfoontest.deeplink = wsrec.producturl;
WHERE
webshop.shopnaam = 'babypark'

For example, but now I get a 404-error not found? And if I don't use the WHERE-function I get the "No space left on device" error. What am I missing?

p.s. I really appreciate your support and am sorry I am a noob in MySQL!

 

Kind regards,

Mark

[Barand]

Relational theory says it is necessary - the only things that should appear in multiple records are ids (keys). The shop table saves you from repeating the shop name in thousands of records.

 

Does this work for you?

UPDATE babyfoontest
JOIN (
     SELECT minprice.eancode as ean
     , webshop.price
     , webshop.producturl
     , webshop.shopnaam
     FROM
     webshop
     INNER JOIN
         (
         SELECT eancode
            , MIN(price) as price
            FROM webshop
            GROUP BY eancode
         ) as minprice USING (eancode,price)
     ) wsrec USING (ean)
SET
    babyfoontest.prijs = wsrec.price
  , babyfoontest.deeplink = wsrec.producturl;
WHERE
    wsrec.shopnaam = 'babypark'

[vistavision]

Ok then I will add the shop table later, thanks! Sounds like a good improvement!

 

After try the SQL, I get:

 

Foutcode: 502

Foutomschrijving: Bad Gateway

And after the 3rd time I get the 404 Not Found error.

 

I try it from the phpmyadmin SQL tab. Is that ok or should I embed it in a php-file?

 

Thanks!!