Jump to content

vistavision

Members
  • Posts

    18
  • Joined

  • Last visited

vistavision's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. 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!!
  2. 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
  3. 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
  4. 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?
  5. 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
  6. 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!!
  7. 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
  8. 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
  9. 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
  10. 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
  11. Hi Psycho, You Are The Best!!! Thanks so much!! This works Perfectly!!!! Thanks!
  12. Hi everybody, I want to cloak my affiliate links or mask it so it looks like a pretty url. Also if possible, I want to track the clicked link with Google Analytics so I think I need a page where I can log the click. I found lots of information but not for the situation I have. I get my links from my database like this: echo '<a href="'.$info['url'].'">the link</a>'; How can I accomplish this? So I need a url like www.mydomain.com/click and when a user click on the link the get a new page with a tracking code and a redirect script to the right url. Thanks so much for the help! Kind regards, Mark
  13. Hi davidannis, Thanks for your great reply and for your help. Only I want it a little bit different I think. It's not that there is a right or wrong answer to the questions. I want to work with points. So if a user choose answer A for example, I want to add 2 points to all products, if they choose B, I want to add 3 points to all products etc. The points are different with each question. In the end, the product with the most points is number 1! Please tell me how I can make this? I'm willing to learn and try things out, but I need to know how to start. I hope you can help me! Thanks!!! Really appreciate it!
  14. Anybody? I really need to know if this is the way to go. Thanks so much!
  15. Hi all, I want to make a quiz but I'm stock in the process. I've created a form with radiobuttons with questions, so for example: Question 1: Q1A,Q1B,Q1C Question 2: Q2A,Q2B,Q2C After visitors filled in the answers, the form post it to my results.php. This works perfectly. Now I have a MySQL database with: +----------+--------+-----+-----+-----+-----+-----+-----+ | PRODUCT | BRAND | Q1A | Q1B | Q1C | Q2A | Q2B | Q2C | +----------+--------+-----+-----+-----+-----+-----+-----+ | | | | | | | | | | product1 | Apple | 1 | 0 | 1 | 0 | 1 | 0 | | | | | | | | | | | product2 | HP | 0 | 1 | 1 | 1 | 1 | 1 | | | | | | | | | | | product3 | Dell | 0 | 0 | 0 | 0 | 1 | 0 | | | | | | | | | | | product4 | Compaq | 1 | 1 | 1 | 0 | 0 | 0 | +----------+--------+-----+-----+-----+-----+-----+-----+ So if the user select Q1C in question A and Q2A in question B, I want to count all the values from the selected columns, so in this case: Product1 = 1 + 0 = 1 Product2 = 1 + 1 = 2 Product3 = 0 + 0 = 0 Product4 = 1 + 0 = 1 Product 2 wins and is most relevant. I then want to show the top 3 on the page. In total I have about 20 questions and 400 products and I want to create a top 3 list, based on the selected answers. I have a few questions: 1) Is this possible? Or are there better ways to make a election/voting script? Any examples? 2) How can I add the values from multiple columns together and make a total score for the visitor? For example, I need something like this: http://www.stemwijzer.nl/votematch2k2006/app.html I really hope you guys can help me with this! I really want this!! Thanks so much in advance!! Kind regards, Mark
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.